Intersection of query and subquery

Need Help
1

Hi guys, down below are my models and my query.
I want all games from a specific user. How can I do this in cakephp with this model setup?

$conn = ConnectionManager::get('default'); $games = $conn->execute('SELECT DISTINCT games.* FROM games INNER JOIN games_tags on games.id = games_tags.game_id WHERE games.is_active AND games_tags.tag_id IN (SELECT users_tags.tag_id FROM users_tags WHERE users_tags.user_id = '.$this->Auth->user('id').');') ->fetchAll('assoc');

public function initialize(array $config)
    {
        $this->table('users');
        $this->displayField('email');
        $this->primaryKey('id');
        $this->addBehavior('Timestamp');
        $this->belongsToMany('Tags', [
            'foreignKey' => 'user_id',
            'targetForeignKey' => 'tag_id',
            'through' => 'UsersTags'
        ]);
    }
public function initialize(array $config)
{
    parent::initialize($config);

    $this->table('users_tags');
    $this->displayField('user_id');
    $this->primaryKey(['user_id', 'tag_id']);

    $this->belongsTo('Users', [
        'foreignKey' => 'user_id',
        'joinType' => 'INNER'
    ]);
    $this->belongsTo('Tags', [
        'foreignKey' => 'tag_id',
        'joinType' => 'INNER'
    ]);
}
public function initialize(array $config)
{
    parent::initialize($config);

    $this->table('tags');
    $this->displayField('title');
    $this->primaryKey('id');

    $this->addBehavior('Timestamp');

    $this->belongsToMany('Users', [
        'foreignKey' => 'tag_id',
        'targetForeignKey' => 'user_id',
        'joinTable' => 'users_tags'
    ]);

    $this->belongsToMany('Games', [
        'foreignKey' => 'tag_id',
        'targetForeignKey' => 'game_id',
        'joinTable' => 'games_tags'
    ]);
}
public function initialize(array $config)
{
    $this->addBehavior('Timestamp');
    
    $this->belongsToMany('Tags', [
        'foreignKey' => 'game_id',
        'targetForeignKey' => 'tag_id',
        'through' => 'GamesTags',
    ]);


    $this->addBehavior('Proffer.Proffer', [
        'title_image' => [    // The name of your upload field
            'root' => WWW_ROOT . 'files', // Customise the root upload folder here, or omit to use the default
            'dir' => 'title_image_dir',   // The name of the field to store the folder
            'thumbnailSizes' => [ // Declare your thumbnails
                'square' => ['w' => 373, 'h' => 210],               
                'middle' => ['w' => 460, 'h' => 215]
            ],
            'thumbnailMethod' => 'Gd'  
        ],
    ]);       
}
public function initialize(array $config)
{
    parent::initialize($config);

    $this->table('games_tags');
    $this->displayField('game_id');
    $this->primaryKey(['game_id', 'tag_id']);

    $this->belongsTo('Games', [
        'foreignKey' => 'game_id',
        'joinType' => 'INNER'
    ]);
    $this->belongsTo('Tags', [
        'foreignKey' => 'tag_id',
        'joinType' => 'INNER'
    ]);
}
2

See last answer here http://laravel.io/forum/05-12-2015-has-many-through-relationship-depth
I’d use querybuilder

3

Hi, thank you!

here is my solution.

$users_tags = TableRegistry::get('UsersTags');
$users_tags = $users_tags->find()->select(['tag_id'])->where(['user_id' => $this->Auth->user('id')]);

$games = $this->Games->find()->leftJoin(
                ['GamesTags' => 'games_tags'],
                ['Games.id = GamesTags.game_id'])
                ->where(['is_active'])->andWhere(['GamesTags.tag_id IN' => $users_tags])->distinct();

$this->set('games', $games);