XF 2.0 Finder and order by a column of a joined table via ->with()

[Scandal]

I have this issue during the use of XF2 Finder.
Here is an example use of the Finder inside a controller:

$this->finder('XF:User')->with('Ban', false)->whereOr(['secondary_group_ids', '=', $bannedusergroupid],
                                                      ['user_group_id', '=', $bannedusergroupid])->where('myfield', '!=', '')->order('register_date', 'DESC')->limitByPage($page, $perpage)->fetch();

-> the myfield != '' is a column on xf_user table. All ok with it! And I don't have to use XF:Ban finder as initial finder but to use with() to make the JOIN.
-> the order by register_date works fine cause it is a column on xf_user table.
-> The issue: I want to order by ban_date which is a column on xf_user_ban table but I'm receiving error Unknown column ban_date on XF:User

Is there a way to order by a LEFT JOIN's table column?

 

[nocte]

This should work:

->order('Ban.ban_date')

 

[Scandal]

omg I had tested "ban.ban_date" and "xf_user_ban.ban_date" instead which not worked. "Ban.ban_date" worked, thanks

 

[Xon]

Your secondary_group_ids clause is hilariously fragile, and likely will not work.

secondary_group_ids is a comma separated list, so you need something like;

$bannedusergroupid = \XF::db()->quote($bannedusergroupid);
$finder->whereSql("FIND_IN_SET($bannedusergroupid, 'secondary_group_ids')")

 

[Scandal]

Hello Xon!
Thanks for noticed it! For some reason I thought that Finder, as a special data manager would done automatically the FIND_IN_SET. But I think that I had tested it with accounts which had only one secondary usergroupid, so for those was working, but no for accounts with > 1 secondaries.

Your solution is working fine, but only if I added $finder->columnSqlName inside the whereSql, so we have:

$finder->whereSql("FIND_IN_SET(". $finder->quote($bannedusergroupid) .", ". $finder->columnSqlName('secondary_group_ids').") OR ". $finder->columnSqlName('user_group_id'). " = ". $finder->quote($bannedusergroupid) ."")

... cause by this way the "`xf_user`.`secondary_group_ids`" (red) is added to the query.

Thanks again,
Peter