XF 2.1 Problem with 'on clause'

[Cupara]

I'm sure it is something simple or I'm missing something easy but here goes.

I have this error:

XF\Db\Exception: MySQL statement prepare error [1054]: Unknown column 'bookmakers_Bookmakers_1.sponsored_by' in 'on clause' in src/XF/Db/AbstractStatement.php at line 217
 
[LIST=1]
[*]    
[*]XF\Db\AbstractStatement->getException() in src/XF/Db/Mysqli/Statement.php at line 196
[*]   
[*]XF\Db\Mysqli\Statement->getException() in src/XF/Db/Mysqli/Statement.php at line 39
[*]   
[*]XF\Db\Mysqli\Statement->prepare() in src/XF/Db/Mysqli/Statement.php at line 54
[*]   
[*]XF\Db\Mysqli\Statement->execute() in src/XF/Db/AbstractAdapter.php at line 94
[*]   
[*]XF\Db\AbstractAdapter->query() in src/XF/Mvc/Entity/Finder.php at line 1294
[*]   
[*]XF\Mvc\Entity\Finder->fetch() in src/addons/BetClever/Tipsters/Pub/Controller/Competitions.php at line 26
[*]   
[*]BetClever\Tipsters\Pub\Controller\Competitions->actionIndex() in src/XF/Mvc/Dispatcher.php at line 321
[*]   
[*]XF\Mvc\Dispatcher->dispatchClass() in src/XF/Mvc/Dispatcher.php at line 248
[*]   
[*]XF\Mvc\Dispatcher->dispatchFromMatch() in src/XF/Mvc/Dispatcher.php at line 100
[*]   
[*]XF\Mvc\Dispatcher->dispatchLoop() in src/XF/Mvc/Dispatcher.php at line 50
[*]   
[*]XF\Mvc\Dispatcher->run() in src/XF/App.php at line 2177
[*]   
[*]XF\App->run() in src/XF.php at line 390
[*]   
[*]XF::runApp() in index.php at line 20
[/LIST]

This is my Bookmakers Entity and my Competitions Entity:
Bookmakers:

<?php
namespace BetClever\Tipsters\Entity;

use XF\Mvc\Entity\Entity;
use XF\Mvc\Entity\Structure;

class Bookmakers extends Entity
{
    public static function getStructure(Structure $structure)
    {
        $structure->table = 'bookmakers';
        $structure->shortName = 'BetClever\Tipsters:Bookmakers';
        $structure->contentType = 'bc_bookmakers';
        $structure->primaryKey = 'bookmaker_id';
        $structure->columns = [
            'bookmaker_id'         => ['type' => self::UINT, 'maxLength' => 11, 'autoIncrement' => true],
            'name'                => ['type' => self::STR, 'maxLength' => 30, 'required' => true],
            'logo'                => ['type' => self::STR, 'maxLength' => 30, 'nullable' => true],
            'url'                => ['type' => self::STR, 'maxLength' => 255, 'required' => true],
            'review'            => ['type' => self::STR],
            'bonus_offer'        => ['type' => self::STR, 'maxLength' => 255, 'nullable' => true],
            'bonus_code'        => ['type' => self::STR, 'maxLength' => 255, 'nullable' => true],
            'banner'            => ['type' => self::STR, 'maxLength' => 255, 'nullable' => true],
            'banner_url'        => ['type' => self::STR, 'nullable' => true, 'required' => true],
            'banner_type'        => ['type' => self::UINT, 'maxLength' => 2, 'nullable' => true],
            'ip_address'        => ['type' => self::STR, 'maxLength' => 50],
            'country'            => ['type' => self::STR, 'maxLength' => 10]
        ];
        $structure->behaviors = [

        ];
        $structure->getters = [

        ];
        $structure->relations = [
            'BookmakerReviews' => [
                'entity' => 'BetClever\Tipsters:BookmakerReviews',
                'type' => self::TO_ONE,
                'conditions' => 'bookmaker_id',
                'primary' => true
            ],
            'Competitions' => [
                'entity' => 'BetClever\Tipsters:Competitions',
                'type' => self::TO_ONE,
                'conditions' => ['bookmaker_id', '=', 'sponsored_by'],
                'primary' => true
            ]
        ];
        $structure->options = [
            
        ];

        return $structure;
    }

    protected function _postDelete()
    {
        $bookmakersRepo = \XF::repository('BetClever\Tipsters:Bookmakers');
        $finder = \XF::em()->find('BetClever\Tipsters:Bookmakers', $this->bookmaker_id);

        if ($finder)
        {
            $finder->delete($this->id);
        }
    }

}

Competitions:

<?php
namespace BetClever\Tipsters\Entity;

use XF\Mvc\Entity\Entity;
use XF\Mvc\Entity\Structure;

class Competitions extends Entity
{
    public static function getStructure(Structure $structure)
    {
        $structure->table = 'competitions';
        $structure->shortName = 'BetClever\Tipsters:Competitions';
        $structure->contentType = 'bc_competitions';
        $structure->primaryKey = 'id';
        $structure->columns = [
            'id'                 => ['type' => self::UINT, 'maxLength' => 11, 'autoIncrement' => true],
            'name'                => ['type' => self::STR, 'maxLength' => 255],
            'sport_id'            => ['type' => self::UINT, 'maxLength' => 11, 'nullable' => true],
            'league_id'            => ['type' => self::UINT, 'maxLength' => 11, 'nullable' => true],
            'description'        => ['type' => self::STR],
            'start_date'        => ['type' => self::STR],
            'end_date'            => ['type' => self::STR],
            'type'                => ['type' => self::UINT, 'maxLength' => 1],
            'price_pool'        => ['type' => self::UINT, 'maxLength' => 5],
            'rewards'            => ['type' => self::UINT, 'maxLength' => 20],
            'currency'            => ['type' => self::STR, 'maxLength' => 3],
            'sponsored_by'        => ['type' => self::UINT, 'maxLength' => 3],
            'min_tips'            => ['type' => self::UINT, 'maxLength' => 11],
            'winner'            => ['type' => self::UINT, 'maxLength' => 11, 'nullable' => true],
            'active'            => ['type' => self::UINT, 'maxLength' => 1]
        ];
        $structure->behaviors = [

        ];
        $structure->getters = [

        ];
        $structure->relations = [
            'Sports' => [
                'entity' => 'BetClever\Tipsters:Sports',
                'type' => self::TO_ONE,
                'conditions' => 'id',
                'primary' => true
            ],
            'Leagues' => [
                'entity' => 'BetClever\Tipsters:Leagues',
                'type' => self::TO_ONE,
                'conditions' => 'id',
                'primary' => true
            ],
            'User' => [
                'entity' => 'XF:User',
                'type' => self::TO_ONE,
                'conditions' => 'user_id',
                'primary' => true
            ],
            'Rewards' => [
                'entity' => 'BetClever\Tipsters:Rewards',
                'type' => self::TO_MANY,
                'conditions' => 'competition_id'
            ],
            'Bookmakers' => [
                'entity' => 'BetClever\Tipsters:Bookmakers',
                'type' => self::TO_ONE,
                'conditions' => ['sponsored_by', '=', 'bookmaker_id'],
                'primary' => true
            ]
        ];
        $structure->options = [
            
        ];

        return $structure;
    }

    protected function _postDelete()
    {
        $competitionsRepo = \XF::repository('BetClever\Tipsters:Competitions');
        $finder = \XF::em()->find('BetClever\Tipsters:Competitions', $this->id);

        if ($finder)
        {
            $finder->delete($this->id);
        }
    }

}

Here is my public controller for Competitions:

<?php
namespace BetClever\Tipsters\Pub\Controller;

class Competitions extends \XF\Pub\Controller\AbstractController
{
    public function actionIndex()
    {
        $options = \XF::options();
        $db = \XF::db();
        $visitor = \XF::visitor();
        $app = \XF::app();
        $finder = \XF::finder('BetClever\Tipsters:Competitions');
        
        $activeComps = $finder->where('active', 1)->with('Bookmakers')->fetch();
        $inactiveComps = $finder->where('active', 0)->with('Bookmakers')->fetch();
        
        $viewParams = [
            'activeComps' => $activeComps,
            'inactiveComps' => $inactiveComps
        ];

        return $this->view('BetClever\Tipsters:View', 'bc_competitions_view', $viewParams);
        
    }
}

Thanks to anyone that can help with this.

 

[Jeremy P]

It looks like your conditions for the relations between the entities should be flipped. Further, the right-hand side should be prefixed with a variable sign to indicate the usage of an entity value:

'Bookmakers' => [
    'entity'     => 'BetClever\Tipsters:Bookmakers',
    'type'       => self::TO_ONE,
    'conditions' => ['bookmaker_id', '=', '$sponsored_by'],
    'primary'    => true
]

 

[Cupara]

Thanks @Jeremy P appreciate the input.

 

[Cupara]

This was a fix that required renaming columns in database tables to make them more unique. Once I did that, the error went away.