I have two content types
SMS Contact and a SMS member
The SMS member content type has a node reference field to SMS contact.

When I create a relation between sms contact (main table) and the sms member using the reference field, the sql that is generated is incorrect. See below

SELECT node.nid AS nid, node.title AS node_title, node_field_data_field_member_contact.nid AS node_field_data_field_member_contact_nid
FROM
{node} node
LEFT JOIN {field_data_field_member_contact} field_data_field_member_contact ON node.nid = field_data_field_member_contact.entity_id AND (field_data_field_member_contact.entity_type = 'node' AND field_data_field_member_contact.deleted = '0')
LEFT JOIN {node} node_field_data_field_member_contact ON field_data_field_member_contact.field_member_contact_nid = node_field_data_field_member_contact.nid
WHERE (( (node.status = '1') AND (node.type IN ('sms_contact')) ))
LIMIT 50 OFFSET 0

The line in bold should be
LEFT JOIN {field_data_field_member_contact} field_data_field_member_contact ON node.nid = field_data_field_member_contact.field_member_contact_nid

The main node nid will never be an entity_id on the other table as these are different node types.

Comments

dawehner’s picture

dawehner
Primary language German
commented
Status: Active » Postponed (maintainer needs more info)

I think you are wrong. Here is the bigger part of your query:

LEFT JOIN {field_data_field_member_contact} field_data_field_member_contact ON node.nid = field_data_field_member_contact.entity_id AND (field_data_field_member_contact.entity_type = 'node' AND field_data_field_member_contact.deleted = '0')
LEFT JOIN {node} node_field_data_field_member_contact ON field_data_field_member_contact.field_member_contact_nid = node_field_data_field_member_contact.nid

There you see two parts. The second part is actually the EXACT sql you proposed, that's the join between the two node via the references.

But the first one is needed for the field you want to display.
In general please always try to also provide an export of the view, it really helps to understand what you did.

merlinofchaos’s picture

merlinofchaos commented
Status: Postponed (maintainer needs more info) » Closed (works as designed)

I agree with dereine, you are definitely incorrect.

The main relationship will go from the node that contains the reference to the node that is reference. That is, sms member -> sms contact.

The SQL you quote would actually be a reverse relationship, which gets you sms contact -> sms member.

That's a separate relationship. See http://drupal.org/node/1083902 -- the patch for that got committed to references module only very, very recently.

diricia’s picture

diricia commented

Thank you.
Will keep in mind to create views from the table that contains the reference to the other tables when doing a relationship.