My final goal is to show distinct list of years when nodes are created. I don't want to rewrite view and want to make it generic. So I need to build query like "SELECT DISTNICT YEAR(node.created)..."

I am porting solution to D7 for the issue http://drupal.org/node/844202 which allows to use custom date formats in SQL. I am almost done with it. And I can build following query:

SELECT DATE_FORMAT((DATE_ADD('19700101', INTERVAL node.created SECOND) + INTERVAL 10800 SECOND), '%Y') AS node_created
FROM {node} node

which returns

Post date: 2010
Post date: 2011
Post date: 2011

All I need is to make this query distinct, but when I click Distinct checkbox in the view configuration I get following query, which returns not distinct set of values:

SELECT DISTINCT node.nid AS nid, DATE_FORMAT((DATE_ADD('19700101', INTERVAL node.created SECOND) + INTERVAL 10800 SECOND), '%Y') AS node_created FROM {node} node

There is code in views_plugin_query_default.inc which have following lines:

  function query($get_count = FALSE) {
    // Check query distinct value.
    if (empty($this->no_distinct) && $this->distinct && !empty($this->fields)) {
      $base_field_alias = $this->add_field($this->base_table, $this->base_field);
      $this->add_groupby($base_field_alias);
      $distinct = TRUE;
    }
   ...

Why do we need to add group by field if we are making distinct query? Could we just delete this code and add nid field if needed manually. Other solution is to add new checkbox which will allow to set distinct granularity: "Distinct among return set", "Distinct among table items".

Support from Acquia helps fund testing for Drupal Acquia logo

Comments

dawehner’s picture

dealancer’s picture

Status: Active » Needs review
FileSize
1.6 KB

Here is my patch witch adds additional option to select distinct criteria.

dealancer’s picture

oops

need to replace

'bool' => TRUE,

with

'bool' => FALSE,
dealancer’s picture

The patch works good for me, I can't reproduce errors with distinct and dbtng.

dawehner’s picture

Status: Needs review » Needs work

Can you please make a proper patch format?

dealancer’s picture

Status: Needs work » Needs review
FileSize
3 KB

Sure, here it is.

pavlosdan’s picture

Could not apply patch at #6 using git apply or patch.

After applying the patch manually, it works as advertised.

dealancer’s picture

I can patch it.

I am located at sites/all/modules/contrib now. Views module is in sites/all/modules/contrib/views. Then I do following

$ wget http://drupal.org/files/issues/views-7.x-3.x-1245010-distinct-2.patch
$ patch -p0 < views-7.x-3.x-1245010-distinct-2.patch

If I am located in sites/all/modules/contrib/views I run

$ wget http://drupal.org/files/issues/views-7.x-3.x-1245010-distinct-2.patch
$ patch -p1 < views-7.x-3.x-1245010-distinct-2.patch

GiorgosK’s picture

Applied patch using netbeans apply diff and it work great (selected diff apply on modules directory)

But how do I select the fields to be distinct ?

I selected Distinct fields and then when revisiting the setting it shows Base field

dealancer’s picture

@GiorgosK, what views version are you using? Do you mean settings you have entered is not saved? Have you tried to select Distinct criteria?

dealancer’s picture

I think we need update text for Distinct criteria options to:

  • Selected fields and base field
  • Selected fields only

Also Distinct criteria could be renamed to the Distinct type.

tim.plunkett’s picture

Triggering the testbot.

Status: Needs review » Needs work

The last submitted patch, views-7.x-3.x-1245010-distinct-2.patch, failed testing.

sathishs’s picture

Title: Ability to use DISTINCT as it is, not just DISTINCT for table items » Views distinct option not working in drupal 7

Hi Pals,

I am using drupal 7. My view contains the fields of taxonomy terms and blog posted user. If multiple user post blogs and choosing the same taxonomy term it shows all the user. I am in need of showing the latest user who post the blog and using the same taxonomy term, it need not to show all the users choosing the same taxonomy term.

Thanks in advance.

TomKremer’s picture

hi dealancer.

your patch looks very promising but the distinct criteria is not saved. I'm using it on an attachment view.

do you have any ideas?

Best regards
Tom

fuchen’s picture

Status: Needs work » Needs review

Status: Needs review » Needs work

The last submitted patch, views-7.x-3.x-1245010-distinct-2.patch, failed testing.

Anonymous’s picture

the link views-7.x-3.x-1245010-distinct-2.patch is not working; it's linking to http://drupal.org/comment/reply/1245010/6836492#pift-results-1245010. I can't get the patch. Any help?

MustangGB’s picture

Status: Needs work » Closed (outdated)