SQL CUSTOM QUERY

Im trying to build a custom query in elgg using sql. I want to get the entities only where "cover" exists. I got this so far:

 

$options = array(
    'type' => 'object',
    'subtype' => 'album',
);

$options['wheres'][] ="
            SELECT * FROM {$db_prefix}metadata md
            WHERE WHERE md.entity_guid = e.guid
                AND md.name_id = ".elgg_get_metastring_id('cover', true)."
                AND md.value_id IS NOT NULL";

 

elgg_get_entities($options);

 

I don't know, i am kinda stumped, sql is fairly new to me. Thanks!

  • Elgg has prebuild functions to get entities from metadata.
    Try this

    $options = array(
       'type' => 'object',
       'subtype' => 'album',
       'metadata_name' => 'cover',
       'metadata_value' => true,
    );
            
    elgg_get_entities_from_metadata($options);
  • Thanks, I know that already. I'm trying to do it using Sql

  • Not sure why you wouldn't want to use egef_metadata...

    $options['wheres'][] ="
                EXISTS (SELECT * FROM {$db_prefix}metadata md
                WHERE md.entity_guid = e.guid
                    AND md.name_id = ".elgg_get_metastring_id('cover', true)."
                    AND md.value_id IS NOT NULL");

    or my preference

    $options['joins'][] = "JOIN {$db_prefix}metadata md ON md.entity_guid = e.guid";

    $options['wheres'][] = "md.name_id = {$name_id} AND md.value_id IS NOT NULL"

    Note that if the md row exists the value id should be not null anyway