Convert db_query to db_select or EntityFieldQuery

Question

Can anyone help me converting this query using db_query to db_select or EntityFieldQuery? I already tried a lot of things but always getting error. I am new with Drupal, my query works well but I think it's better converting this to db_select or EntityFieldQuery, what you think?

UPDATE

My query:

function get_recent_videos($uid) {
  $query = db_query('SELECT * FROM {node} n, {field_data_field_video} v 
         WHERE n.uid = :uid
         AND n.nid = v.entity_id
         AND v.field_video_value IS NOT NULL
         ORDER BY created DESC
         LIMIT 0, 4', array(':uid' => $uid));

  $result = $query->fetchAll();

  return $result;
}

How can I use a placeholder in the limit? I tried the same approach that I have used in :uid but didn't work.

Thanks a lot.

Answer 1

You can use Entity Query here, with no need to make joins here is the sample code

function get_videos($limit = 4, $uid = 2508) { 
    $query = new EntityFieldQuery();
    $query->entityCondition('entity_type', 'node');
    $query->entityCondition('bundle', 'your_content_type');
    $query->propertyCondition('uid',$uid);
    $query->fieldCondition('field_video','value', 'NULL', '!=');
    $query->propertyOrderBy('created', 'ASC');
    $query->range(0, $limit);
    $result = $query->execute();
    dpm($result);
  if (isset($result['node'])) {
    return $result;
  }
}

The db_select for the same will be

function get_videos($uid, $limit = 4) {
    $query = db_select('node', 'n');
    $query->condition('n.type', 'your_content_type');
    $query->join('field_data_field_video', 'v', 'n.nid = v.entity_id');
    $query->condition('n.uid', $uid, '=');
    $nids = $query->fields('n', array('nid', 'uid','title'))
            ->fields('v', array('field_video_value'))
            ->isNotNull('v.field_video_value')
            ->orderBy('n.created', 'DESC')
            ->range(0, $limit)
            ->execute()
            ->fetchAll();
    return $nids;
}

Please go with Entity Query The reason I can say is you do not need to

have advance database relationship to each other,as clear in the above example Entity Query looks pretty clean as simple object and attributes format while in db_select you need to take care of joins.

Answer 2

Yes, if you're querying entity/field data you should pretty much always use an EntityFieldQuery in place of raw SQL. If you're not querying entity/field data, and need to build up a dynamic query, then db_select() is probably the way to go.

I'd also recommend reading the DB API docs and making sure you understand how to use db_query() properly - currently your code is likely to be susceptible to an SQL injection attack, as you're not using parameterized queries.

Answer 3

You could use EntityFieldQuery Extra Fields, there's also a good example included.

Here is a quote about it (from the module's project page):

It allows you to use addField within an EntityFieldQueryExtraFields object (extends EntityFieldQuery objects). Initially EntityFieldQuery only gives you back the entity id, bundle and entity type. This module adds an extra layer on top of that without doing some extra entity_loads! Important if you want to work with massive amounts of data and you want to reduce the sql queries. Since EFQ already queries the database for this information, why not alter that query to also give you some additional fields. This means that not a single extra query will be performed to get that data.