2

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.

  • When you select fields to show, make sure you add a field that you want to sort by, whether it's A1:title or B1:taxonomy_term:name or what have you. When adding the field make sure under relationship you select the relationship you added for U<A or U<B depending which content type the field is on. – CR47 May 10 '14 at 17:48
  • @CR47 thanks. I'd added the sort field; when I do, the 'Configure sort criterion' dialog has only one choice under is 'Relationship' dropdown ('Profile'), which I select. – tog22 May 10 '14 at 18:08
  • How's your SQL? Are you able to post an example of the query that gets executed, and an example of how you'd like it to look? That would make it easier to work out if this can be done through the UI or if it needs a bit of query altering – Clive May 10 '14 at 18:08
  • @Clive I've added a simplified example to the question. My SQL skills are good. – tog22 May 10 '14 at 18:25
1

(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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.