I have a bit of an issue using Date 7.x-2.0-alpha1 with Views 7.x-3.0-alpha1
I have a basic Event content type with Title, Body and Date. I have allowed both From and To date.
I created a simple view to display all nodes on my site using fields - Title, Node Type and Date (both values).
It lists all of my test content, so I go ahead and add a filter for Date using "greater than or equal to" and "now" for the default value for from date.
The view doesn't list anything at all now. The SQL it is generating is:
SELECT node.title AS node_title, node.nid AS nid, node.type AS node_type, field_data_field_date.delta AS field_data_field_date_delta, field_data_field_date.language AS field_data_field_date_language, field_data_field_date.bundle AS field_data_field_date_bundle, field_data_field_date.field_date_value AS field_data_field_date_field_date_value, field_data_field_date.field_date_value2 AS field_data_field_date_field_date_value2, field_data_field_date.revision_id AS field_data_field_date_revision_id, 'node' AS field_data_field_date_node_entity_type
FROM
{node} node
LEFT JOIN {field_data_field_date} field_data_field_date ON node.nid = field_data_field_date.entity_id AND (field_data_field_date.entity_type = :views_join_condition_0 AND field_data_field_date.deleted = :views_join_condition_1)
WHERE (( (DATE_FORMAT(FROM_UNIXTIME(field_data_field_date.field_date_value), '%Y-%m-%d') > '2011-03-17') ))
Now... as Event is the only content type with a Date field, I added a filter of Node Type = Event to see what it would do. The WHERE clause in the SQL now changes to:
WHERE (( (DATE_FORMAT(FROM_UNIXTIME(field_data_field_date.field_date_value), '%Y-%m-%d') > '2011-03-17') OR (node.type IN ('event')) ))
.... and now my view shows events... all of them... including events with a from date in the past.
I'm not sure why the filter for the date being >= now is not applying correctly to begin with, but I'm also a bit puzzled as to why the filters are using an OR operator rather than an AND. If I just use Published and Node Type of Event it uses an AND. Once I add a Date filter it switches to OR for all filters rather than just the Date ones.
Should I be using a different version of Date with Views 7.x-3.0-alpha1?
| Comment | File | Size | Author |
|---|---|---|---|
| #6 | Date-bug.png | 52.42 KB | Chris Graham |
Comments
Comment #1
ejwettstein commentedI can confirm the same problem. I am not getting any results with my date filter either.
I get roughly the same SQL, but your second example is controlled by a setting when you create the filter. If I create the filter that way and tell it to AND multiple date filters, the date part of the query will be ANDed to the query. Still no results though.
Comment #2
ejwettstein commentedThe problem seems to be in
(DATE_FORMAT(CONVERT_TZ(FROM_UNIXTIME(field_data_field_expires.field_expires_value), 'UTC', ''), '%Y-%m-%d') >= '2011-03-17') )). The field is being stored as text timestamp, not the UNIX timestamp format. Example: 2011-03-18T05:00:00.I don't know if the value should be stored as a Unix timestamp or whether different functions need to be applied in the SQL.
Comment #3
wrd commentedI'm having the same problem as well; subscribing to keep an eye on this.
My I ask how you guys are seeing your SQL? I'd like to compare, but it looks like the views doesn't display it on preview anymore.
Comment #4
Chris Graham commentedIt displays on preview for me. It is below the preview output.
Comment #5
karens commentedTo see if this is working right, you have to use the latest -dev versions of Date, Views, and CTools -- views is changing rapidly and I cannot even attempt to make sure that any combination of anything but the latest version of all of them works.
If there is still a problem there, I need to know what kind of field you are using and how the view is configured to be able to reproduce it.
Comment #6
Chris Graham commentedOn a fresh install of D7 with the dev versions of the modules I can get it to work with the new "Fields" filter, but not the "Date" filter.
Content type
I created the Event content type, keeping everything pretty much at default and added a Date field (allowed optional To Date... everything else default).
I used Devel Generate to create 20 test nodes.
Views
If I create a basic table view of node title and field_date (my Date field), adding a filter for Content Type of Event, I get all 20 nodes returned in the preview.
If I now add a "Fields: Date (field_date) - value" filter, don't change anything on the first settings page and then change the Operator to >= and set a relative Date default of "now", I get the expected results... in my case 8 events are shown (the future ones).
If I instead use the old school method of applying a filter by choosing "Date: Date (node)", set the Date Fields checkboxes to just use "Fields: Date (field_date) - value" and then apply the same Operator and relative date as above... I get all content... even basic page content.
Clicking on the menu beside filters and choosing "and/or" is bringing up the popup saying that it is using the AND operator on the filters... but it obviously isn't.
Somehow the Method setting (see the attached image to see the setting I am talking about) in the Date filter is being used on all filters rather than just applying to dates. This is why it is bringing back pages as well as events, it is saying:
Filter on - Published OR Event OR Date >= now
Comment #7
process91 commentedVerified that #6 gives me the same results. Using the "Field" filter works, the "Date" filter does not.
Comment #8
karens commentedThere a couple completely different issues in this thread now, please stick to one problem per issue. This issue is about not seeing the right connectors in the sql when combining a couple dates in the same filter. I can confirm that problem and I just committed a fix for it. The comments about problems with Unix dates are unrelated to this issue.