'Through' association with 'foreign_key' & 'model' fields in CakePHP 3

Question

This is a pretty standard thing that I've done probably 600 times in CakePHP 2, but for the life of me, I can't get it to work in CakePHP 3.

I have Videos, Photos, Articles. I also have Categories.

Videos, Photos, and Articles can all belong to one or more Categories.

The goal of the current problem is to pull videos that are under a certain category.

So, I tried this:

// VideosTable
$this->belongsToMany('Categories', [
    'joinTable' => 'categorized',
    'className' => 'Categorized',
    'foreignKey' => 'foreign_key',
    'conditions' => [
        'Categorized.model' => 'Videos',
    ]
]);

public function getTopVideosByCategory($categorySlug)
{
    return $this->Categories->find('all')
        ->where(['Categories.slug' => $categorySlug])
        ->contain([
            'Videos' => function ($q) {
                return $q
                    ->limit(8)
                    ->contain([
                        'Tags',
                        'Categories' // tried with and without this
                    ])
                    ->order([
                        'Videos.featured' => 'DESC',
                        'Videos.created' => 'DESC'
                    ]);
            }
        ])
        ->first();
}

Error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'Categorized.model' in 'where clause'

I've tried a number of other ways including creating the join table's model, and a few others, but keep getting errors. I've tried with every option, and with limited number of options. I've tried using an actual Table class, and I've tried a pseudo one (like "Categorized" above).

I have to assume this is pretty standard, but can't find an example in the book, and I just can't seem to get it to work.

---

Edit:

I've also tried this:

//VideosTable
public function initialize(array $config)
{
    $this->belongsToMany('Categories', [
        'through' => 'Categorized',
        'conditions' => [
            'Categorized.model' => $this->alias(),
        ]
    ]);
}

public function getTopVideosByCategory($categorySlug)
{
    return $this->find('all')
        ->matching('Categories', function ($q) use ($categorySlug) {
            return $q
                ->where(['Categories.slug' => $categorySlug]);
        })
        ->contain([
            'Tags',
            'Categories'
        ])
        ->limit(8)
        ->order([
            'Videos.featured' => 'DESC',
            'Videos.created' => 'DESC'
            ])
        ->first();
}

But get this error:

Error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'Categorized.model' in 'on clause'

Answer 1

Since Videos and Categories is not a 1-1 o n-1 (hasOne or belongsTo), it is impossible to build a SQL expression that can include conditions for the other table. For those cases, CakePHP implements the matching() function. It works similar to contain() but what it does is using an INNER join to get the data from the external associations:

http://book.cakephp.org/3.0/en/orm/retrieving-data-and-resultsets.html#filtering-by-associated-data

You can also look an an example of using it here:

http://book.cakephp.org/3.0/en/tutorials-and-examples/bookmarks/intro.html#creating-the-finder-method

Answer 2

I ended up getting it to work like this:

class VideosTable extends Table
{
    public function initialize(array $config)
    {
        $this->hasMany('Categorized', [
            'foreignKey' => 'foreign_key',
            'conditions' => [
                'Categorized.model' => $this->alias(),
            ]
        ]);
    }

    public function getTopVideosByCategory($categorySlug)
    {
        return $this->find()
            ->matching(
                'Categorized.Categories', function ($q) use ($categorySlug) {
                    return $q
                        ->where(['Categories.slug' => $categorySlug]);
            })
            ->limit(8)
            ->order([
                'Videos.featured' => 'DESC',
                'Videos.created' => 'DESC'
                ])
            ->all();
    }

---

I've up-voted José's answer, as it led me down the road of figuring it out, but will mark this as the answer, as I think it more-quickly helps users trying to figure this particular problem out.

José, if you want to append this (with any tweaks you see fit) to your answer, I'll change the marked answer to yours.

Answer 3

It seem that what you want is:

$this->belongsToMany('Categories', [
    'through' => 'Categorized',
    'conditions' => ['Categorized.model' => $this->alias()]
]);