For Elgg 3: help needed to get a rewrite of a query using Querybuilder to work

The code on Elgg 2 is

// limit number of users to be displayed
$limit = (int) elgg_get_plugin_setting('user_listing_limit', 'users_online', 20);
// display admins currently logged in?
$show_admins = elgg_get_plugin_setting('show_admins', 'users_online', 'yes');
$show_admins = ($show_admins == 'yes') ? "('yes', 'no')" : "('no')";

// always added logged in user
$logged_in_guid = elgg_get_logged_in_user_guid();

// active users within the last 5 minutes
$dbprefix = elgg_get_config('dbprefix');
$time = time() - 300;
$users_online = elgg_get_entities([
    'type' => 'user',
    'limit' => $limit,
    'joins' => ["join {$dbprefix}users_entity u on e.guid = u.guid"],
    'wheres' => ["((u.last_action >= $time) and (u.admin in $show_admins)) or (u.guid = $logged_in_guid)"],
    'order_by' => "u.last_action desc",
    'batch' => true,
]);

I'm trying to get this working on Elgg 3 using Querybuilder API but I think I have some fundamental mistake in my code (or just don't understand how the Querybuilder API is supposed to work at all). last_action and guid would be available in the entities table but I would have to take into account the admin metadata from the metadata tables somehow now. But how? I've been searching for some suitable code example in Elgg core but either missed it or there isn't one.

Right now I have

$users_online = elgg_get_entities([
    'type' => 'user',
    'limit' => $limit,
    'joins' => new JoinClause('metadata', 'ma', function(QueryBuilder $qb, $joined_alias, $main_alias) {
            return $qb->compare("$joined_alias.entity_guid", '=', "$main_alias.guid");
        }),
    'wheres' => function(QueryBuilder $qb) use ($time, $show_admins, $logged_in_guid) {
        return $qb->expr()->orX(
            $qb->expr()->andX(
                $qb->compare('e.last_action', '>=', $time, ELGG_VALUE_INTEGER),
                $qb->compare('ma.name', '=', 'admin', ELGG_VALUE_STRING),
                $qb->compare('ma.value', 'IN', $show_admins, ELGG_VALUE_STRING)),
            $qb->compare('e.guid', '=', $logged_in_guid, ELGG_VALUE_INTEGER));
        },
    'order_by' => new OrderByClause('e.last_action', 'DESC'),
    'batch' => true,
]);

This gives me the logged in user as result (I guess due to the second orX argument) but something seems totally not working with the ma join/where clause stuff.

  • My personal preference is to put all queries into a single closure. There is no reason to separate joins from wheres from orders, as they all represent a single logical unit and there is no way you can filter the query options using known Elgg hooks.

    First of all, take a look at 'search_name_value_pairs' option, which has been added in 3.0. It works exactly like metadata_name_value_pairs, but individual where clauses are merged using OR instead of AND. For this particual query, however, it probably won't work, so you would need to use a closure.

    I think for the most part your query could work as is, the only problem I see is that the metadata join is using an INNER join, which in case of metadata is not always set (you don't have admin = 'no' set on all users, but it was always set in 2.0 users table), that probably why you end up with a logged in user only.

    Hopefully this works for you.

    $users_online = elgg_get_entities([
       'type' => 'user',
       'limit' => $limit,
       'wheres' => function(QueryBuilder $qb, $alias) use ($time, $show_admins, $logged_in_guid) {
          $qb->orderBy('e.last_action', 'DESC');
    
          // We use left joins, because 'admin' metadata is not set on all user entities
          $qb->joinMetadataTable($alias, 'guid', 'admin', 'left', 'ma');
    
          $admin_filter = $qb->merge([
             $qb->compare('ma.name', '=', 'admin', ELGG_VALUE_STRING),
    
             // Pass an array of strings here, do not try to concatenate the query string on your own
             // e.g. $show_admins = ['yes', 'no'];
             $qb->compare('ma.value', 'IN', $show_admins, ELGG_VALUE_STRING),
          ]);
    
          $ands = $qb->merge([
             $qb->compare('e.last_action', '>=', $time, ELGG_VALUE_INTEGER),
             $admin_filter,
          ]);
    
          $ors = [
             $qb->compare('e.guid', '=', $logged_in_guid, ELGG_VALUE_INTEGER),
             $ands,
          ];
    
          return $qb->merge($ors, 'OR');
       },
    
       'batch' => true,
    ]);
    

    If you have comments on how to improve the docs, add a github issue and I will try to clarify.

  • It looks like one mistake I had made is that I "concatenated the query string on my own" ($show_admins) as it worked this way for the Elgg 2 query (if I remember I had tried it with an array at some point but I must have had some other error in the query at this time as it also did not work then and now I had totally forgot that this could be the reason for the failure). With the values within an array it works now with my original query code. Though I also wasn't aware about the necessity of using LEFT JOIN (is is really true that not all users have admin metadata either 'yes' or 'no'?).

    I'll try your code for sure. But I think I'll do it tomorrow as I'm feeling rather groogy right now already.

    Regarding the docs I haven't found an example  that uses the Querybuilder API for any metadata constraints. I also wasn't aware of the joinMetadataTable() function so far. The metadata constraint example in the comment of the elgg_get_entities() function makes no use of the Querybuilder API as it's just a simple example. Maybe it would help to have a few "typical" queries using some ands and ors or what else might be often needed just to have some starting point (the doctrine docs are not much detailed either so not much help gained from there).

  • There are quite advanced QB examples in the search docs. I will see if I can add some introduction to explain the reasoning and basic principles I had in mind, when I wrote the helpers.

    As far as admin metadata is concerned, it might be the case if you use register_user to create users, byť it doesn't have to be. You can use ElggUser constructor with save() and unless there is some validation that would fill in missing values, no metadata is required or guaranteed to be written. Unless you are searching for specific metadata, I would suggest always using LEFT joins to make sure your ORs don't skips entities.

  • It looks like any users registered on Elgg 3 who are not admin have no admin metadata. So, it does not work with the "IN" operator (you would only get admins, users registered pre-3.0 and yourself displayed as online users).

    Is there a way to get it working without a subquery (to get users either with admin metadata NOT 'yes' or users without admin metadata entry)?

  • It looks like it works with

    
    $users_online = elgg_list_entities([
        'type' => 'user',
        'limit' => $limit,
        'wheres' => function(QueryBuilder $qb, $alias) use ($time, $show_admins, $logged_in_guid) {
            $qb->orderBy('e.last_action', 'DESC');
    
            if ($show_admins != 'yes') {
                // We need to users without an admin metadata entries
                // i.e. users registered on Elgg 3 or later who are no admins
                $subquery = $qb->subquery('metadata', 'ne');
                $subquery->select('1')
                    ->where($qb->compare('ne.entity_guid', '=', 'e.guid'))
                    ->andWhere($qb->compare('ne.name', '=', 'admin', ELGG_VALUE_STRING));
    
                // We use left joins, because 'admin' metadata is not set on all user entities
                $qb->joinMetadataTable($alias, 'guid', 'admin', 'left', 'ma');
    
                $admin_metadata = $qb->merge([
                    $qb->compare('ma.name', '=', 'admin', ELGG_VALUE_STRING),
                    // Pass an array of strings here, do not try to concatenate the query string on your own
                    // e.g. $show_admins = ['yes', 'no'];
                    $qb->compare('ma.value', '<>', 'yes', ELGG_VALUE_STRING),
                ]);
    
                $admin_filter = $qb->merge([
                    $admin_metadata,
                    "NOT EXISTS (" . $subquery->getSQL() . ")",
                ], 'OR');
    
                $ands = $qb->merge([
                    $qb->compare('e.last_action', '>=', $time, ELGG_VALUE_INTEGER),
                    $admin_filter,
                ]);
            } else {
                $ands = $qb->compare('e.last_action', '>=', $time, ELGG_VALUE_INTEGER);
            }
    
            $ors = [
                $qb->compare('e.guid', '=', $logged_in_guid, ELGG_VALUE_INTEGER),
                $ands,
            ];
    
            return $qb->merge($ors, 'OR');
        },
        'list_type' => 'gallery',
        'item_view' => 'users_online/list/user',
    ]);

    What I find odd is that it fails (shows admins) when I use type casting in the subquery in the line

    ->where($qb->compare('ne.entity_guid', '=', 'e.guid', ELGG_VALUE_INTEGER))

    but works without

    ->where($qb->compare('ne.entity_guid', '=', 'e.guid'))

    But for example in the line

    $qb->compare('e.guid', '=', $logged_in_guid, ELGG_VALUE_INTEGER),

    and the others it works with type casting. Is the failure due to chaining ->where and ->andWhere?

    Any suggestions to get it working differently (more efficiently)?

  • Approach it differently. Include all users and if admins should be excluded, use NOT IN with sub query for admin metadata.

    Typecasting is only needed when you are parametrizing PHP variables and it tells DBAL how to sanitize your values. For literal SQL statements, you don't need sanitization. If you need to cast a column to a specific value, you to need to include it in your comparison clause: CAST(e.guid as signed) and leave out the type argument.

  • Thanks. Got it working.