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".
Comment | File | Size | Author |
---|---|---|---|
#6 | views-7.x-3.x-1245010-distinct-2.patch | 3 KB | dealancer |
#2 | views-7.x-3.x-1245010-distinct-1.patch | 1.6 KB | dealancer |
Comments
Comment #1
dawehnerPlease read #863478: Distinct is not supported by dbtng
Comment #2
dealancer CreditAttribution: dealancer commentedHere is my patch witch adds additional option to select distinct criteria.
Comment #3
dealancer CreditAttribution: dealancer commentedoops
need to replace
with
Comment #4
dealancer CreditAttribution: dealancer commentedThe patch works good for me, I can't reproduce errors with distinct and dbtng.
Comment #5
dawehnerCan you please make a proper patch format?
Comment #6
dealancer CreditAttribution: dealancer commentedSure, here it is.
Comment #7
pavlosdan CreditAttribution: pavlosdan commentedCould not apply patch at #6 using git apply or patch.
After applying the patch manually, it works as advertised.
Comment #8
dealancer CreditAttribution: dealancer commentedI 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
Comment #9
GiorgosKApplied 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
Comment #10
dealancer CreditAttribution: dealancer commented@GiorgosK, what views version are you using? Do you mean settings you have entered is not saved? Have you tried to select Distinct criteria?
Comment #11
dealancer CreditAttribution: dealancer commentedI think we need update text for Distinct criteria options to:
Also Distinct criteria could be renamed to the Distinct type.
Comment #12
tim.plunkettTriggering the testbot.
Comment #14
sathishs CreditAttribution: sathishs commentedHi 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.
Comment #15
TomKremer CreditAttribution: TomKremer commentedhi 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
Comment #16
fuchen CreditAttribution: fuchen commented#6: views-7.x-3.x-1245010-distinct-2.patch queued for re-testing.
Comment #18
Anonymous (not verified) CreditAttribution: Anonymous commentedthe 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?
Comment #19
MustangGB CreditAttribution: MustangGB commented