current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Drupal Answers is a question and answer site for Drupal developers and administrators. It's 100% free, no registration required.
up vote 0 down vote favorite
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');
share|improve this question
    
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

2 Answers 2

up vote 0 down vote accepted

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
share|improve this answer
    
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
up vote 1 down vote

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');
share|improve this answer
    
thanks, ive edited my question to add more clarity of what im trying to do. –  user33822 Jul 7 '14 at 10:17

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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