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

**URL:** <https://discourse.cakephp.org/t/i-need-a-find-that-returns-based-on-the-existence-of-multiple-rows-in-a-joined-table/7968>\
**Category:** Need Help\
**Created:** [June 21, 2020, 4:39pm UTC](https://discourse.cakephp.org/t/i-need-a-find-that-returns-based-on-the-existence-of-multiple-rows-in-a-joined-table/7968 "2020-06-21T16:39:21Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![lucian](https://avatars.discourse-cdn.com/v4/letter/l/eb9ed0/32.png) [@lucian](https://discourse.cakephp.org/u/lucian)\
**Post date:** [June 21, 2020, 4:39pm UTC](https://discourse.cakephp.org/t/i-need-a-find-that-returns-based-on-the-existence-of-multiple-rows-in-a-joined-table/7968/1 "2020-06-21T16:39:21Z")

</div>

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!

---

<div class="post-metadata">

**Author:** ![Zuluru](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.cakephp.org/zuluru/32/1230_2.png) [@Zuluru](https://discourse.cakephp.org/u/Zuluru)\
**Post date:** [June 21, 2020, 5:00pm UTC](https://discourse.cakephp.org/t/i-need-a-find-that-returns-based-on-the-existence-of-multiple-rows-in-a-joined-table/7968/2 "2020-06-21T17:00:19Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![lucian](https://avatars.discourse-cdn.com/v4/letter/l/eb9ed0/32.png) [@lucian](https://discourse.cakephp.org/u/lucian)\
**Post date:** [June 21, 2020, 5:11pm UTC](https://discourse.cakephp.org/t/i-need-a-find-that-returns-based-on-the-existence-of-multiple-rows-in-a-joined-table/7968/3 "2020-06-21T17:11:42Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![raul338](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.cakephp.org/raul338/32/69_2.png) [@raul338](https://discourse.cakephp.org/u/raul338)\
**Post date:** [June 22, 2020, 3:24pm UTC](https://discourse.cakephp.org/t/i-need-a-find-that-returns-based-on-the-existence-of-multiple-rows-in-a-joined-table/7968/4 "2020-06-22T15:24:43Z")

</div>

You have to use multiple Joins with the same table.

```sql
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.

```php
$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,
        ]);
    });
}

```

---

<div class="post-metadata">

**Author:** ![lucian](https://avatars.discourse-cdn.com/v4/letter/l/eb9ed0/32.png) [@lucian](https://discourse.cakephp.org/u/lucian)\
**Post date:** [June 22, 2020, 6:17pm UTC](https://discourse.cakephp.org/t/i-need-a-find-that-returns-based-on-the-existence-of-multiple-rows-in-a-joined-table/7968/5 "2020-06-22T18:17:05Z")

</div>

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!

---

<div class="post-metadata">

**Author:** ![lucian](https://avatars.discourse-cdn.com/v4/letter/l/eb9ed0/32.png) [@lucian](https://discourse.cakephp.org/u/lucian)\
**Post date:** [June 23, 2020, 5:25pm UTC](https://discourse.cakephp.org/t/i-need-a-find-that-returns-based-on-the-existence-of-multiple-rows-in-a-joined-table/7968/7 "2020-06-23T17:25:28Z")

</div>

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

```auto
        $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!