can i get a list of entities based on a sum of their total comments and likes - without resorting to a custom sql query?

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

  • I appear to have got the query to work using a UNION, but i don't think elgg natively supports UNIONS, right?

    select guid, sum(trending_score) from (
    SELECT DISTINCT e.guid,
    count( * ) AS trending_score
    FROM entities e
    JOIN entities ce ON ce.container_guid = e.guid
    JOIN entity_subtypes cs ON ce.subtype = cs.id
    AND cs.subtype = 'comment'
    WHERE e.type = 'object'
    AND e.subtype IN (27,6,24,12)
    AND e.site_guid IN (1)
    AND e.time_created >= 1488557607
    AND e.access_id IN (2,-5)
    AND e.enabled = 'yes'
    GROUP BY e.guid

    UNION DISTINCT

    SELECT DISTINCT e.guid,
    count(CAST(a_msv.string AS signed)) AS trending_score
    FROM annotations n_table
    JOIN entities e ON n_table.entity_guid = e.guid
    JOIN metastrings a_msv ON n_table.value_id = a_msv.id
    JOIN metastrings msn on n_table.name_id = msn.id
    WHERE ((e.type = 'object' AND e.subtype IN (27,6,24,12)))
    AND (e.time_created >= 1488561504)
    AND ((msn.string IN ('likes'))
    AND ((1 = 1)
    AND (n_table.enabled = 'yes')))
    AND ((1 = 1)
    AND (e.enabled = 'yes'))
    GROUP BY n_table.entity_guid
    ORDER BY trending_score DESC
    )
  • 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.