I want to add a query as a table name, but since views doesn't alias the table-as-query in add_table, the generated SQL is invalid (it includes the subselect multiple times int the query). The attached patch, moves $joininfo up a few lines in query(), passes $joininfo to get_table_name, and adds one check in get_table_name to look for the table alias ('right' => 'alias').
Please advise if there is a better way to do this. My code that uses this is as follows:
$sql = "(SELECT vfsn.nid FROM node vfsn LEFT join search_index vfsx on vfsn.nid=vfsx.sid WHERE vfsx.fromsid = 0 AND (". implode(' OR ', $and_clause) .") GROUP BY vfsn.nid HAVING COUNT(*) = ". count($and_clause) .")"; $join = array( 'left' => array( 'table' => 'node', 'field' => 'nid' ), 'right' => array( 'field' => 'nid', 'alias' => 'vfs', ) ); $query->add_table($sql, false, 1, $join); $query->add_where("vfs.nid IS NOT NULL", $arguments);
I'm not sure yet how portable this code is. It works on mysql 5.
This request is for views_fastsearch. See also #143160.