Filter users by metadata and ordering

Hello,

I want to get only users with a particular value of a particular metadata field. But I also want to get 10 of these users by ordering them from followers count. If I use elgg_get_entities_from_metadata I only get the last created users with that field of metadata, if I use elgg_get_entities_from_relationship_count I don't get nothing if I insert metadata_name and metadata_value in options array.

Any tip? Thank you

  • Try (but I haven't checked it):

    //Popular users with most followers

    $entities = elgg_get_entities_from_relationship_count(array('type' => 'user', 'full_view' => false, 'limit' => 10, 'relationship' => 'friend', 'inverse_relationship'=> false));

    foreach ($entities as $entity) {
        $icon = elgg_view_entity_icon($entity, 'tiny', array('use_hover' => true));
        $params = array(
            'entity' => $entity,
            'title' => $entity->username,
        );
        $list_body = elgg_view('user/elements/summary', $params);
        
      //Set your metadata's values

      $metadata_true = elgg_get_metadata(array(
            'guid' => $entity->guid,
            'metadata_name' => 'your_metadata_name',
            'metadata_value' => 'your_metadata_value',
        ));
        
        if($metadata_true) {
           $html .= elgg_view_image_block($icon, $list_body);
        }
    }

    echo $html;

  • I would like to do it in the other way, i.e. to get all users with that particular metadata field and value, and then order the entities list by number of followers.

    I want to do it this way because it would be faster for my site.

    Thank you

  • It's probably easier to add the metadata condition manually. So use elgg_get_entities_from_relationship_count() but in $options add a "joins" key for the metadata table with the needed conditions in a "wheres" key.

    Note this will ignore the access_id of the metadata, but that's probably fine for your purpose. It will also likely be slow, so cache the results if you can.

  • Ok @Steve Clay, I think this is the best way. But I am a noob with joins and wheres options in Elgg, can you make me an example for these options? e.g. if I want to get users with 'Premium' value for 'profile_type' metadata field, how do I have to write these conditions? Thank you very much

  • Let's start with options for elgg_get_entities_from_relationship_count():

    $options = [
      'type' => 'user',
      'limit' => 10,
      'relationship' => 'friend',
      'inverse_relationship'=> false,
    ];

    Now let's get metastring IDs for use in the metadata table:

    $name_id = elgg_get_metastring_id('metadata_name');
    $value_id = elgg_get_metastring_id('metadata_value');
    
    

    Now we add the join:

    $prefix = elgg_get_config('dbprefix');
    $options['joins'][] = "JOIN {$prefix}metadata md ON (e.guid = md.entity_guid)";

    And finally the where condition to match metadata, and perform the query:

    $options['wheres'][] = "md.name_id = {$name_id}";
    $options['wheres'][] = "md.value_id = {$value_id}";
    
    $users = elgg_get_entities_from_relationship_count($options);
  • Ok I solved it, it was a very stupid error. For other Elgg users: elgg_get_entities_from_relationship_count() works with metadata fields. But thank you very much, RvR and Steve Clay, for explaining me using joins and wheres options. 

  • I'm sorry, but this method with joins and wheres works fine with strings. If I want to get all users with a metadata integer field major or equal than a certain value what do I have to do? Thanks

  • $options['wheres'][] = "md.value_id >= {$value_id}";
    

    That should work fine. If it doesn't you can cast the value: http://www.mysqltutorial.org/mysql-cast/

    $options['wheres'][] = "CAST(md.value_id AS SIGNED) >= {$value_id}";
  • @Ismayil, even if it is an integer value, do I have to use

    $value_id = elgg_get_metastring_id($number) ?