allow DISTINCT on other fields

Not tested it yet, but this code looks like the same sort of problem might be present:

    if ($this->has_aggregate || $this->groupby) {
      $groupby = "GROUP BY " . implode(', ', array_unique(array_merge($this->groupby, $non_aggregates))) . "\n";
      if ($this->having) {
        $having = $this->condition_sql('having');
      }
    }

Still a problem in 3.x:

function views_query_alter_views_query_alter(&$view, &$query) {
  dsm($query);
  
  $query->groupby[] = 'node_title';
}

produces this query:

SELECT node.nid AS nid,
node.title AS node_title
FROM node node
GROUP BY node_title, nid

BTW, the use case for this was this:

I have performance nodes which nodereference both a parent event node, and a venue node.

On the event node, I want to list the venues. In most cases, there are several performances of an event, but most if not all are all in the same venue: eg a play showing 5 nights a week in the same theatre; but could equally be a play that tours several theatres over the course of a month.

So the view takes the nid argument from the current event node, gets performance nodes with that value in the event noderef field, and adds a noderef relationship to get the venue nodes details such as title, google map link, etc -- which intentionally produces multiple rows for the single event node the argument gets.

Obviously, in those multiple rows I get a ton of duplicate venues, and the problem is to eliminate these. The 'distinct' UI option only affects the base field, the nid, which is the nid of the event node.

I've solved it with theming, but it would be cleaner to get the groupby working properly -- and have wider uses too.

I think #903768: groupby on cck fields only works when all values are the same is *perhaps* the same issue, but if it is, it's not expressed correctly.

The problem is not that "groupby on cck fields only works when all values are the same", it is that groupby adds *all fields in the query* to the GROUP BY clause, thus rendering the GROUP BY pointless.

And I don't think it's impossible to fix -- at least I am not convinced it is impossible.

The first step would be to take that big chunk of code I posted -- the monster if/elseif/elseif/elseif... block and *document it* -- that is, write a comment for EACH clause explaining what it's catching and what it's doing with it. Then we stand some chance of figuring out which of those should apply when we're adding a single groupby to the query.

O_o

Further evidence that PostgreSQL is crack...

Okay then the way to do it is with a more complex model for how to add a DISTINCT to the query:

http://www.postgresql.org/docs/9.0/interactive/queries-select-lists.html...

After the select list has been processed, the result table can optionally be subject to the elimination of duplicate rows. The DISTINCT key word is written directly after SELECT to specify this:

SELECT DISTINCT select_list ...

-- we need the ability for the DISTINCT to affect fields other than the view's base.

Though I still maintain PostgreSQL is absolute tosh which we shouldn't bother with :/

... and change the title accordingly.

> Is there a way to use a theme to perform a "distinct" filter?

Absolutely. Loop over your rows in the theme before they are rendered and remove whatever you like.