XF 2.1 Unknown relation or alias Admin accessed on xf_user

[asprin]

As part of my addon, I have a brand new table called xf_fc_events. In this table, there is a column named created_by in which I will be storing the logged in user's user_id.

Now in order for the XF:User entity to be aware of this relation, I created a code_event_listener


Code inside Listener.php file is as follows:

namespace FC;

use XF\Mvc\Entity\Entity;

class Listener
{
    public static function userEntityStructure(\XF\Mvc\Entity\Manager $em, \XF\Mvc\Entity\Structure &$structure)
    {
       
        $structure->relations = [
            'EventCreator' => [
                'entity' => 'FC:Events',
                'type' => Entity::TO_MANY, // one user can create multiple events
                'conditions' => 'user_id',
                'key' => 'created_by'
            ]
        ];
    }
}

Now, when I use the following to fetch all events:

<?php

namespace FC\Repository;

use XF\Mvc\Entity\Finder;
use XF\Mvc\Entity\Repository;

class Events extends Repository
{
    /**
     * @return Finder
     */
    public function fetchAllEvents()
    {
        $visitor = \XF::visitor();

        $finder = $this->finder('FC:Events');
        $finder
            ->setDefaultOrder('sys_created_on', 'DESC')
            ->with('User', true);

        return $finder;
    }
}

I'm getting the following error:


What am I doing wrong here?

If it helps, xf_fc_events currently has only 1 record and created_by column value is "1" which is an admin account with username "admin"

 

[Naz]

You're overwriting all of the existing relations with your own. Change your listener to something like this:

namespace FC;

use XF\Mvc\Entity\Entity;

class Listener
{
    public static function userEntityStructure(\XF\Mvc\Entity\Manager $em, \XF\Mvc\Entity\Structure &$structure)
    {
        $structure->relations['EventCreator'] = [
            'entity' => 'FC:Events',
            'type' => Entity::TO_MANY, // one user can create multiple events
            'conditions' => 'user_id',
            'key' => 'created_by'
        ];
    }
}

 

[asprin]

You're overwriting all of the existing relations with your own. Change your listener to something like this:

namespace FC;

use XF\Mvc\Entity\Entity;

class Listener
{
    public static function userEntityStructure(\XF\Mvc\Entity\Manager $em, \XF\Mvc\Entity\Structure &$structure)
    {
        $structure->relations['EventCreator'] = [
            'entity' => 'FC:Events',
            'type' => Entity::TO_MANY, // one user can create multiple events
            'conditions' => 'user_id',
            'key' => 'created_by'
        ];
    }
}

Ah..that makes sense. I'm not at my desk right now, so I will have to wait till I return home and test it.

In the meantime, could you shed some light on the following options used when defining a relation?

1. conditions (my assumption is that we put the column name (primary key) of the referenced/parent table)
2. key (my assumption is that we put the column name which will reference the primary key)
3. primary (no idea when to use it and when to use true or false as value)

 

[asprin]

So I changed some code and used the following:

// this is for xf_fc_events table
$structure->relations = [
    'Creator' => [
        'entity' => 'XF:User',
        'type' => self::TO_ONE,
        'conditions' => [
            ['user_id', '=', 'created_by']
        ],
        'primary' => true
    ],
];

After adding _debug=1 in the query string, the query generated was as follows:

SELECT `xf_fc_events`.*, `xf_user_Creator_1`.*
FROM `xf_fc_events`
INNER JOIN `xf_user` AS `xf_user_Creator_1` ON (`xf_user_Creator_1`.`user_id` = 'created_by')

ORDER BY `xf_fc_events`.`sys_created_on` DESC

which is about 95% close to what I was expecting - just that the ON condition should read
(`xf_user_Creator_1`.`user_id` = `xf_fc_events`.`created_by`). Currently, it's checking for a value of "created_by" rather than the column entity. How can I change that?

 

[Lawrence]

Hi. Why are you adding your xf_fc_events entity to the User entity structure?

You need to add the User relation to your own entities structure, as an example:

$structure->relations = [
            'User' => [
                'entity' => 'XF:User',
                'type' => self::TO_ONE,
                'conditions' => 'user_id',
                 'primary' => true
            ]
 ];

which is about 95% close to what I was expecting - just that the ON condition should read
(`xf_user_Creator_1`.`user_id` = `xf_fc_events`.`created_by`). Currently, it's checking for a value of "created_by" rather than the column entity. How can I change that?

Change ['user_id', '=', 'created_by'] to ['user_id', '=', '$created_by']

 

[asprin]

Why are you adding your xf_fc_events entity to the User entity structure?

Yeh, realized that I don't need to do that. Have removed the Listener.php code

Change ['user_id', '=', 'created_by'] to ['user_id', '=', '$created_by']

Thanks, this worked.

$structure->relations = [ 'User' => [ 'entity' => 'XF:User', 'type' => self::TO_ONE, 'conditions' => 'user_id', 'primary' => true ] ];

But using this, how would it know that xf_user.user_id should be joined on xf_fc_events.created_by? I do not see any mention of created_by in the above.

 

[Lawrence]

The above was just an example, and as I always user 'user_id' for the user that created the data in my add-ons tables I didn't give it another thought. Use this for the conditions: [['user_id', '=', '$created_by']]

 

[asprin]

[['user_id', '=', '$created_by']]

Just one last bit - using the above is there a way to add OR condition between multiple conditions? For example:

[['user_id', '=', '$created_by'], ['user_id', '=' , '$updated_by']]

generates

ON (xf_user.user_id = xf_fc_events.created_by AND xf_user.user_id = xf_fc_events.updated_by)

// how can the above be changed to read as
ON (xf_user.user_id = xf_fc_events.created_by OR xf_user.user_id = xf_fc_events.updated_by)

 

[Lawrence]

Finders do support whereOr, in this case I would add another relation:

'UpdatedUser' => [
                'entity' => 'XF:User',
                'type' => self::TO_ONE,
                'conditions' => [['user_id', '=', '$updated_by']],
                'primary' => true
            ],

Then when you use your finder, you can add, if appropriate, :->with('UpdatedUser')

An example of whereOr:

        $visitor = \XF::visitor();

        $conditions = [];
        $viewableStates = ['visible'];

        if ($visitor->hasPermission('eaeHistory', 'viewDeleted'))
        {
            $viewableStates[] = 'deleted';

            $this->with('DeletionLog');
        }

        if ($visitor->hasPermission('eaeHistory', 'viewModerated'))
        {
            $viewableStates[] = 'moderated';
        }

        $conditions[] = ['event_state', $viewableStates];
        $this->whereOr($conditions);