Fixed Finder where not using withed relation key.

[Liam W]

Affected version

2.0 DP10

If I use there with method on a finder, with a relation key (the | character), and then reference that relation without the key in there where method, it joins the relation without the key. I have to reference the relation in the where with the key as well.

As an example, if I use this:

$this->finder('LiamW\PostMacros:Macro')->with("Sharing|$userId")->with("Enrolment|$userId")
   ->whereOr(['user_id', $userId], ["Sharing.enrolled", 1], ["Enrolment.user_id", '!=', null])
   ->order('title');

The resulting query is this:

SELECT `xf_liamw_post_macros`.*, `xf_liamw_post_macros_shared_Sharing_1`.*, `xf_liamw_post_macros_global_enrolment_Enrolment_2`.*
FROM `xf_liamw_post_macros`
LEFT JOIN `xf_liamw_post_macros_shared` AS `xf_liamw_post_macros_shared_Sharing_1` ON (`xf_liamw_post_macros_shared_Sharing_1`.`macro_id` = `xf_liamw_post_macros`.`macro_id` AND `xf_liamw_post_macros_shared_Sharing_1`.`user_id` = '1')
LEFT JOIN `xf_liamw_post_macros_global_enrolment` AS `xf_liamw_post_macros_global_enrolment_Enrolment_2` ON (`xf_liamw_post_macros_global_enrolment_Enrolment_2`.`macro_id` = `xf_liamw_post_macros`.`macro_id` AND `xf_liamw_post_macros_global_enrolment_Enrolment_2`.`user_id` = '1')
LEFT JOIN `xf_liamw_post_macros_shared` AS `xf_liamw_post_macros_shared_Sharing_3` ON (`xf_liamw_post_macros_shared_Sharing_3`.`macro_id` = `xf_liamw_post_macros`.`macro_id`)
LEFT JOIN `xf_liamw_post_macros_global_enrolment` AS `xf_liamw_post_macros_global_enrolment_Enrolment_4` ON (`xf_liamw_post_macros_global_enrolment_Enrolment_4`.`macro_id` = `xf_liamw_post_macros`.`macro_id`)
WHERE ((`xf_liamw_post_macros`.`user_id` = 1) OR (`xf_liamw_post_macros_shared_Sharing_3`.`enrolled` = 1) OR (`xf_liamw_post_macros_global_enrolment_Enrolment_4`.`user_id` IS NOT NULL))
ORDER BY `xf_liamw_post_macros`.`title` ASC

If I change the whereOr relation fields to include the key, the query is fixed:

SELECT `xf_liamw_post_macros`.*, `xf_liamw_post_macros_shared_Sharing_1`.*, `xf_liamw_post_macros_global_enrolment_Enrolment_2`.*
FROM `xf_liamw_post_macros`
LEFT JOIN `xf_liamw_post_macros_shared` AS `xf_liamw_post_macros_shared_Sharing_1` ON (`xf_liamw_post_macros_shared_Sharing_1`.`macro_id` = `xf_liamw_post_macros`.`macro_id` AND `xf_liamw_post_macros_shared_Sharing_1`.`user_id` = '1')
LEFT JOIN `xf_liamw_post_macros_global_enrolment` AS `xf_liamw_post_macros_global_enrolment_Enrolment_2` ON (`xf_liamw_post_macros_global_enrolment_Enrolment_2`.`macro_id` = `xf_liamw_post_macros`.`macro_id` AND `xf_liamw_post_macros_global_enrolment_Enrolment_2`.`user_id` = '1')
WHERE ((`xf_liamw_post_macros`.`user_id` = 1) OR (`xf_liamw_post_macros_shared_Sharing_1`.`enrolled` = 1) OR (`xf_liamw_post_macros_global_enrolment_Enrolment_2`.`user_id` IS NOT NULL))
ORDER BY `xf_liamw_post_macros`.`title` ASC

Is this the correct behaviour? It seems incorrect to me.

 

[Mike]

Arguably, it should potentially error because doing that kind of action on a TO_MANY relationship isn't going to give you what you expect. On that basis, I'll leave this open for investigation.

However, beyond that, given the code you wrote, the query is technically correct. "X|1" and "X" would be distinct relations. As a comparison, you could join "X|1" and "X|2" in a query and apply different conditions on each. You wouldn't be able to do that unless you could reference the specific relation being requested.

 

[Mike]

This sort of TO_MANY join is now blocked in all cases. Previously there were cases that allowed it that applied here.