XF 2.2 Finder & conditions

[kirsty]

I'm having trouble figuring out the right way to convert some SQL to a finder query and how to define the conditions in the relationship.

Some sample data to help out with the explaining....

a is a table with content in:

a_id	stuff
1	alpha
2	bravo
3	charlie
4	delta

b is a table that shows user interaction with the content from table a:

b_id	a_id	user_id
1	2	1
2	4	1
3	1	2
4	2	2
5	3	2

The SQL query

SELECT * FROM a
    LEFT JOIN b ON a.a_id = b.a_id
        AND b.user_id = 1
WHERE
    b.a_id IS NULL

produces, for user_id 1, a list of the content they haven't interacted with:

a_id	stuff	b_id	a_id	user_id
1	alpha
3	charlie

You can see that although each row of table a is related to many rows of table b, if you consider a fixed user_id (SELECT * from b WHERE user_id=n) each row of table a can only be related to a single row of table b, so there is a conditional one to one relationship that is what I'm trying to get at.

I want to do this query from a XenForo finder. A and B are set up as entities equivalent to the SQL tables. I can set up the relationship within the A entity as

$structureA->relations = [
    'B' => [
                    'entity' => 'MyAddOn:B',
                    'type'      => self::TO_MANY,
                    'conditions' => 'a_id',
                    'primary' => true
                ]
]

But this doesn't let me grab multiple rows at a time. (To be clear: I understand why that doesn't work and is limited to returning a single thing.)

What I think I want to be able to do is something like this to mirror the conditions on my SQL join

$structureA->relations = [
    'B' => [
                    'entity' => 'MyAddOn:B',
                    'type'      => self::TO_ONE,
                    'conditions' => [['a_id', '=', 'B.a_id'], ['B.user_id', '=', $user_id]], // this bit isn't right, and I'm not sure where $user_id would be set to the fixed value?
                    'primary' => true
                ]
]

so that then I can do something like

$finder =  $this->finder('My/AddOn:A')
    -> with('B')
    -> where('user_id', \XF:visitor()->user_id)   // these are probably not right either,
    -> where('B.a_id', null)                      // I haven't got as far as this in proper code yet!

Can anyone help me out with what the conditions should look like? Or is this not something I can do with the built in relationships?

 

[Kirby]

'type' => self::TO_ONE,
'conditions' => [['a_id', '=', '$a_id'], 'user_id']

with('B|' . \XF::visitor()->user_id)