Order by Meta_value

Hi all, I’m doing some s2member custom code that includes some buddypress fields, I need to order by a meta_value field and I’m stuck :(

Its not an s2 field I want to order by but you guys are so smart and helpful I was hoping you’d spot the answer to my problem…

Here’s my query:

SELECT DISTINCT(U.ID) as `ID`
FROM ast_users AS U 
inner join ast_usermeta as UM on U.ID = UM.user_id 
inner join ast_usermeta as UMMD on U.ID = UMMD.user_id 
WHERE (UMMD.meta_value = (SELECT meta_value 
FROM ast_usermeta WHERE meta_key = 'last_activity' 
AND user_id = U.ID) 
OR UMMD.meta_value = '') 
ORDER BY UMMD.meta_value DESC

I need to order by UMMD.meta_value which contains a date, so I’ve used my WHERE to dig out the correct meta_value field based on its corresponding meta_key. However, the order by doesn’t work – if I change the top line to SELECT DISTINCT(U.ID) as `ID, UMMD.meta_value it works, but it also duplicates my results as it seems to ignore the DISTINCT on the U.ID field…

I’m stumped, I’ve tried all sorts – please help!!

update: if I remove OR UMMD.meta_value = ” it works, but then users with no last_activity field are excluded from the results!!

Well, I’m not very experienced with MySQL…

Are you trying to get a list of all the user IDs and their last_activity time if they have one? I don’t know if a single query could return that.

The problem with usermeta is that -from what I’ve seen- if the user doesn’t have that field, it’s not just empty, it doesn’t even exist for the user. No row is created with that user_key for that user if it was never stored.

What I’d do is two queries: one for the [hilite mono]`users.ID`[/hilite] list and another for the [hilite mono]`usermeta`.`user_id`[/hilite] with a [hilite mono]last_activity[/hilite]. Then work with the results in PHP.

I hope that helps. :)

My only concern is it might be a bit inefficient, Also I can’t immediately think of a way 2 queries will work, I can get the last_activity date but cant sort by it (without it excluding those without or duplicating entires)… I guess I’ll have to test it a bit more! It might also be worth finding out when buddypress stores that date and why people dont have it, surely when they sign up that is “activity”… Hmm!

Thanks for the feedback though, it’s useful to have a second opinion whe you work alone! :)

Well, MySQL is not super efficient when it comes to complex queries either… I think doing the two quick queries and then working with the results in PHP may be faster. You get the complete list of users, get the list of users with last_activity time and then add it to the other.

You could have the user_id as the keys of the users array. Then when you loop the results fo the last_activity query, just do something like [hilite code]$users[$user_id]['last_activity'] = $last_activity_time;[/hilite]