Only return published nodes

By tizer on 27 Feb 2015 at 01:46 UTC

I'm editing some old code which has this query:

$result = db_query("SELECT entity_id FROM {field_data_field_category} WHERE field_category_tid = :cat", array(':cat' => $node->field_category['und'][0]['tid']));

I only want to return published nodes so tried to join with node table (code below) - but got this error:

DatabaseStatementBase::join()

$result = db_query("SELECT entity_id FROM {field_data_field_category} WHERE field_category_tid = :cat", array(':cat' => $node->field_category['und'][0]['tid']))
-> join('node', 'n', 'n.nid = entity_id')
-> condition('status', 1,'>');
$result = $query->execute();

Where am I going wrong - I'm pretty sure its to do with n.nid = entity_id - but am not sure how to change it.

Apologies if this is obvious - I'm pretty new to Drupal

Comments

Hi tizer,

taslett commented 27 February 2015 at 02:12

Hi tizer,
You are mixing things up a little.

you can either use db_query() something like:

$query = db_query("SELECT entity_id FROM {field_data_field_category} c INNER JOIN {node} n ON c.entity_id = n.nid WHERE c.field_category_tid = :cat AND n.status > 1", array(':cat' => $node->field_category['und'][0]['tid']))

Or db_select() something like:

$query = db_select('field_data_field_category', 'c')
->fields('c', array('entity_id'))
-> join('node', 'n', 'n.nid = entity_id')
-> condition('n.status', 1,'>')
-> condition('c.field_category_tid', $node->field_category['und'][0]['tid']);

Thanks taslett :)

tizer commented 27 February 2015 at 04:01

Thanks taslett :)

Yes - i can see now I was getting a bit mixed up (a bit!!!)... I've got to admit, there are so many reference sites with what seems like varying syntax - I am getting very mixed up (but getting there)

I got the first one working, thanks :)

But I am having problems with the second... don't answer though - I'll figure it out :)