I need a find that returns based on the existence of multiple rows in a joined table

Need Help
1

Hy Guys!

Let`s say we have an Articles table and a Tags table. Articles are Many to Many with Tags with a ArticlesTags table.

How can i come up with a find that gets me All the Articles that have many of the same tags.
(Ex. I want all the articles that have both the tag with name “Cake” and the tag “PHP”).

I am trying with matching but i can only get ALL the Articles connected with either “Cake” or “PHP”.

  • if i add an “AND” or chain the matching for both tags i get an SQL like …tag_name=“Cake” and tag_name=“PHP”… wich returns 0 rows :).

Thanks!

2

There is no tag record that will have both Cake and PHP as the name. You need to use OR to chain those, so it finds tags named Cake or PHP`, and then looks for those with the match.

3

Of course. There there has to be 2 rows (one for “Cake” and one for “PHP”) in the join table for each article that is valid.

The logic is somehting “get all articles where exists(cake tag) and exists(php tag)” but i don`t see how can i do it within CakePHP (if it can be done). I will probably work it out with some custom SQL.

4

You have to use multiple Joins with the same table.

SELECT
    Articles.*
FROM articles Articles
INNER JOIN articles_tags ArticlesTagsCake ON
    ArticlesTagsCake.article_id = Articles.id
    AND ArticlesTagsCake.tag_id = 1 -- "Cake" tag id
INNER JOIN articles_tags ArticlesTagsPhp ON
    ArticlesTagsPhp.article_id = Articles.id
    AND ArticlesTagsPhp.tag_id = 2 -- "Php" tag id

You can create associations on the fly and matching with that association.

$articles = $this->Articles->find('all');
foreach ($tags as $tag) {
    // best to copy other options like foreign key name used in ArticlesTable::initialize()
    $this->Articles->hasOne('tag' . $tag->id, ['classname' => 'ArticleTags']);
    $articles->innerJoinWith('tag' . $tag->id, function (\Cake\ORM\Query $query) use ($tag) {
        return $query->where([
            'tag' . $tag->id . '.id' => $tag->id,
        ]);
    });
}
1 Like
5

Nice, it was too of a late hour to think about multiple join for the same table. This is the answer!

For now i am doing the “filtering” within a PHP foreach but i will try it when i come back to revise. Probably it will be faster than the PHP loop patched solution.

Thanks!

7

I reworked the code with your ideea. This is the final product. (My project does not involve articles/tags but a similar connection)

        $articles = $this->Articles->find();
        $tagstofind = [10,20]; // the id of the tags to search
        foreach ($tagstofind as $tag) {
            $articles->join([
                'table' => 'articles_tags',
                'alias' => 'joinedTagTable'.$tag,
                'type' => 'LEFT',
                'conditions' => [
                    'joinedTagTable'.$tag.'.article_id = Articles.id',
                ]
            ])->where([
                'joinedTagTable'.$tag.'.tag_id' => $tag,
            ]);
        }

Thanks again!

1 Like