List photos entities based on users metadata

How do I list all photos of users that have a certain metadata. So Lets say I have two entities:

'users' and 'images'

some users have the following metadata saved to them like so:

$user->status = 'active'

so now I want to list all the image entities for all users that have $user->status = 'active'.

How can this be done?

  • It might work with

    offset = (int)get_input('offset', 0);
    $limit = (int)get_input('limit', 16);
    $db_prefix = elgg_get_config('dbprefix');
    $meta_status_id = elgg_get_metastring_id('status');
    
    $entities = elgg_list_entities(array(
        'type' => 'object',
        'subtype' => 'image',
        'limit' => $limit,
        'offset' => $offset,
        'joins' => array(
            "JOIN {$db_prefix}users_entity ue ON ue.guid = e.owner_guid",
            "JOIN {$db_prefix}metadata md4 ON (md4.owner_guid = e.owner_guid AND md4.name_id = $meta_status_id)",
            "JOIN {$db_prefix}metastrings ms4 ON md4.value_id = ms4.id"
        ),
        'wheres' => array(
            "ms4.string = 'active'"
        ),
        'group_by' => 'e.guid',
        'order_by' => "views DESC",
        'full_view' => false,
        'list_type' => 'gallery',
        'gallery_class' => 'tidypics-gallery'
    ));

    I can't tell if this code is fully correct though (can't test your setup with the status metadata).

  • I received this error

     

    An exception occurred while executing 'SELECT COUNT(DISTINCT e.guid) as total FROM elgg_entities e JOIN elgg_users_entity ue ON ue.guid = e.owner_guid JOIN elgg_metadata md4 ON (md4.owner_guid = e.owner_guid AND md4.name_id = 283801) JOIN elgg_metastrings ms4 ON md4.value_id = ms4.id WHERE ms4.string = 'active' AND ((e.type = 'object' AND e.subtype IN (11))) AND (e.site_guid IN (1)) AND ((1 = 1) AND (e.enabled = 'yes'))':

    SQLSTATE[HY000]: General error: 2013 Lost connection to MySQL server during query

    QUERY: SELECT COUNT(DISTINCT e.guid) as total FROM elgg_entities e JOIN elgg_users_entity ue ON ue.guid = e.owner_guid JOIN elgg_metadata md4 ON (md4.owner_guid = e.owner_guid AND md4.name_id = 283801) JOIN elgg_metastrings ms4 ON md4.value_id = ms4.id WHERE ms4.string = 'active' AND ((e.type = 'object' AND e.subtype IN (11))) AND (e.site_guid IN (1)) AND ((1 = 1) AND (e.enabled = 'yes'))

  • Difficult to say what might be wrong. "Lost connection to MySQL server during query" could mean a timeout issue or a out-of-memory issue of the MySQL server during the query. This might be because the query I suggested is wrong (as I said I can't test it because I don't have the necessary data you require, i.e. the status metadata). Or it might be that the query is correct as such but so complex that your server can't handle it due the limits set in the MySQL server config (timeouts, memory, cache sizes etc.). Maybe the query could get modified to require less server resources. But I can't say how because that's beyond my MySQL knowledge. Or it might be necessary to adjust the MySQL server config to be able to handle this query. But server config support is a complex matter on its own and I have to say it's beyond the support I can provide here.