I need to select nodes (1) of a certain content type and (2) satisfying a test against a custom field's value. Pseudo-code would be:

select all nodes of type TYPE_X which are published and where MY_FIELD = 'x'

I tried that with the EntityFieldQuery suggested here: How do I query a content type with custom fields in the 'condition' statement but Drupal/php crashes with "Fatal error: Allowed memory size of x bytes exhausted..."

So - what would be the correct code to use? I would appreciate a query which takes revisions into account.


In Views, you could do this:

  1. Create a view, filter on node TYPE and node PUBLISHED
  2. Create an argument/contextual filter using your CUSTOM FIELD
  3. In Fields, select NID (and/or TITLE or whatever you want to work with in the node(s))

Preview the view, putting FILTERONTHIS in the arguments/contextual filter area.

Look at the SQL it generated in the Preview and cut/paste it into whatever you need, substituting FILTERONTHIS for a variable of your chosing. Or, save the view and then use views_get_view_result() to access the result.

  • Thanks Jimajamma. I did as you recommended (although I originally used direct filter method rather than contextual filter, but the resulting query was obviously quite similar). Now I would like to rewrite the query the Drupal way (as in: $query = db_select('node', 'n') etc.) My first attempts are erroneous and when I dpm them, they produce quite different statements.
    – Artur
    Jul 29 '12 at 21:13
  • The Views query is SELECT node.nid AS nid, node.title AS node_title FROM {node} node LEFT JOIN {field_data_field_nick} field_data_field_nick ON node.nid = field_data_field_nick.entity_id AND (field_data_field_nick.entity_type = 'node' AND field_data_field_nick.deleted = '0') WHERE (( (field_data_field_nick.field_nick_value = 'a10') )AND(( (node.status = '1') AND (node.type IN ('product')) )))
    – Artur
    Jul 29 '12 at 21:14
  • Would it be too much if I asked for help in rewriting? This is my first Database API approach and I'm sure I could learn by example. Thanks.
    – Artur
    Jul 29 '12 at 21:15
  • I would be glad to if it were a D6 example as I have that down pat, but if you are on D7 (which I am suspecting) I won't be of much help because I am just now getting started with it.
    – Jimajamma
    Jul 29 '12 at 22:21
  • but views_get_view_result() isn't that horrid...and is a lot easier to manage if you need to rework your queries at a later date...
    – Jimajamma
    Jul 29 '12 at 22:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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