how to code this complex list query? (annotation count + metadata call)

i recently found a glitch in my sorting plugin for elgg that is triggered when i sort videos by 'view count'. the problem is that i have already altered my videolist pages to show both videolist items and also videos that are uploaded to elgg's file plugin, so these queries are now metadata queries, rather than just a plain object lookup query. when i seek to add an annotation count 'getter' to the equation, i am stuck in that i am forced to choose between elgg's metadata query function and elgg's annotation count query function... i can't easily use both together.

essentially, i need a way to return all videolist objects, plus all file objects with a simpletype of 'video', plus i need to also count all the 'view count' metadata records for each entity and then sort the resulting list by the number of views that each entity has received.

i initially thought that i would just write a custom query for this, but looking at the code that has been generated by elgg for performing similar tasks - i think the query might be quite large, so i am wondering if anyone has a better idea. ;)
i am ok with large queries, but it will save me time/effort to find an 'elgg' way to do it.

i also noticed while looking at this, that the core bug i raised in relation to this previously, has not been fixed.. so any comment on that would be helpful too: https://github.com/Elgg/Elgg/issues/8580

  • so it turns out that this query hits the same problem as i already logged in elgg core issue 8580. basically, i can't make complex queries using the elgg core method, due to this SQL sequencing dysfunction. am i stuck writing out the query in full? or is there a better solution?

  • I'm not sure I understand exactly what you're trying to accomplish, but I'm sure it can be accomplished with a standard getter function with custom joins/wheres and order_by

    Pick which one you want core to handle (annotations or metadata) and use that getter function, then handle the other part with joins/wheres

  • i think you are right, matt - unfortunately, i am encountering the sequencing bug that i already logged on github, which means the query that elgg is generating fails every time. any chance you can look at this in core?

  • The easiest way to use plain elgg_get_entities() and write all the custom selects, joins, and wheres yourself. Well, that's what I would do :)

        /**
         * Returns children of a parent within a folder
         * Defaults to root
         * 
         * @param int   $parent_guid GUID of the parent entity
         * @param array $options     Getter options
         * @param bool  $guids_only  Only return guids
         * @return \ElggBatch|int[]|int
         */
        public function getChildren($parent_guid = 0, $options = array(), $guids_only = false) {
    
            $parent_guid = ($parent_guid) ? : $this->guid;
    
            $dbprefix = elgg_get_config('dbprefix');
            $parent_ms = elgg_get_metastring_id('parent');
            $weight_ms = elgg_get_metastring_id('weight');
            $parent_id_ms = elgg_get_metastring_id($parent_guid);
    
            $folder_subtype_id = get_subtype_id('object', Folder::SUBTYPE);
    
            $defaults = array(
                'selects' => array(
                    'CAST(msv_parent.string AS SIGNED) AS parent',
                    'CAST(msv_weight.string AS SIGNED) AS weight',
                ),
                'joins' => array(
                    "JOIN {$dbprefix}annotations ann_parent ON ann_parent.entity_guid = e.guid",
                    "JOIN {$dbprefix}annotations ann_weight ON ann_weight.entity_guid = e.guid",
                    "JOIN {$dbprefix}metastrings msv_parent ON msv_parent.id = ann_parent.value_id",
                    "JOIN {$dbprefix}metastrings msv_weight ON msv_weight.id = ann_weight.value_id",
                ),
                'wheres' => array(
                    "(ann_parent.owner_guid = {$this->guid} AND ann_parent.name_id = $parent_ms AND ann_parent.value_id = $parent_id_ms)",
                    "(ann_weight.owner_guid = {$this->guid} AND ann_weight.name_id = $weight_ms)"
                ),
                'limit' => 0,
                'order_by' => "FIELD(e.subtype, $folder_subtype_id) DESC, weight ASC",
            );
    
            $options = array_merge($defaults, $options);
    
            if ($guids_only) {
                $options['callback'] = array($this, 'rowToGUID');
            } else {
                $options['callback'] = array($this, 'rowToTreeNode');
            }
    
            if ((isset($options['count']) && $options['count']) || $guids_only) {
                return elgg_get_entities($options);
            }
    
            return new \ElggBatch('elgg_get_entities', $options);
        }
    

    Here is a snippet for you to play with. Ultimately, annotations and metadata are the same thing, but in different tables. You can add custom selects for counting views.

    Just note that this doesn't take into account annotation and metadata access.

  • that is essentially what am already doing, except that i see:

    Unknown column 'e.guid' in 'on clause'

    when the query is run.

    if i copy the SQL that is shown via the elgg error message and run it in phpmyadmin, i find i need to move the sequencing of the JOINS i have added, so that my custom ones are below the earlier ones added by core, rather than the other way around - as i already mentioned in the github issue (you actually already commented on that issue and said you had seen the same bug too ismayil ;))

    once i move the order of the joins, the query works fine - so i think this is a bug in elgg core, but as yet i haven't found the needed fix.

  • Just join them again with a different name until the bug is fixed.

  • the entities table is brought into the SQL query within the JOINs that are added by elgg core at the end of the lists of JOINs. so i cannot reference it in my own SQL. if i add entities back in, via my own JOIN and use a different ALIAS for the entities table, then i'm not sure on how that would help me, since the main operations in the query are being applied in reference to the alias to the entities table that is being added by elgg core.

    in other words, as far as i know, if i add an alias to entities and call it e2, then since there is already another alias called e that is referenced by elgg core - the dbase server will access the entities table twice and treat each lookup as a different point of reference, which means that any filters that are applied to the alias 'e', will not be applied to the lookup with alias 'e2' - so the query will not return the correct data.

    at least, that is how other databases i have used would have worked - i don't know 100% for sure that that is how MYSQL would work..

    or did i totally misinterpret what you were suggesting here? ;)

  • I think the problem is here: https://github.com/Elgg/Elgg/blob/2.x/engine/classes/Elgg/Database/Annotations.php#L422

    You can try to make a pull request with a fix. Instead of just merging the $options, it should do something along the lines of elgg_get_entities(), where custom selects, joins, wheres are added the default array after the core selects, joins and where were added.

  • the query i am currently seeing an error for is:

    SELECT
        DISTINCT n_table.*,
        e.*,
        count(
            CAST(a_msv.string AS signed)
        ) AS annotation_calculation
    FROM
        annotations n_table
        JOIN metadata m_table1 on e.guid = m_table1.entity_guid
        JOIN metastrings msn1 on m_table1.name_id = msn1.id
        JOIN metastrings msv1 on m_table1.value_id = msv1.id
        JOIN metastrings a_msv ON n_table.value_id = a_msv.id
        JOIN entities e ON n_table.entity_guid = e.guid
        JOIN metastrings msn on n_table.name_id = msn.id
    WHERE
        msn1.string = "simpletype"
        AND BINARY msv1.string = "video"
        AND m_table1.enabled = "yes"
        AND (
            (
                e.type = 'object'
                AND e.subtype IN (40, 1)
            )
        )
        AND (
            (
                msn.string IN ('views_counter')
            )
            AND (
                (1 = 1)
                AND (n_table.enabled = 'yes')
            )
        )
        AND (
            (1 = 1)
            AND (e.enabled = 'yes')
        )
    GROUP BY
        n_table.entity_guid
    ORDER BY
        annotation_calculation ASC,
        n_table.id
    LIMIT
        0, 12

    the line:  JOIN entities e ON n_table.entity_guid = e.guid

    is one of the ones that i need to be at the top of the query and i am only seeing that here:
    https://github.com/Elgg/Elgg/blob/1.12/engine/lib/metastrings.php#L219

    i have just tested this by replacing line 219 (in the 1.12.x branch) with this:

    array_unshift($joins, "JOIN {$db_prefix}entities e ON n_table.entity_guid = e.guid");

    and in my quick test here, i am no longer seeing any sql errors :)
    i will do some more testing soon.

  • so it looks like this one change to core was enough to get my queries working properly, once i updated one of the queries. there may have been something else that was fixed in elgg 1.12.x as compared to 1.11.x too - but regardless, my code works :)