XF 2.0 Advanced where-relation

[Lukas W.]

I'm looking to construct an advanced 'where' statement for my finder. I have a set of ids and want to filter for all entities, that have a relation with all of these ids. The to_many relation is defined in the entity. Is there a way to do a $finder->where([...]) query to check, if these relations exists? Would be optimal if this could be compressed into a single where-call, but I don't mind doing many as well, as the number of ids will likely never be higher than 2 or 3.

To get an example: I have a group of user ids and want to filter all (previously filtered) threads down to those that have a watch-relation with all these users.

 

[Lukas W.]

I have been partially able to solve this by applying the with() function for each id. It is not optimal I guess, but it works for now.

Last thing that would be missing would be to apply a filter that each relation must fullfil a certain condition - for example each watch-relation must have email notifications enabled.

 

[Chris D]

Maybe something like this?

foreach ([1, 2, 3] AS $userId)
{
    $finder->with('Watch|' . $userId, true);
}

That would do multiple inner joins, so it will only be included if the entry actually exists.

Not certain I've totally understood the task at hand but that sounds right. Should produce something like:

SELECT *
FROM xf_thread AS thread
INNER JOIN xf_thread_watch AS watch1 ON
    (thread.thread_id = watch1.thread_id AND user_id = 1)
INNER JOIN xf_thread_watch AS watch2 ON
    (thread.thread_id = watch2.thread_id AND user_id = 2)
INNER JOIN xf_thread_watch AS watch3 ON
    (thread.thread_id = watch3.thread_id AND user_id = 3)

 

[Lukas W.]

Thanks Chris. I've done that at first, and it works so far, but I haven't been able to do the next step, as doing

$finder->where('Watch.send_email', 1)

throws an error, because it is not (yet?) implemented.

I've written a whereSql to achieve that for now, kind of ugly though.

$finder->whereSql("$count = (SELECT SUM(1) FROM xf_thread_watch r WHERE xf_thread.thread_id = r.thread_id AND r.user_id IN({$filters['user_ids']}) AND r.send_email)");

Edit: count is the number of ids in $filters['user_ids'].

 

[Chris D]

You'd have to do this:

foreach ([1, 2, 3] AS $userId)
{
    $finder->with('Watch|' . $userId, true);
    $finder->where('Watch|' . $userId . '.send_email', 1);
}

 

[Lukas W.]

That works! Thanks a lot.