71

For the background on this, please see http://drupal.org/node/1067802.

Given all that, what situations exists where I might want to use db_select(), or should I be relying solely on db_query?

Improve this question

3 Answers 3

Reset to default
89
+50

There are 5 reasons to use SelectQuery

  • You are building dynamic queries with a varying number of conditions, joins, fields and so on. See field_read_fields() for an example.

  • You want to use so called Extenders. Example extenders are PagerDefault (replaces pager_query()) and TableSort (replaces tablesort_sql()). These allow to add additionally functionality to SelectQuery. See also How do you make sortable tables with a pager with data from a custom table?. An example: node_page_default().

  • You want to allow other modules to alter your queries. Then you can add so called tags and SelectQuery will automatically call a corresponding alter hook for that tag. I'm am heavily relying on this with my Privatemsg module (We already did that in D6 with a custom query builder).

  • If you want/need to use the node_access system to only show nodes the user is allowed to see. Just add the 'node_access' tag to your $query. This replaces db_rewrite_sql().

  • SelectQuery has a few features that help to make your code work the same across all supported databases. For example there is SelectQuery::orderRandom(). And if you have a LIKE condition, ->condition('field', $value, 'LIKE') will make sure that it is always a case insensitive comparison. In D6, you had to use LOWER() for that which was much slower. But AFAIK, there aren't more than this two right now.

If none of these reasons apply for a specific case, use db_query().

Improve this answer
2
  • 1
    Added a fifth point, database portability features like orderRandom() and case insensitive LIKE.
    – Berdir
    Commented Mar 23, 2011 at 23:33
  • 6
    As a sixth reason, I would add cross database compatibility. Oracle queries, for instance, have syntax that differs in some ways from MySQL, Postgres etc. It's much easier to write code to generate the correct syntax from a db_select() than it can be when some not-very-oracle-compatible query code is dumped directly into db_query().
    – BrianV
    Commented Jul 4, 2012 at 20:35
10

The documentation about db_query() says:

Use this function for SELECT queries if it is just a simple query string. If the caller or other modules need to change the query, use db_select() instead.

Improve this answer
2
  • Thanks, but that's quite unspecific. It leaves the definition of 'simple query string' quite open to interpretation. If I'm selecting across 4 tables with 6 joins, is that still a simple query, or should it be done with db_select() instead?
    – Chris Cohen
    Commented Mar 23, 2011 at 10:29
  • 3
    It's not about "simple query" but, about a "simple query string" and simple actually means hardcoded and not dynamic. See my answer for more details :)
    – Berdir
    Commented Mar 23, 2011 at 10:39
9

I always use db_select as I prefer readability, maintainability and cross database compatibility over small performance gains. Moreover, I think the numbers given in the mentioned issue give a wrong image of the overall performance. We are talking a 300 microseconds difference on a query that, when returning more than a single column often run in the multi millisecond range. And I wouldn't be surprised if there is some 1-time only overhead (class loading) and thus that the differences for a full (page) request are far less.

Improve this answer
1

Your Answer

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

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