• This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn more.

Fixed Finder where not using withed relation key.

Affected version
2.0 DP10

Liam W

Well-known member
#1
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:

PHP:
$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:

SQL:
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:

SQL:
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

XenForo developer
Staff member
#2
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

XenForo developer
Staff member
#3
This sort of TO_MANY join is now blocked in all cases. Previously there were cases that allowed it that applied here.