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

nigelcunningham’s picture

Title: Need a simple 'DISTINCT' (no groupby) » Need a simple 'DISTINCT' (not applied to fields and no groupby)
mustanggb’s picture

Status: Active » Closed (won't fix)