XF 2.0 Do I have to create relations on the key?

[AndrewSimm]

I have a list of football players and within their profile (stored in their table) I list their home state abbreviation. I also use this entity on one of my widgets, and I would like to list the full state name. To give me the ability to list the full state I created a "State" table/entity and added all 50 states. In this widget I would like to join the state from player to the manage_short_state from my State table/entity. Basically, I need the relation to not join on keys, so I don't have to replace the state with state id in the players profile.

Here is the relation I am adding to player; however, it's not working.

'State' => [
    'entity' => 'CIS\Backend:State',
    'type' => self::TO_ONE,
    'conditions' => [
                        ['manage_state_short', '=', '$state']
                    ],
    'primary' => true
]

 

[Mike]

There's no obligation that you do joins through primary keys. The $thread->Poll relation is an example that doesn't.

That said, if you're not joining on the primary key, you can't have the "primary" entry in that array. We cache entities by the primary ID and if you specify that, we can do some short circuiting to use a cached entity.

Beyond that, I don't see anything obviously wrong. Have you looked at the queries being built/executed? Are they as you expect?

 

[AndrewSimm]

There's no obligation that you do joins through primary keys. The $thread->Poll relation is an example that doesn't.

That said, if you're not joining on the primary key, you can't have the "primary" entry in that array. We cache entities by the primary ID and if you specify that, we can do some short circuiting to use a cached entity.

Beyond that, I don't see anything obviously wrong. Have you looked at the queries being built/executed? Are they as you expect?

How can I view the query it creates? That would be super helpful.

 

[Mike]

If you're just calling it like the $thread->Poll example above, the easiest way would be to add the query string param _debug=1 to the URL (prefixed with ? or & as needed). You'll then see all of the queries being run and where they were triggered from. You should be able to identify your code from that.