I am trying to filter out all the events those are currently active i.e. start (from) date <= now AND to date >= now
To achieve this I have created two filters in the view thinking they should be combined as AND in the final query.
However I am getting both the queries combined as OR instead of AND, unable to figure out the reason. the final query is:
SELECT node.nid AS nid, node_data_field_date.field_date_value AS node_data_field_date_field_date_value, node_data_field_date.field_date_value2 AS node_data_field_date_field_date_value2, node.type AS node_type, node.vid AS node_vid, node.title AS node_title, node_revisions.body AS node_revisions_body, node_revisions.format AS node_revisions_format, node.changed AS node_changed FROM node node LEFT JOIN content_type_event node_data_field_date ON node.vid = node_data_field_date.vid LEFT JOIN node_revisions node_revisions ON node.vid = node_revisions.vid WHERE
((node.status = 1) AND (node.type in ('event'))) AND
((DATE_FORMAT(CONVERT_TZ(STR_TO_DATE(node_data_field_date.field_date_value2, '%Y-%m-%dT%T'), 'UTC', 'Australia/Adelaide'), '%Y-%m-%d') >= '2011-12-31') OR
(DATE_FORMAT(CONVERT_TZ(STR_TO_DATE(node_data_field_date.field_date_value, '%Y-%m-%dT%T'), 'UTC', 'Australia/Adelaide'), '%Y-%m-%d') <= '2011-12-31') OR
((DATE_FORMAT(CONVERT_TZ(STR_TO_DATE(node_data_field_date.field_date_value, '%Y-%m-%dT%T'), 'UTC', 'Australia/Adelaide'), '%Y-%m-%d') <= '2011-12-31' AND DATE_FORMAT(CONVERT_TZ(STR_TO_DATE(node_data_field_date.field_date_value2, '%Y-%m-%dT%T'), 'UTC', 'Australia/Adelaide'), '%Y-%m-%d') >= '2011-12-31'))) ORDER BY node_changed ASC
My views setting is as attached.
Please help me solve this issue.
Please inform if the post needs correction.
| Comment | File | Size | Author |
|---|---|---|---|
| #6 | config.jpg | 124.91 KB | vikramaditya234 |
| views-filter.jpg | 109.86 KB | vikramaditya234 |
Comments
Comment #1
penguin25 commentedIn the settings for the date filters (the ones you get by clicking on the cog icon to the right of the filter in the filter list), there is an option called "Method", which allows you to choose between OR and AND for combining multiple date fields in the same query. By default, it's set to OR, which sounds like the behaviour you're seeing.
Comment #2
vikramaditya234 commentedI thought same, but found that, no matter what option ('AND' or 'OR'), I still get the same result.
I also tried changing the option ('AND' and 'OR') for multiple date ('from' and 'to') combined in single filter, but still found no effect in the outcome SQL.
Is there a problem in my views module itself?
Comment #3
rickmanelius commentedI might recommend views 6.x-3.0-rc2. I did a quick test and it works there. If you could test the upgrade on a local development copy, then you could move forward.
And yes, there is probably a simple patch for the 2.x branch.
Comment #4
dawehnerMove to date module, as it's probably not a bug of views itself.
I bet this issue doesn't exist in the dev version of date.
Comment #5
vikramaditya234 commentedsolved.
Upgraded to view-6.x-3.0 and didnt need to place the date filter and passed the current date as the argument.
Comment #6
vikramaditya234 commentedRegret bringing this issue up again.
This wat is troubling me. I am trying to get only the upcoming events i.e. To date is >= now(). I have created a view to give me the output. But I am getting all the events and expired events are NOT getting filtered out. The SQL generated is:
SELECT node.nid AS nid, node_data_field_date.field_date_value AS node_data_field_date_field_date_value, node_data_field_date.field_date_value2 AS node_data_field_date_field_date_value2, node.type AS node_type, node.vid AS node_vid, node.title AS node_title, node_revisions.body AS node_revisions_body, node_revisions.format AS node_revisions_format, node.changed AS node_changed FROM node node LEFT JOIN content_type_event node_data_field_date ON node.vid = node_data_field_date.vid LEFT JOIN node_revisions node_revisions ON node.vid = node_revisions.vid WHERE ((node.status = 1) AND (node.type in ('event'))) AND ((DATE_FORMAT(CONVERT_TZ(STR_TO_DATE(node_data_field_date.field_date_value2, '%Y-%m-%dT%T'), 'UTC', 'Australia/Adelaide'), '%Y-%m-%d') >= '2012-01-09') OR ((DATE_FORMAT(CONVERT_TZ(STR_TO_DATE(node_data_field_date.field_date_value, '%Y-%m-%dT%T'), 'UTC', 'Australia/Adelaide'), '%Y-%m') <= '2012-01' AND DATE_FORMAT(CONVERT_TZ(STR_TO_DATE(node_data_field_date.field_date_value2, '%Y-%m-%dT%T'), 'UTC', 'Australia/Adelaide'), '%Y-%m') >= '2012-01'))) ORDER BY node_changed ASCFrom this I could find the error (as I understood) is that the query is taking the date filter that I am providing (To date >= now) and doing OR with the argument that I am passing to it. So effectively the query is checking if the event has TO date more than todays date OR the event is in the period of a month (this is due to argument being provided). Hence all the events were/will be active for the month are listed.
The screenshot for the configuration is attached.
Please help me understand this behavior or is it due to some mis-configuration from my end.
Comment #7
vikramaditya234 commentedchanging the status
Comment #8
idflood commentedHaving the exact same issue with 7.x-2.x.
My setup is really simple:
- two view filters on the date:
date.start OR date.end >= now
date.start OR date.end <= now
- the result query looks similar to this: WHERE (date.start OR date.end >= now) OR (date.start OR date.end <= now)
- it should be: - WHERE (date.start OR date.end >= now) AND (date.start OR date.end <= now)
The view filter operator is the default AND but the query doesn't respect that. Another issue is that moving one of the date filter in a filter group doesn't change the query.
Comment #9
karens commentedThe code in D7 is a total rewrite. No D6 issue applies to D7. This is a D6 issue.
Comment #10
idflood commentedSorry, I created a new issue with patches for d7: #1399744: Using a custom filter group breaks the filter grouping UI
Comment #11
idflood commentedComment #12
damienmckennaUnfortunately the Drupal 6 version of the Date module is no longer supported. That said, we appreciate that you took time to work on this issue. Should this request still be relevant for Drupal 7 please feel free to reopen it. Thank you.