i want to produce a list of entities that are ordered according to the sum of their overall total comments and likes combined.
i have the queries to get the entities based on their total comments and also based on their total likes, but i haven't yet found a way to combine the two total counts while also using elgg's query building system in PHP.
Anyone got any thoughts as to the best way to do this?
What's the current recommended command for running a custom SQL query if I need to?
thanks
info@elgg.org
Security issues should be reported to security@elgg.org!
©2014 the Elgg Foundation
Elgg is a registered trademark of Thematic Networks.
Cover image by RaĆ¼l Utrera is used under Creative Commons license.
Icons by Flaticon and FontAwesome.
- ura soul@tunist
ura soul - 0 likes
- ura soul@tunist
ura soul - 0 likes
- ura soul@tunist
ura soul - 0 likes
- ura soul@tunist
ura soul - 0 likes
- Steve Clay@steve_clay
Steve Clay - 0 likes
- ura soul@tunist
ura soul - 0 likes
You must log in to post replies.I appear to have got the query to work using a UNION, but i don't think elgg natively supports UNIONS, right?
so i found i can run the query using get_data.. but there is a bug in the query. i continue!
weird. if an item has 2 comments and 1 like, then i get the correct answer of 3 when the two numbers are summed. but if the item has 1 comment and 1 like then i get the incorrect answer of 1 when the two numbers are summed... :/
ah, i needed to use union all, instead of union distinct
I think the most efficient way to display these would be to use your UNION query just to pull the GUIDs in order, then query the entities via elgg_get_entities() and reorder them in PHP based on your GUID array.
I mean, you can just dump a stdClass row into a class constructor, but ege() can do things like prefetch users/containers or use memcache.
that is more or less how the code currently works - if i have understood your description. i grab the guids in the correct order using the query and then build the list using elgg_view_entity_list. it works well so far, i am not noticing any slowdown to mention.