I was attempting to make a taxonomy based view that uses a term ID as an argument, and uses a relationship to a node to get other taxonomy items from a particular vocabulary that are related. I could almost do it, except that when I enable the DISTINCT option to remove duplicates, the query Views creates is
SELECT DISTINCT(term_data.tid) AS tid,
node_term_node__term_data.name AS node_term_node__term_data_name,
node_term_node__term_data.vid AS node_term_node__term_data_vid,
node_term_node__term_data.tid AS node_term_node__term_data_tid
FROM term_data term_data
LEFT JOIN term_node term_node ON term_data.tid = term_node.tid
INNER JOIN node node_term_node ON term_node.vid = node_term_node.vid
LEFT JOIN term_node node_term_node__term_node ON node_term_node.vid = node_term_node__term_node.vid
LEFT JOIN term_node node_term_node_node_term_node__term_node ON node_term_node.vid = node_term_node_node_term_node__term_node.vid
LEFT JOIN term_data node_term_node__term_data ON node_term_node__term_node.tid = node_term_node__term_data.tid
WHERE (node_term_node__term_data.vid in ('10')) AND (term_data.tid = 115)
GROUP BY tid
instead of
SELECT DISTINCT term_data.tid AS tid,
node_term_node__term_data.name AS node_term_node__term_data_name,
node_term_node__term_data.vid AS node_term_node__term_data_vid,
node_term_node__term_data.tid AS node_term_node__term_data_tid
FROM term_data term_data
LEFT JOIN term_node term_node ON term_data.tid = term_node.tid
INNER JOIN node node_term_node ON term_node.vid = node_term_node.vid
LEFT JOIN term_node node_term_node__term_node ON node_term_node.vid =
node_term_node__term_node.vid
LEFT JOIN term_node node_term_node_node_term_node__term_node ON node_term_node.vid = node_term_node_node_term_node__term_node.vid
LEFT JOIN term_data node_term_node__term_data ON node_term_node__term_node.tid = node_term_node__term_data.tid
WHERE (node_term_node__term_data.vid in ('10')) AND (term_data.tid = 115)
(The distinct is applied to a single field and a group by is added).
A simple hack (which I'm sure breaks other uses of distinct) which achieved the result for me was to comment out
$this->fields[$this->base_field]['distinct'] = TRUE;
$this->add_groupby($this->base_field);
And put in its place
$is_distinct = "DISTINCT";
Then add $is_distinct when the query string is constructed.
Is there a better way that I've missed? If not, could an option be added to do a general DISTINCT without a GROUP_BY?
Thanks for all of your work on Views, by the way.
Comments
Comment #1
nigelcunningham commentedComment #2
mustanggb commented