Posted by pribeh on October 19, 2010 at 7:50pm
3 followers
Jump to:
| Project: | Privatemsg Views |
| Version: | 6.x-1.x-dev |
| Component: | Miscellaneous |
| Category: | feature request |
| Priority: | normal |
| Assigned: | Unassigned |
| Status: | active |
Issue Summary
Hi,
Here's an actual issue I discovered. I've added user pictures to the default privatemsg_mailbox view in via the "Privatemsg: Author" relationship and the User Picture field. Unfortunately, this seems to break the thread into separate messages. I read this issue (http://drupal.org/node/735730) but am still unsure as to what is causing the threads to be broken into individual messages. Is there a way around this?
Thanks,
Thomas
Comments
#1
So the threads are not broken into separate messages if I use "Privatemsg: Participant" as the relationship for the user picture but that doesn't provide the right display.
#2
Mailbox view is tricky stuff because of GROUP BY etc. Can you post here exact query your view generates ?
#3
Thanks for looking into this:
SELECT users_pm_message.picture AS users_pm_message_picture, users_pm_message.uid AS users_pm_message_uid, users_pm_message.name AS users_pm_message_name, users_pm_message.mail AS users_pm_message_mail, MAX(pm_message.mid) AS mid, pm_index.thread_id AS pm_index_thread_id, pm_index.uid AS pm_index_uid, pm_message.subject AS pm_message_subject, MAX(pm_message.timestamp) AS updated FROM pm_message pm_message INNER JOIN pm_index pm_index ON pm_message.mid = pm_index.mid LEFT JOIN users users_pm_index ON pm_index.uid = users_pm_index.uid LEFT JOIN users users_pm_message ON pm_message.author = users_pm_message.uid WHERE (pm_index.deleted = 0) AND (pm_index.uid = 1) GROUP BY users_pm_message_picture, users_pm_message_uid, users_pm_message_name, users_pm_message_mail, pm_index_thread_id, pm_index_uid, pm_message_subject ORDER BY updated DESC#4
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.
#5
Hey Crea,
Thanks for looking into this. I assumed that something like what you describe was happening. How difficult would it be to write a views handler for this?
Thomas
#6
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.
#7
Would I use a views template override?
#8
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.
#9
#10
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?
#11
@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.
#12
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.