Query builder "strict" whereIn, or why multiple where not working.

Hello, i can't buld a query, which make "strict" whereIn request.

I have polymorphic manyToMany scheme, with categories , documents and categoryables tables.

Now i have some correct data in categoriables table:

id category_id categoryable_type categoryable_id
26 4 "App\Models\Document" 7
27 5 "App\Models\Document" 7
28 4 "App\Models\Document" 8
29 5 "App\Models\Document" 8
30 10 "App\Models\Document" 8

When i make request:

Document::whereRelation( 'categories', fn ( Builder $builder ) => $builder
    ->whereIn( 'category_id', [10,5] ) ) )->get();

and I get documents with ids: 7 and 8.

When i make request:

Document::whereRelation( 'categories', fn ( Builder $builder ) => $builder
    ->where( 'category_id', '=', 5 )->where( 'category_id', '=', 10 ) ) )->get();

I get nothing...

I want to get documents, which have both - category_id 5 AND 10 ...

What can i do?

rodrigo.pedra

3 years ago

Best Answer

Level 56

Document::query()
    ->whereRelation('categories', fn ($builder) => $builder->where( 'category_id', 5))
    ->whereRelation('categories', fn ($builder) => $builder->where( 'category_id', 10))
    ->get();

In a single query, all where clauses need to be matched by each record. Therefore, you won't have a record with two different values for a single column. That is why using two different values to test the same columns doesn't work.

The query above will test each individual value separately, and as they are both where clauses to the same parent query, only records matching both separate where clauses will be fetched.

1 like

rodrigo.pedra

3 years ago

Level 56

Your ->where()->where() attempt read like this:

Fetch any records from the documents table, where there is at least one related record in the categories table with category_id equals to 5 and categories equals to 10

There won't be any records on the category table that matches both values for the same category_id column.

My proposed solution reads like this:

Fetch any records from the documents table, where there is at least one related record in the categories table with category_id equals to 5 and also, there is at least one related record in the categories table with category_id equals to 10

Fomaxwebcreator

3 years ago

Level 1

@rodrigo.pedra Thanks a lot for the solution and detailed explanation! And find one more solution through whereHas :

Document::whereHas(  'categories',  function ( $query ) {
            $query->whereIn( 'resource_category_id', [ 10, 5 ] );
 },  '> ', 1 )->get();

1 like

rodrigo.pedra

3 years ago

Level 56

@Fomaxwebcreator that is even better, in the sense the query optimizer will perform less sub-queries.

But in some sense the same logic stands, as it uses an aggregate to calculate how many records match the criteria, thus it can find more than a single row.

Great you find it out, I often forget about this feature. And thanks for letting me know.

Have a nice day =)

robgordijn

2 years ago

Level 5

A late reply, but the above can be solved by creating a query with

a join on the pivot table with the wanted tags
a grouped and sorted string-representation of the connected tags (to the Post)
and filter with a having on wanted (and sorted) tags

This removes the need to additional filtering afterwards, no need to have multiple whereRelation() calls.

        return Post::query()
            ->select('post.*')

            ->selectRaw('group_concat(distinct post_tag.tag_id order by post_tag.tag_id separator "'.$separator.'") as tags')

            ->join('post_tag', function ($join) use ($tagIds) {
                $join->on('post_tag.thing_id', '=', 'post.id')
                    ->whereIn('post_tag.tag_id', $tagIds);
            })
            ->having('tags', '=', $tagIds->implode($separator))
            ->groupBy('post.id')
            ->get();

Please or to participate in this conversation.