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
Comment #1
damien tournoud commentedThis is an expression. You want something like:
Comment #2
waverate commentedThank you. That was it.
I replaced the $query->addField with the above $query->addExpression.