I'm pretty sure this is a bug, but slap it down if you don't think so...

I have a view that has a defined relationship, listing nodes of one content type (artist, which has nodereference to another node-type) along with a few fields from the related node type (event, which also has a date field). Everything works as expected if I don't use any arguments, or if I use an argument of NID (from event).

The problem is when I try to use an date argument. After adding the date argument, the JOIN statement's are altered, and an empty set is returned. I spent some time looking into the problem, and I've tracked it to function pre_query() in date_api_argument_handler.inc. Specifically, when I commented out (for testing), the following lines, everything worked again as expected.

        // Explicitly add this table using add_table so Views does not
        // remove it if it is a duplicate, since that will break the query.
        $this->query->add_table($field['table_name'], NULL, NULL, $field['table_name']);
        // Make sure the real field is added to the query.
        $this->query->add_field($field['table_name'], $field['field_name']);

It's clear those lines are there for some purpose, but in my case, they are resulting in a SQL statement that returns nothing.

I've attached copies of the 2 content types, the (non-working) view & it's SQL statement, and a manually altered SQL statement that works correctly. You can see when comparing the 2 SQL statements, that there is a small difference in the JOIN statement.

I'll continue to try to debug this, but I'd be really grateful for any advice or thoughts you might have about why this is happening.

CommentFileSizeAuthor
#2 minus_pre_query.patch1.91 KBbcn
working_sql.txt1.04 KBbcn
broken_sql.txt1.01 KBbcn
view.txt4.43 KBbcn
event_cck_export.txt6.93 KBbcn
artist_cck_export.txt6.14 KBbcn

Comments

davidhernandez’s picture

davidhernandez
Primary language English
Location USA
commented

I also noticed this problem today. I'm using 2.2. The date seems to be ignoring the relationship. When I test it, it returns matches for the date field of the main node of the view, and not the node referenced by the relationship.

bcn’s picture

bcn commented
StatusFileSize
new minus_pre_query.patch1.91 KB

For fun, I removed the entire pre_query(), and have yet to find any errors/problems without it.

   * Make sure the date field is added to the query.
   * 
   * Do this in pre_query() so it will get done even if the argument 
   * is the wildcard, since query() is skipped when the wildcard is used.

The code comments for that function tell us it's necessary to make sure the date field is added in cases where there is a wildcard argument, but from my limited testing on views (with and without wildcard arguments), everything functions without the pre_query() code. Is there a chance something changed in views, and this code isn't necessary anymore? Can you give me an idea what to test, to try to find where the pre_query() is necessary?

In the small chance, I'm not totally lost, attached is patch removing the pre_query()....

russbollesjr’s picture

russbollesjr commented

removing the entire pre_query doesn't work in the April 28th -dev version. You get an 'unknown column' error for your date argument field in the WHERE clause:

user warning: Unknown column 'node_data_field_maintenance_date.field_maintenance_date_value' in 'where clause' query: execute /* russell : execute */ SELECT node.nid AS nid, node.title AS node_title, node_node_data_field_maintenance_node_data_field_maintenance_date.field_maintenance_date_value AS node_node_data_field_maintenance_node_data_field_maintenance_date_field_maintenance_date_value, node_node_data_field_maintenance.nid AS node_node_data_field_maintenance_nid, node_node_data_field_maintenance.type AS node_node_data_field_maintenance_type, node_node_data_field_maintenance.vid AS node_node_data_field_maintenance_vid FROM node node LEFT JOIN content_field_maintenance node_data_field_maintenance ON node.vid = node_data_field_maintenance.vid LEFT JOIN node node_node_data_field_maintenance ON node_data_field_maintenance.field_maintenance_nid = node_node_data_field_maintenance.nid LEFT JOIN content_type_tree node_node_data_field_plant_date ON node.vid = node_node_data_field_plant_date.vid LEFT JOIN content_type_tree node_data_field_plant_date ON node.vid = node_data_field_plant_date.vid LEFT JOIN content_type_maintenance node_node_data_field_maintenance_node_data_field_maintenance_date ON node_node_data_field_maintenance.vid = node_node_data_field_maintenance_node_data_field_maintenance_date.vid WHERE (node.type in ('tree')) AND ((DATE_FORMAT(STR_TO_DATE(node_node_data_field_plant_date.field_plant_date_value, '%Y-%m-%dT%T'), '%Y-%m-%d') = '2009-03-31') AND ((DATE_FORMAT(STR_TO_DATE(node_data_field_maintenance_date.field_maintenance_date_value, '%Y-%m-%dT%T'), '%Y-%m-%d') <= '2009-04-16' AND DATE_FORMAT(STR_TO_DATE(node_data_field_maintenance_date.field_maintenance_date_value, '%Y-%m-%dT%T'), '%Y-%m-%d') >= '2009-04-16'))) LIMIT 0, 10 in C:\Users\Russell Bolles\Desktop\Senior Project\code\sites\all\modules\views\includes\view.inc on line 731.

karens’s picture

karens commented
Status: Active » Closed (duplicate)

There is already another thread about relationship issues at #385688: Views filters & arguments not working when using a date relationship.. It's confusing to have the same problem duplicated in several issues.

jecheve’s picture

jecheve commented

Karen, could you please help us with this blocking bug?

nvahalik’s picture

nvahalik commented

I also ran into this problem while building a Calendar view. I get an error about an undefined index when calling query->add_table. What I can't figure out is why the argument handler is passing a null $relationship AND $join parameters. You can't have both of them be null. add_table() uses $relationship IF $join is NULL and so will try to reference $this->relationships[NULL] since $join is NULL.

I changed line 531 in date_api_filter_handler.inc:

         $this->query->add_table($field['table_name'], NULL, NULL, $field['table_name']);

to:

        $this->query->add_table($field['table_name'], $field['table_name']);

The error is gone and the view appears to work fine.