Not a bug Finder not returning results when Entity Relations specifies a Conditions using explicit column IDs to join on

[Kevin]

Affected version

2.1 B5

The Cliff Notes version is that if I define a relation to an entity and in the conditions if I explicitly define the column IDs to join on when the SQL query gets generated then no rows are found because the query uses ' to wrap the right side of the join statement column name instead of ` like the rest of the query is using. It's because the join function XF\Mvc\Entity\Finder is using the quote function from XF\Db\AbstractAdapter and is where the ' is coming from. But... even if the ` character was used then if the same column name is explicitly specified then an error would still get returned because the join statement that gets built is not qualified with the table name and so an 'ambigous column' error is thrown.

The details...

I'm using a new side-table that is a one-to-one relation to the Attachments table. At the moment I'm using 'attachment_id' to join the new table to Attachments but ran into an issue after changing the column name in the side table to 'content_attachment_id' in that I was no longer getting any rows returned when nothing had changed other than the column name. Using dumpSimple, so I could see the raw queries, at first glance the query with the changed column name appeared to be fine but the query as-is wouldn't run for me when trying it manually and is when I noticed the ' instead of `. As an experiment I changed the column name back to just "attachment_id" in the side table but left the entity relations conditions explicitly defined yet for the join, still got no results in XF. Changed the conditions back to just the single column name again and then it worked as expected.

With the side table using "attachment_id" at the column name and the entity relations condition using just the column name...

$structure->relations = [
   'Attachment' => [
      'entity' => 'XF:Attachment',
      'type' => self::TO_ONE,
      'conditions' => 'attachment_id',
      'primary' => true,
              'with' => 'Data'
   ]
];

... I got this as the query which works fine (note the quotes used for = `xf_cinvin_portal_featured_attachment`.`attachment_id` in the inner join to the attachment table and that it is qualified with the table name)...

SELECT `xf_cinvin_portal_featured_attachment`.*, `xf_attachment_Attachment_1`.*, `xf_attachment_data_Data_2`.*
FROM `xf_cinvin_portal_featured_attachment`
INNER JOIN `xf_attachment` AS `xf_attachment_Attachment_1` ON (`xf_attachment_Attachment_1`.`attachment_id` = `xf_cinvin_portal_featured_attachment`.`attachment_id`)
LEFT JOIN `xf_attachment_data` AS `xf_attachment_data_Data_2` ON (`xf_attachment_data_Data_2`.`data_id` = `xf_attachment_Attachment_1`.`data_id`)
WHERE (`xf_attachment_Attachment_1`.`unassociated` <> 1)
ORDER BY `xf_cinvin_portal_featured_attachment`.`featured_date` DESC
LIMIT 10

... but when I use this relations definition...

$structure->relations = [
   'Attachment' => [
      'entity' => 'XF:Attachment',
      'type' => self::TO_ONE,
      'conditions' => [ ['attachment_id','=','attachment_id'] ],
      'primary' => true,
              'with' => 'Data'
   ]
];

... I end up with this as the query statement which does not work (note the quotes for = 'attachment_id' in the inner join to the attachment table and that the column name is not qualified with the table name).

SELECT `xf_cinvin_portal_featured_attachment`.*, `xf_attachment_Attachment_1`.*, `xf_attachment_data_Data_2`.*
FROM `xf_cinvin_portal_featured_attachment`
INNER JOIN `xf_attachment` AS `xf_attachment_Attachment_1` ON (`xf_attachment_Attachment_1`.`attachment_id` = 'attachment_id')
LEFT JOIN `xf_attachment_data` AS `xf_attachment_data_Data_2` ON (`xf_attachment_data_Data_2`.`data_id` = `xf_attachment_Attachment_1`.`data_id`)
WHERE (`xf_attachment_Attachment_1`.`unassociated` <> 1)
ORDER BY `xf_cinvin_portal_featured_attachment`.`featured_date` DESC
LIMIT 10

 

[Mike]

In short, that's not the correct syntax for the meaning you want. When you get into the "values" portion of the join, it will default to a literal value. You need to prefix it with a $ to flip into a dynamic value (which depending on context, will either become a reference for a join or read the current value from the entity).

Here's an example from the Thread entity which uses both:

'DeletionLog' => [
   'entity' => 'XF:DeletionLog',
   'type' => self::TO_ONE,
   'conditions' => [
      ['content_type', '=', 'thread'],
      ['content_id', '=', '$thread_id']
   ],
   'primary' => true
]

The first condition will be (roughly) DeletionLog.content_type = 'thread'. The second would get turned into DeletionLog.content_id = thread.thread_id as a join.

 

[Kevin]

In short, that's not the correct syntax for the meaning you want.

Well, nuts. Thanks for the explanation.




EDIT: The worst part? In the same add-on in a different entity I had it correct. This is what happens when I don't have any coffee for several days during the holidays.