Sorting entities in a view by a field in a 'sibling' entity At its most general, my question is how you can sort entities in a view by a field in a related entity. This is best illustrated with a lengthy example which is more specific, though still quite abstract. It describes sorting by 'sibling' or 'child' entities (depending on what you count the view as showing).



Suppose that you have entities in the following structure:


Entity A1 (of entity type A*) related to (colloquially, a 'child' of) entity U1 (of type U*)
Entity B1 (or type B*) is also a child of the same entity U1


Here is my particular example:


User P has 2 profile2 profiles, A and B


Now suppose you have a view of entities of type U* (in my example, a view of users) - i.e. when first creating the view, you chose U* (here, users) from the 'Show' menu. You've added relationships to A* and B* in this view, and made it show the name field of U* (here, 'User: name') and the fields of A*, filtered by a field in A*. Given our specific entities, the view would show:

U1
A1 field 1
A1 field 2


This means that though this is a view of U* entities in the 'i.e.' sense above, it could also be called a view of A* entities in the sense that if you'd added B* fields they'd show under a duplicate of the U* name. Given our specific entities, the view would show:

U1
A1 field 1
A1 field 2

U1
B1 field 1
B1 field 2


This makes my example either about 'child' or 'sibling' entities.

Sorting the view by a field of B* (e.g. B* field 1) doesn't work. Applied to this abstract example, my question is how to make it work.



As requested in comments, here is the SQL query and results for an instance of this example. The SQL:

SELECT users.name AS users_name, users.uid AS uid, profile_users.pid AS profile_users_pid, profile_users__field_data_field_weighting.field_weighting_value AS profile_users__field_data_field_weighting_field_weighting_va, 'profile2' AS field_data_field_do_you_plan_to_donate_a_ce_profile2_entity_, 'profile2' AS field_data_field_if_so_to_which_causes__profile2_entity_type, 'profile2' AS field_data_field_if_so_until_when__profile2_entity_type, 'profile2' AS field_data_field_would_you_like_to_give_any_profile2_entity_
FROM 
{users} users
LEFT JOIN {profile} profile_users ON users.uid = profile_users.uid
LEFT JOIN {field_data_field_do_you_plan_to_donate_a_ce} profile_users__field_data_field_do_you_plan_to_donate_a_ce ON profile_users.pid = profile_users__field_data_field_do_you_plan_to_donate_a_ce.entity_id AND (profile_users__field_data_field_do_you_plan_to_donate_a_ce.entity_type = 'profile2' AND profile_users__field_data_field_do_you_plan_to_donate_a_ce.deleted = '0')
LEFT JOIN {field_data_field_weighting} profile_users__field_data_field_weighting ON profile_users.pid = profile_users__field_data_field_weighting.entity_id AND (profile_users__field_data_field_weighting.entity_type = 'profile2' AND profile_users__field_data_field_weighting.deleted = '0')
WHERE (( (profile_users__field_data_field_do_you_plan_to_donate_a_ce.field_do_you_plan_to_donate_a_ce_value IS NOT NULL ) ))
ORDER BY profile_users__field_data_field_weighting_field_weighting_va ASC


The results:

Tom Ash
Do you plan to donate a certain amount either until you retire or until you die?:
Everything over £14,000 (~$22,000), and at least 10% of income
Weighting:
NO RESULT, BECAUSE SHOWING DIFFERENT 

Tom Ash
NO RESULT, BECAUSE SHOWING DIFFERENT PROFILE
Weighting:
432




My research so far (~3 hours): Googling; looking through dozens of StackExchange answers; searching the Views issue queue; as had no responses here yet, added a support request in the Views issue queue.
(1) you cannot do this simply by editing the view, but (2) you can do this with a custom module. I expand on (1) and (2) below.

(1) holds because there is no single ;-terminated SQL query which will do this. The reason is that when you have 2 profile2 profiles the data you're fetching is spread across 4 tables structured like this. As you can see from that, the 2 profile2 tables reside in separate tables, and you can't tell that they belong to the same user except by checking a 3rd table. SQL queries simply can't manage this.

(2) is possible because a custom module lets you run multiple lines of PHP, drawing on additional query to fetch the field you want to sort by. You want to do this in hook_views_pre_render. Here's a module I created to do something similar on an existing site I had: http://www.pastebucket.com/40183