0

I'm trying to list 8 users (name and profile image) starting with the one who created the newest node and follow with the rest of users in (descending) order of their last created node.

I got to achieve it* by:

  1. adding fields and filters
  2. adding a Relationship (advanced) with Content authored
  3. adding Sort criteria as Content: date (descendant)

It 'works' but I get duplicated users. If a user created the 3 newest nodes, then it appears in positions #1, #2 and #3. I can't get it to get only distinct (unique) users in the list. Aggregation did not work.

SQL query (where I get users in the right order but with repeated ones) added:

SELECT DISTINCT "users_field_data"."created" AS "users_field_data_created", "users_field_data"."uid" AS "uid", "node_field_data_users_field_data"."nid" AS "node_field_data_users_field_data_nid", DATE_FORMAT((DATE_ADD('19700101', INTERVAL node_field_data_users_field_data.created SECOND) + INTERVAL 3600 SECOND), '%Y%m%d%H') AS "node_field_data_users_field_data_created_hour"
FROM
{users_field_data} "users_field_data"
INNER JOIN {node_field_data} "node_field_data_users_field_data" ON users_field_data.uid = node_field_data_users_field_data.uid
LEFT JOIN {user__roles} "user__roles_value_0" ON users_field_data.uid = user__roles_value_0.entity_id AND user__roles_value_0.roles_target_id = 'content_editor'
WHERE ("users_field_data"."status" = '1') AND ("user__roles_value_0"."roles_target_id" = 'content_editor')
ORDER BY "node_field_data_users_field_data_created_hour" DESC, "users_field_data_created" DESC
LIMIT 10 OFFSET 0

Any clues? Ty

7
  • 1
    Is the view listing users with a relation to content, or content with a relation to users?
    – Jaypan
    Feb 29 at 18:28
  • Users with a relation to content. I previously had a (simple) view of the last created users. Then added the relationship.
    – hip
    Mar 1 at 12:59
  • In the view settings, under the Advanced -> Query Settings click 'settings', then select 'Distinct'. Does this solve your issue?
    – Jaypan
    Mar 1 at 17:27
  • Ty, but for whatever the reason that 'Distinct' option doesn't seem to force any change. :-(
    – hip
    Mar 1 at 17:58
  • 1
    If that doesn't work, post the query that is being executed. You may need to enable the setting to show the query, which is in Views settings (not the settings for the view itself, the global settings for all views)
    – Jaypan
    Mar 1 at 18:07

1 Answer 1

0

SOLVED. Taking a different approach.

  1. Adding fields and filters (as above)
  2. I've set the Relationship to Representative Node (instead of Content Authored)
  3. Within that relationship, Order Criteria was set to (Content) Date - descendant
  4. (Main) Order Criteria was set to (Content) Date (node representative) - descendant

and voilà. Solved! The 'Representative Node' did the bit to get a distinct / unique / only one single user even if they had (and they have) several nodes published.

This way we have a list of users ordered by the date of their last published node starting from the most recent ones.

Ty @Jaypan for your interest and suggestions. They helped.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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