4

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.

2
  • in order to make limit as variable you can use db_query_range() or range() with db_query() here are some pointers for the issue you are facing with db_query 1,2
    – arpitr
    Oct 1, 2013 at 10:28
  • Thanks, for now i will use db_query_range until i find a way to convert the query to db_select method.
    – Mambley
    Oct 1, 2013 at 11:26

3 Answers 3

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.

1
  • Thanks @arpitr, its working on both cases, i still however like more the db_select ;)
    – Mambley
    Oct 2, 2013 at 14:13
5

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.

7
  • Hello Clive, thanks a lot for your answer. I will read the DB API docs, thanks for the tip. If is not too work, can you give me an idea what i need to fix to avoid SQL injection on this query? Once again, thanks for your help.
    – Mambley
    Sep 30, 2013 at 10:14
  • There are many, many examples on the db_query() docs page, that's where I'd start
    – Clive
    Sep 30, 2013 at 14:35
  • i update the first post, can you please tell me if it is now secure? Thanks.
    – Mambley
    Oct 1, 2013 at 8:33
  • Indeed it is :)
    – Clive
    Oct 1, 2013 at 8:33
  • Thanks a lot @Clive :). Do you know how can i solve the limit issue?
    – Mambley
    Oct 1, 2013 at 8:39
1

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.

1
  • Will check this later, seems site is offline, thanks.
    – Mambley
    Sep 30, 2013 at 10:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.