Multigroup filter producing duplicates with relationships
| Project: | Content Construction Kit (CCK) |
| Version: | 6.x-3.x-dev |
| Component: | Views Integration |
| Category: | bug report |
| Priority: | normal |
| Assigned: | Unassigned |
| Status: | active |
Jump to:
I'm not sure if this is an issue with Nodereferrer or CCK -- happy to have this issue moved if it is in fact Nodereferrer. I spent some time debugging the SQL produced, and I have found the problem in the query, but I'm not sure where the issue is in the code.
In short, I am starting with Nodes of type INVOICE, then adding a relationship (provided by Nodereferrer) to add a Multigroup to the view (belonging to Nodes of type PAYMENT). There are two fields in this Multigroup: Applied Amount 'applyto_amount' (Integer), and Invoice Reference 'applyto_order' (Node Reference to INVOICE). I am using the Multigroup Filter to synchronize deltas.
From what I can see in the following query, Nodereferrer is first bringing in the 'applyto_order' table (aliased as node2) via nid, and then the PAYMENT content-type table also via nid. The problem seems to be that subsequently the 'applyto_order' table is joined a second time (via vid) followed by 'applyto_amount' (also via vid). The deltas are sync'd on this second join, but since we've already brought in 'applyto_amount' once, and we are not syncing between the first and second instance, duplicates result.
Here is the initial query:
SELECT ...
FROM node node
LEFT JOIN content_field_payment_applyto_order node2 ON node.nid = node2.field_payment_applyto_order_nid
LEFT JOIN node node_node ON node2.nid = node_node.nid
INNER JOIN content_type_order node_data_field_order_type ON node.vid = node_data_field_order_type.vid
LEFT JOIN content_field_payment_applyto_order node_node_node_data_field_payment_applyto_order ON node_node.vid = node_node_node_data_field_payment_applyto_order.vid
LEFT JOIN content_field_payment_applyto_amount node_node_node_data_field_payment_applyto_amount ON node_node.vid = node_node_node_data_field_payment_applyto_amount.vid AND (node_node_node_data_field_payment_applyto_order.delta = node_node_node_data_field_payment_applyto_amount.delta)
LEFT JOIN content_type_order node_data_field_order_createdate ON node.vid = node_data_field_order_createdate.vid
WHERE ...And here is 'fixed' Query:
SELECT ...
FROM node node
LEFT JOIN content_field_payment_applyto_order node2 ON node.nid = node2.field_payment_applyto_order_nid
LEFT JOIN node node_node ON node2.nid = node_node.nid
INNER JOIN content_type_order node_data_field_order_type ON node.vid = node_data_field_order_type.vid
LEFT JOIN content_field_payment_applyto_order node_node_node_data_field_payment_applyto_order ON node_node.vid = node_node_node_data_field_payment_applyto_order.vid AND (node_node_node_data_field_payment_applyto_order.delta = node2.delta)
LEFT JOIN content_field_payment_applyto_amount node_node_node_data_field_payment_applyto_amount ON node_node.vid = node_node_node_data_field_payment_applyto_amount.vid AND (node_node_node_data_field_payment_applyto_order.delta = node_node_node_data_field_payment_applyto_amount.delta)
LEFT JOIN content_type_order node_data_field_order_createdate ON node.vid = node_data_field_order_createdate.vid
WHERE ...I simply added a second synchronization to ensure the first and second joins of the 'applyto_amount' tables shared the same delta. This could of course also be accomplished by avoiding the second join.
Any ideas on where this might be going wrong?
Thanks!
#1
It looks like the problem happens because the table content_field_payment_applyto_order is joined twice. Why? Not sure, but it looks like someone is not adding the join correctly, and Views is not able to figure out the table is already included.
I'm afraid I won't have the time to debug this for a while though. One reason is lack of time right now, another reason is I do not use Nodereferrer module (mainly because it duplicates relationship data, which is prone to inconsistencies, and we have resolved node back references using a different approach based on Node relationships module and a custom relationships handler as discussed here).
#2
Thanks Markus - I will try your approach and see if I have more luck with that.
#3
I went ahead and removed the Nodereferrer module, and instead am using the soon-to-be-released Nodereference Reverse module (http://drupal.org/node/552944) you recommended which basically adds additional 'reversed' node referrer fields within views relationships. Simple and intuitive.
I now believe the problem lies with how the Multigroup filter is working. I am posting two SQL snippets below to illustrate.
Before enabling the Multigroup Filter (with relationship)
SELECT ...FROM node node
LEFT JOIN content_field_payment_applyto_order node2 ON node.nid = node2.field_payment_applyto_order_nid
LEFT JOIN node node_node ON node2.vid = node_node.vid
INNER JOIN content_type_order node_data_field_order_type ON node.vid = node_data_field_order_type.vid
LEFT JOIN content_type_order node_data_field_order_createdate ON node.vid = node_data_field_order_createdate.vid
LEFT JOIN content_field_payment_applyto_amount node_node_node_data_field_payment_applyto_amount ON node_node.vid = node_node_node_data_field_payment_applyto_amount.vid
WHERE ...
And after enabling:
SELECT ...FROM node node
LEFT JOIN content_field_payment_applyto_order node2 ON node.nid = node2.field_payment_applyto_order_nid
LEFT JOIN node node_node ON node2.vid = node_node.vid
INNER JOIN content_type_order node_data_field_order_type ON node.vid = node_data_field_order_type.vid
LEFT JOIN content_field_payment_applyto_order node_node_node_data_field_payment_applyto_order ON node_node.vid = node_node_node_data_field_payment_applyto_order.vid
LEFT JOIN content_field_payment_applyto_amount node_node_node_data_field_payment_applyto_amount ON node_node.vid = node_node_node_data_field_payment_applyto_amount.vid AND (node_node_node_data_field_payment_applyto_order.delta = node_node_node_data_field_payment_applyto_amount.delta)
LEFT JOIN content_type_order node_data_field_order_createdate ON node.vid = node_data_field_order_createdate.vid
WHERE ...
The table "content_field_payment_applyto_order" is added to the query as the first join as a result of adding a relationship. The Multigroup Filter adds it a second time, resulting in duplicates.
From what I can tell, this line is added by the filter and should not be:
LEFT JOIN content_field_payment_applyto_order node_node_node_data_field_payment_applyto_order ON node_node.vid = node_node_node_data_field_payment_applyto_order.vidAnd this line:
LEFT JOIN content_field_payment_applyto_amount node_node_node_data_field_payment_applyto_amount ON node_node.vid = node_node_node_data_field_payment_applyto_amount.vid AND (node_node_node_data_field_payment_applyto_order.delta = node_node_node_data_field_payment_applyto_amount.delta)Should instead be:
LEFT JOIN content_field_payment_applyto_amount node_node_node_data_field_payment_applyto_amount ON node_node.vid = node_node_node_data_field_payment_applyto_amount.vid AND (node2.delta = node_node_node_data_field_payment_applyto_amount.delta)Basically, it appears as though the filter is duplicating a relationship by adding a redundant table-alias for a table that is already present in the query in order to perform delta synchronization, when it should be using the already-existing table / table alias.
I hope this makes sense.
#4
Take a look also at http://drupal.org/project/backreference
#5
I've narrowed it down (so to speak) to the following two lines of code:
nodereference_reverse_view_handler_relationship.inc
$this->table_alias = $this->query->add_table($this->table, $this->relationship, $join);content_multigroup_handler_filter.inc
$base_alias = $this->query->ensure_table($this->options['content_multigroup_master_field'], $this->relationship);It basically looks as though the call to ensure_table() by the multigroup filter is not returning the table alias added by the nodereference_reverse_view_handler and is instead adding the table again. I'm assuming this has something to do with the $join parameter specified when calling add_table(), but I'm still trying to wrap my head around it. Diving into views code is proving a bit dizzying.
#6
I am seeing the same issue with plain node-reference fields with multiple values.
#7
I have abandoned this approach for now and went at the problem from a different direction -- there are just too many still-in-development or experimental pieces of code involved.
I could not find a 'simple' solution to this problem, but fundamentally the problem is this:
A normal 'forward' relationships works like this: Node A (nodereference) --> Joins To --> Node B (nid/vid) --> Joins To --> Node B's CCK Multi-Value Field Table (nid/vid). In this scenario, a 'Relationship' is defined in views as being between Node A and Node B (the cck fields and needed joins are then handled 'under the hood'). The existence of these relationships are generally checked to determine whether or not a table join already exists, or needs to be added.
This model breaks down in reverse, because views is primarily designed to define relationships between Nodes. The 'reverse' relationship looks something like this: Node A (nid) <-- Joins To <-- Node B's CCK Field Table (nodereference) --> Joins To Node B (nid/vid). In this scenario, the 'Relationship' is again defined as being directly between Node A and Node B, but this is not correct. The relationship is ACTUALLY between Node A and Node B CCK Field Table. Ultimately, because the relationship is not recorded correctly, views does not know that the multi-value table has already been joined, and ends up joining it again (in my case, this was occurring when the multi-group sync filter was added).
I experimented with tweaking the Nodereference Reverse views plugin to define the relationship explicitly as between Node A and Node B's CCK Field Table, but this caused problems because views auto-magically includes certain fields along with the relationship that only exist in the Node Table (and not in the CCK Field Table). Trying to modify this particular behavior seemed a little too daunting for me.