0

I have a complex views SQL query, that I have converted to a dynamic query which works perfectly -- my issue is with custom fields:

'node' AS field_data_field_description_node_entity_type,
'node' AS field_data_field_menu_photo_node_entity_type,
'node' AS field_data_field_price_node_entity_type

How do I replicate these, this is the only part I am baffled by... I am doing all of this using hook_views_pre_execute(), here is the original SQL returned by views (providing it so you can see the complexity):

SELECT node.nid AS nid,node.title AS node_title,
taxonomy_term_data_node.name AS taxonomy_term_data_node_name,
taxonomy_term_data_node.vid AS taxonomy_term_data_node_vid,
taxonomy_term_data_node.tid AS taxonomy_term_data_node_tid,
taxonomy_term_data_node.weight AS taxonomy_term_data_node_weight,
taxonomy_term_data_node__taxonomy_vocabulary.machine_name AS taxonomy_term_data_node__taxonomy_vocabulary_machine_name,
taxonomy_term_data_taxonomy_term_hierarchy.name AS taxonomy_term_data_taxonomy_term_hierarchy_name,
taxonomy_term_data_taxonomy_term_hierarchy.vid AS taxonomy_term_data_taxonomy_term_hierarchy_vid,
taxonomy_term_data_taxonomy_term_hierarchy.tid AS taxonomy_term_data_taxonomy_term_hierarchy_tid,
taxonomy_term_data_taxonomy_term_hierarchy.weight AS taxonomy_term_data_taxonomy_term_hierarchy_weight,
taxonomy_term_data_taxonomy_term_hierarchy__taxonomy_vocabulary.machine_name AS taxonomy_term_data_taxonomy_term_hierarchy__taxonomy_vocabul,
taxonomy_index.weight AS taxonomy_index_weight,
'node' AS field_data_field_description_node_entity_type,
'node' AS field_data_field_menu_photo_node_entity_type,
'node' AS field_data_field_price_node_entity_type
FROM {node} node
INNER JOIN (SELECT td.*, tn.nid AS nid FROM {taxonomy_term_data} td
INNER JOIN {taxonomy_vocabulary} tv ON td.vid = tv.vid
INNER JOIN {taxonomy_index} tn ON tn.tid = td.tid
WHERE (tv.machine_name IN  ('*DYNAMIC_VOCAB_MACHINE_NAME*')) ) taxonomy_term_data_node ON node.nid = taxonomy_term_data_node.nid
LEFT JOIN {taxonomy_term_hierarchy} taxonomy_term_data_node__taxonomy_term_hierarchy ON taxonomy_term_data_node.tid = taxonomy_term_data_node__taxonomy_term_hierarchy.tid
LEFT JOIN {taxonomy_term_data} taxonomy_term_data_taxonomy_term_hierarchy ON taxonomy_term_data_node__taxonomy_term_hierarchy.parent = taxonomy_term_data_taxonomy_term_hierarchy.tid
LEFT JOIN {users} users_node ON node.uid = users_node.uid
LEFT JOIN {taxonomy_vocabulary} taxonomy_term_data_node__taxonomy_vocabulary ON taxonomy_term_data_node.vid = taxonomy_term_data_node__taxonomy_vocabulary.vid
LEFT JOIN {taxonomy_vocabulary} taxonomy_term_data_taxonomy_term_hierarchy__taxonomy_vocabulary ON taxonomy_term_data_taxonomy_term_hierarchy.vid = taxonomy_term_data_taxonomy_term_hierarchy__taxonomy_vocabulary.vid
LEFT JOIN {taxonomy_index} taxonomy_index ON node.nid = taxonomy_index.nid
WHERE (( (users_node.uid = '*DYNAMIC_USER_ID*' ) )AND(( (node.status = '1') AND (node.type IN  ('*NODE_TYPE*')) )))

Here is the dynamic query, an exact replica of the above minus the 'node' AS black magic:

// dynamically add vocabulary machine name
$query_vocab = db_select('taxonomy_term_data', 'td');
$query_vocab->innerJoin('taxonomy_vocabulary', 'tv', 'td.vid = tv.vid');
$query_vocab->innerJoin('taxonomy_index', 'tn', 'tn.tid = td.tid');
$query_vocab->condition('tv.machine_name', array($vocab_machine_name));
$query_vocab->fields('td')->addField('tn', 'nid', 'nid');

// main query
$query = db_select('node', 'node');

// joins
$query->innerJoin($query_vocab, 'taxonomy_term_data_node', 'node.nid = taxonomy_term_data_node.nid');
$query->leftJoin('taxonomy_term_hierarchy', 'taxonomy_term_data_node__taxonomy_term_hierarchy', 'taxonomy_term_data_node.tid = taxonomy_term_data_node__taxonomy_term_hierarchy.tid');
$query->leftJoin('taxonomy_term_data', 'taxonomy_term_data_taxonomy_term_hierarchy', 'taxonomy_term_data_node__taxonomy_term_hierarchy.parent = taxonomy_term_data_taxonomy_term_hierarchy.tid');
$query->leftJoin('users', 'users_node', 'node.uid = users_node.uid');
$query->leftJoin('taxonomy_vocabulary', 'taxonomy_term_data_node__taxonomy_vocabulary', 'taxonomy_term_data_node.vid = taxonomy_term_data_node__taxonomy_vocabulary.vid');
$query->leftJoin('taxonomy_vocabulary', 'taxonomy_term_data_taxonomy_term_hierarchy__taxonomy_vocabulary', 'taxonomy_term_data_taxonomy_term_hierarchy.vid = taxonomy_term_data_taxonomy_term_hierarchy__taxonomy_vocabulary.vid');
$query->leftJoin('taxonomy_index', 'taxonomy_index', 'node.nid = taxonomy_index.nid');


// conditions
$query
  ->condition('node.status', 1)
  ->condition('node.type', array($node_type))
  ->condition('users_node.uid', $owner->uid);

// fields
$query->addField('node', 'nid', 'nid');
$query->addField('node', 'title', 'node_title');
$query->addField('taxonomy_term_data_node', 'name', 'taxonomy_term_data_node_name');
$query->addField('taxonomy_term_data_node', 'vid', 'taxonomy_term_data_node_vid');
$query->addField('taxonomy_term_data_node', 'tid', 'taxonomy_term_data_node_tid');
$query->addField('taxonomy_term_data_node', 'weight', 'taxonomy_term_data_node_weight');
$query->addField('taxonomy_term_data_node__taxonomy_vocabulary', 'machine_name', 'taxonomy_term_data_node__taxonomy_vocabulary_machine_name');
$query->addField('taxonomy_term_data_taxonomy_term_hierarchy', 'name', 'taxonomy_term_data_taxonomy_term_hierarchy_name');
$query->addField('taxonomy_term_data_taxonomy_term_hierarchy', 'vid', 'taxonomy_term_data_taxonomy_term_hierarchy_vid');
$query->addField('taxonomy_term_data_taxonomy_term_hierarchy', 'tid', 'taxonomy_term_data_taxonomy_term_hierarchy_tid');
$query->addField('taxonomy_term_data_taxonomy_term_hierarchy', 'weight', 'taxonomy_term_data_taxonomy_term_hierarchy_weight');
$query->addField('taxonomy_term_data_taxonomy_term_hierarchy__taxonomy_vocabulary', 'machine_name', 'taxonomy_term_data_taxonomy_term_hierarchy__taxonomy_vocabul');
$query->addField('taxonomy_index', 'weight', 'taxonomy_index_weight');

If I add a join like join('field_data_field_desciption', 'field_desc', 'field_desc.entity_id = node.nid') I get no results. I might just opt for using field_get_items() in a preprocess function... but would rather not.

Any guidance/help would be highly appreciated :)

  • It should just be another join against a Field table ... kind of like you've mentioned as failing to achieve. I think you just need some more trial and error ... – tenken Jan 18 '15 at 5:31
  • 1
    These three columns have the static value 'node' in every row of the view. Give it a try on the command line: SELECT 'node' AS field_data_field_description_node_entity_type; ... they aren't used anywhere else in the query, so maybe you don't need them? I'd guess views uses it for rendering/theming. If you do need them, you can add them with $query->addExpression('node', 'field_data_field_description_node_entity_type'), etc. api.drupal.org/api/drupal/includes!database!select.inc/function/… – calebtr Jan 18 '15 at 7:58
  • 1
    I think this may be off-topic because this site is for questions, not challenges etc. – Mołot Jan 18 '15 at 11:23
  • @Molot, did you even read the post, or compulsively commented? The word 'challenge' was just used to entice people ;) – in0ni Jan 18 '15 at 14:06
  • @celabtr... interesting. Yes, I think it is used for rendering/theming, and I have thought about just leaving them out but I'd like for it to be complete as I am now missing these fields from my view (though can get them with field_get_items. When adding addExpression to the query, I again get no results. I'll have to read it a bit more... thanks for the tip! – in0ni Jan 18 '15 at 14:15
0

As it turns out the following are SQL Aliases, this is related to the FieldAPI:

'node' AS field_data_field_description_node_entity_type,
'node' AS field_data_field_menu_photo_node_entity_type,
'node' AS field_data_field_price_node_entity_type

They are replicated using addExpression they must be before any conditions and addField. Adding the following to my code above replicates the SQL perfectly:

$query->addExpression("'node'", 'field_data_field_FIELD_NAME_node_entity_type');

Thanks to @calebtr for the help!

| improve this answer | |

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.