Isolate GROUP BY in a subquery to make regular field handlers work with the thread-based mailbox

Mailbox view is tricky stuff because of GROUP BY etc. Can you post here exact query your view generates ?

The problem is "users_pm_message_picture" is inside GROUP BY. Because user pictures are unique for every message, they break thread grouping.
Unfortunately, they can't be ouside of GROUP BY either: that would be invalid SQL. This requires special views handler most likely.

It's not difficult. It simply requires someone to do it. But as a generic solution we might need something better. It would be awesome to have a relationship plugin that allowed to fetch field values in a separate query. That would allow us to use any existing field in GROUP BY queries using the relationship. Though I am not sure if this is possible.

For now I can recommend to output user pictures inside a template. That is much easier to do.

Yeah or use Views Custom field or something like that (a module that lets to use custom php code as a field output). Just make sure that message author id is available.

I'm trying to do something similar, but using an image field from content_profile.module's user nodes. So I need two relationships instead of one.
The wanted fields are joined in by first adding a relationship with Privatemsg: Author, then another one for Node: Content Profile which has been set to use the previous Author relationship.
Then finally I add the CCK file/image field to the query via the Content Profile relationship.

The above (including some other fields) produces a query like:

SELECT MAX(pm_message.mid) AS mid,
   pm_index.thread_id AS pm_index_thread_id,
   pm_index.uid AS pm_index_uid,
   node_users_pm_message_node_data_field_avatar.field_avatar_fid AS node_users_pm_message_node_data_field_avatar_field_avatar_fid,
   node_users_pm_message_node_data_field_avatar.field_avatar_list AS node_users_pm_message_node_data_field_avatar_field_avatar_list,
   node_users_pm_message_node_data_field_avatar.field_avatar_data AS node_users_pm_message_node_data_field_avatar_field_avatar_data,
   node_users_pm_message.nid AS node_users_pm_message_nid,
   node_users_pm_message.type AS node_users_pm_message_type,
   node_users_pm_message.vid AS node_users_pm_message_vid,
   users_pm_message.name AS users_pm_message_name,
   users_pm_message.uid AS users_pm_message_uid,
   pm_message.subject AS pm_message_subject,
   MAX(pm_message.timestamp) AS updated,
   pm_index.is_new AS pm_index_is_new
 FROM pm_message pm_message 
 LEFT JOIN users users_pm_message ON pm_message.author = users_pm_message.uid
 LEFT JOIN node node_users_pm_message ON users_pm_message.uid = node_users_pm_message.uid AND node_users_pm_message.type = 'profile'
 INNER JOIN pm_index pm_index ON pm_message.mid = pm_index.mid
 LEFT JOIN content_type_profile node_users_pm_message_node_data_field_avatar ON node_users_pm_message.vid = node_users_pm_message_node_data_field_avatar.vid
 WHERE (pm_index.deleted = 0) AND (pm_index.uid = 13)
 GROUP BY pm_index_thread_id, pm_index_uid, node_users_pm_message_node_data_field_avatar_field_avatar_fid, node_users_pm_message_node_data_field_avatar_field_avatar_list, node_users_pm_message_node_data_field_avatar_field_avatar_data, node_users_pm_message_nid, node_users_pm_message_type, node_users_pm_message_vid, users_pm_message_name, users_pm_message_uid, pm_message_subject, pm_index_is_new
  ORDER BY pm_index_is_new DESC, pm_index_thread_id DESC

That gives one row per participant in a thread, but I want one row per thread.

For this, pretty much all the groupings above are redundant. There's no point to group on pm_index_uid, any of the avatar (Content Profile CCK image) fields, the message's name, subject or new status.

The query I'm after would look similar to:

SELECT MAX(pm_message.mid) AS mid,
   pm_index.thread_id AS pm_index_thread_id,
   pm_index.uid AS pm_index_uid,
   node_users_pm_message_node_data_field_avatar.field_avatar_fid AS node_users_pm_message_node_data_field_avatar_field_avatar_fid,
   node_users_pm_message_node_data_field_avatar.field_avatar_list AS node_users_pm_message_node_data_field_avatar_field_avatar_list,
   node_users_pm_message_node_data_field_avatar.field_avatar_data AS node_users_pm_message_node_data_field_avatar_field_avatar_data,
   node_users_pm_message.nid AS node_users_pm_message_nid,
   node_users_pm_message.type AS node_users_pm_message_type,
   node_users_pm_message.vid AS node_users_pm_message_vid,
   users_pm_message.name AS users_pm_message_name,
   users_pm_message.uid AS users_pm_message_uid,
   pm_message.subject AS pm_message_subject,
   MAX(pm_message.timestamp) AS updated,
   pm_index.is_new AS pm_index_is_new
 FROM pm_message pm_message 
 LEFT JOIN users users_pm_message ON pm_message.author = users_pm_message.uid
 LEFT JOIN node node_users_pm_message ON users_pm_message.uid = node_users_pm_message.uid AND node_users_pm_message.type = 'profile'
 INNER JOIN pm_index pm_index ON pm_message.mid = pm_index.mid
 LEFT JOIN content_type_profile node_users_pm_message_node_data_field_avatar ON node_users_pm_message.vid = node_users_pm_message_node_data_field_avatar.vid
 WHERE (pm_index.deleted = 0) AND (pm_index.uid = 13)
 GROUP BY pm_index_thread_id
  ORDER BY pm_index_is_new DESC, pm_index_thread_id DESC

Which fetches what I need, but how do I make Views do that with the handlers provided by this module? The comments above indicate new handlers are needed, mind elaborating on that?

The query doesn't become invalid SQL because those fields aren't grouped by. Do the results really need to be grouped by those fields?

@TwoD
If there's a column in the query that is not aggregate function, it should be inside GROUP BY. Otherwise SQL is invalid. The examples you link show using of subquery which would require custom handlers too.

On a second thought, I think the linked technique is quite interesting: it could indeed allow to use regular handlers with our views, cause grouping would be isolated in a subquery. Making it a feature request because of that.

@TwoD: If you want to work on this, it should be a relationship handler. By selecting which fields/arguments/filters use the relationship and which don't, we would move them between the main query and the subquery.