1

How do you achieve the highlighted 'node' alias when using the Drupal 7 database abstraction language?

This is the original query produced by the view

SELECT node.title AS node_title, node.nid AS nid, node.created AS node_created, **'node' AS field_data_field_reference_code_node_entity_type, 'node' AS field_data_field_employer_node_entity_type, 'node' AS field_data_field_contract_type_node_entity_type, 'node' AS field_data_field_display_location_node_entity_type, 'node' AS field_data_field_salary_description_node_entity_type**
FROM 
{node} node
INNER JOIN {field_data_field_locations} field_data_field_locations ON node.nid = field_data_field_locations.entity_id AND (field_data_field_locations.entity_type = 'node' AND field_data_field_locations.deleted = '0')
LEFT JOIN {workflow_node_history} workflow_node_current ON (  SELECT max(hid) FROM {workflow_node_history} where nid = node.nid and sid != old_sid ) = workflow_node_current.hid
WHERE (( (node.type IN  ('job')) AND (field_data_field_locations.field_locations_tid = '56') AND (workflow_node_current.sid =  '3') ))
ORDER BY node_created DESC
LIMIT 10 OFFSET 0;

Here my current code, which produces the query but cant recreate the aliases in bold with the addfield method. Tried to escape the quotes around 'node' but drupal stripes it out.

$result = db_select('node','node');

    $join = $result->innerJoin('field_data_field_locations','field_data_field_locations',"node.nid = field_data_field_locations.entity_id AND field_data_field_locations.entity_type = 'node' AND field_data_field_locations.deleted = '0'");

    $result->leftJoin('workflow_node_history','workflow_node_current',"(SELECT max(hid) FROM {workflow_node_history} where nid = node.nid and sid != old_sid ) = workflow_node_current.hid");

    $result->addField('','node')
    $result->addField('node','title','node_title');
    $result->addField('node','created','node_created');
    $result->addField('node','nid','nid');
    $result->condition('node.type',array('job'),'IN');
    $result->condition('field_data_field_locations.field_locations_tid','56','=');
    $result->condition('workflow_node_current.sid','3','=');
    $result->orderby('node.created','desc');
4
  • Hello. Your SQL query is incomplete. It lacks FROM, JOIN and so on. It seems you can use aliases all right, so I don't know what's exactly your problem here.
    – Mołot
    Jul 7 '14 at 9:40
  • Hi, i just pasted part of the query as the rest i have recreated. i cant recreate 'node' AS field_data_field_reference_code_node_entity_type part of the query.
    – user33822
    Jul 7 '14 at 9:45
  • Your query now tries to select node column many times, under various aliases. It hardly makes sense to me as there is no node column, as far as I see, and even if there is, you shouldn't need the same data repeated so many times under various aliases. Are you sure that this query, pasted directly to MySQL management software (ie console) selects properly what you want selected?
    – Mołot
    Jul 7 '14 at 10:19
  • This is my point, this is the sql that im getting from views.
    – user33822
    Jul 7 '14 at 10:30
0

Add an expression instead:

$result = db_select('node','node');

$join = $result->innerJoin('field_data_field_locations','field_data_field_locations',"node.nid = field_data_field_locations.entity_id AND field_data_field_locations.entity_type = 'node' AND field_data_field_locations.deleted = '0'");

$result->leftJoin('workflow_node_history','workflow_node_current',"(SELECT max(hid) FROM {workflow_node_history} where nid = node.nid and sid != old_sid ) = workflow_node_current.hid");

// EXPRESSION HERE
$result->addExpression("'node'", 'field_data_field_reference_code_node_entity_type');

$result->addField('node','title','node_title');
$result->addField('node','created','node_created');
$result->addField('node','nid','nid');
$result->condition('node.type',array('job'),'IN');
$result->condition('field_data_field_locations.field_locations_tid','56','=');
$result->condition('workflow_node_current.sid','3','=');
$result->orderby('node.created','desc');

Produces:

SELECT node.title AS node_title, node.created AS node_created, node.nid AS nid, 'node' AS field_data_field_reference_code_node_entity_type
FROM 
{node} node
INNER JOIN {field_data_field_locations} field_data_field_locations ON node.nid = field_data_field_locations.entity_id AND field_data_field_locations.entity_type = 'node' AND field_data_field_locations.deleted = '0'
LEFT OUTER JOIN {workflow_node_history} workflow_node_current ON (SELECT max(hid) FROM {workflow_node_history} where nid = node.nid and sid != old_sid ) = workflow_node_current.hid
WHERE  (node.type IN  (:db_condition_placeholder_0)) AND (field_data_field_locations.field_locations_tid = :db_condition_placeholder_1) AND (workflow_node_current.sid = :db_condition_placeholder_2) 
ORDER BY node.created desc
6
  • I think you made little error in your code, it's orderBy, not orderby.
    – Mołot
    Jul 8 '14 at 11:47
  • @Mołot Not my code, it's copied straight from the question. Interestingly that doesn't throw an error, the SQL output in the answer is taken directly from a copy-paste of the code above it, no changes
    – Clive
    Jul 8 '14 at 11:53
  • "Curiouser and curiouser!"
    – Mołot
    Jul 8 '14 at 12:03
  • Yeah it's annoying the hell out of me actually, can't decide why it works!!
    – Clive
    Jul 8 '14 at 12:03
  • Maybe this will explain a bit?
    – Mołot
    Jul 8 '14 at 12:07
1

In this link you will see that field method doesn't support alias. Instead supports,

$query = db_select('node', 'n');

But to have alias for fields follow this link which will be,

$query->addField('n', 'name', 'label');
$query->addField('n', 'name', 'value');
1
  • thanks, ive edited my question to add more clarity of what im trying to do.
    – user33822
    Jul 7 '14 at 10:17

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.