I am trying to build the same view using db_select that Views has built using the UI. The Views UI query is:

SELECT node.title AS node_title, node.nid AS nid, field_collection_item_field_data_field_lan_address.item_id AS field_collection_item_field_data_field_lan_address_item_id, 'field_collection_item' AS field_data_field_ip_address_field_collection_item_entity_typ
FROM 
{node} node
LEFT JOIN {field_data_field_lan_address} field_data_field_lan_address ON node.nid = field_data_field_lan_address.entity_id AND (field_data_field_lan_address.entity_type = 'node' AND field_data_field_lan_address.deleted = '0')
LEFT JOIN {field_collection_item} field_collection_item_field_data_field_lan_address ON field_data_field_lan_address.field_lan_address_value = field_collection_item_field_data_field_lan_address.item_id
WHERE (( (node.status = '1') AND (node.type IN  ('laptop')) ))

however, if I use the following function:

function my_module_views_pre_execute(&$view) {
  if ($view->name == "subnet_view") {
    $query = db_select('node', 'node');
    $query->joinLeft('field_data_field_lan_address', 'field_data_field_lan_address', 'node.nid = field_data_field_lan_address.entity_id AND (field_data_field_lan_address.entity_type = :param1 AND field_data_field_lan_address.deleted = :param2)', array(':param1'=>'node', ':param2'=>'0'));
    $query->joinLeft('field_collection_item', 'field_collection_item_field_data_field_lan_address', 'field_data_field_lan_address.field_lan_address_value = field_collection_item_field_data_field_lan_address.item_id');
    $query->addField('node', 'title', 'node_title');
    $query->addField('node', 'nid', 'nid');
    $query->addField('field_collection_item_field_data_field_lan_address', 'item_id', 'field_collection_item_field_data_field_lan_address_item_id');
    $query->addField('field_collection_item', 'field_data_field_ip_address_field_collection_item_entity_typ');
    $query->condition('node.status', 1, '=');
    $query->condition('node.type', 'laptop', '=');

    $view->build_info['query'] = $query;
    $view->build_info['count_query']=$view->build_info['query'];
  }
}

the query becomes:

SELECT node.title AS node_title, node.nid AS nid, field_collection_item_field_data_field_lan_address.item_id AS field_collection_item_field_data_field_lan_address_item_id, field_collection_item.field_data_field_ip_address_field_collection_item_entity_typ AS field_data_field_ip_address_field_collection_item_entity_typ
FROM 
{node} node
LEFT OUTER JOIN {field_data_field_lan_address} field_data_field_lan_address ON node.nid = field_data_field_lan_address.entity_id AND (field_data_field_lan_address.entity_type = 'node' AND field_data_field_lan_address.deleted = '0')
LEFT OUTER JOIN {field_collection_item} field_collection_item_field_data_field_lan_address ON field_data_field_lan_address.field_lan_address_value = field_collection_item_field_data_field_lan_address.item_id
WHERE  (node.status = '1') AND (node.type = 'laptop') 

with the error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'field_collection_item.field_data_field_ip_address_field_collection_item_entity_typ' in 'field list'

1. It looks like $query->addField('field_collection_item', 'field_data_field_ip_address_field_collection_item_entity_typ'); is not treating field_collection_item as a static. How do I change this?

2. Using joinLeft(), the resulting query uses LEFT OUTER JOIN. Is this an problem?

3. I am missing parentheses in the WHERE statement. Are these required?

How should I fix these problems so that my query matches the one generated by the Views UI?

Comments

damien tournoud’s picture

Status: Active » Fixed

This is an expression. You want something like:

$query->addExpression(':entity_type', 'field_data_field_ip_address_field_collection_item_entity_typ', array(':entity_type' => 'field_collection_item'));
waverate’s picture

Thank you. That was it.

I replaced the $query->addField with the above $query->addExpression.

Status: Fixed » Closed (fixed)

Automatically closed - issue fixed for 2 weeks with no activity.