0

I have a view of photo nodes uploaded by users and want to group them by post date and user. That part is pretty straightforward once you patch views. Just set sort by "post date" with "day" granularity and add a username field to group by user and day.

I use "count distinct" for nid to show the number of photos by user. I even used "format plural", "no results behavior" and "rewrite output" to only show a link to more photos if there are more than 1 photos per user. So far so good ...

The challenge I'm having is when I try to show a photo and title. When I add the "title" field with "group results" it's now showing each photo instead of grouping. If I use "max" or "min" it will only show 1 photo per user but now the photo won't match the title.

So, how can I show a photo and matching title for photos grouped by day and user? At this point it doesn't matter much if the photo is the oldest or newest for that user and day.

thanks!

1
+50

In terms of the database there is two ways to make this happen.

  1. Using GROUP BY (=aggregation)
    • group by user and day.
    • add additional sort criteria so that the photo you want to display is the first in this ordering.
    • mysql automatically displays the value of the first row in the group for each field that hasn't another aggregation function applied (and isn't grouped by).
    • Example to display the first photo per user and day (pseudo-SQL): SELECT node.*, DAY(created) AS day FROM node GROUP BY user, day ORDER BY created
  2. Reformulate the problem and filter using joins
    • The first photo from each user per day can be reformulated as: The photo of each user (per day) that hasn't got an older one on this day.
    • In SQL this can be realized using joins
    • SELECT n1.* FROM node n1 LEFT OUTER JOIN node n2 ON n1.uid=n2.uid AND DAY(n1.created)=DAY(n2.created) AND n2.created<n1.created WHERE n2.nid IS NULL

In theory both ways should be expressible in views. But both are rather hard:

  1. views doesn't support ungrouped/unaggregated fields in queries using aggregation. So to work with that you'd need to patch views.
  2. Views provides no way to add additional conditions to relations.

The simplest way to achieve this is perhaps by using views to create the query without aggregation (like in 1.) and then use hook_views_query_alter() to add the grouping:

function yourmodule_views_query_alter(&$view, &$query) {
  $query->groupBy('node.uid')->groupBy('day');
}

NOTE that this won't work as is and without testing. You'd need to take a closer look at the $query generated by views to do this right.

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.