[Solved] Filter Results based on the parent relation

Hi,

I'm trying to filter my results based on a field value.

Some informations

Laravel 5.3
postgres 9.6
Laravel Scout implemented

Current situation

In the current implementation, i have a local scope which filters the status field.

This works, but i have the problem, that related/child posts are shown even when the parent record is not active.

Code code code

Short example

| id | serie_id | type     | status    |
|----|----------|----------|-----------|
| 1  | null     | tutorial | published | 
| 2  | null     | series   | published | 
| 3  | null     | series   | draft     | 
| 4  | 2        | episode  | published |
| 5  | 2        | episode  | draft     |
| 6  | 3        | episode  | published |
| 7  | 3        | episode  | draft     |

Current result: 4
Expected result: 3

What have i tried already

My first idea was to keep it as it is in the model. I have used the "reject()" method, but then I can't use the "paginate()" method. ( snippet: http://laravel.io/bin/mGxXV )

I tried also to handle the filtering via the scope, but this doesn't work (maybe it was to late - i don't know :D ) ( snippet: http://laravel.io/bin/Gyw9r )

Has anyone a idea how to solve this?

Thanks a lot!

Greets, Marcus

noxify

9 years ago

Best Answer

Level 1

Solved :)

Here is my current scope:

public function scopeIsPublished($query) {
        return $query
            ->where('status', 'published')
            ->whereHas('serie', function($q) {
                $q->where('status', 'published');
            })
            ->orWhere('status', 'published')
            ->whereNull('serie_id');
}

The sql looks like:

select * from "lessons" where ("status" = 'published' and exists (select * from "lessons" as "laravel_reserved_1" where "laravel_reserved_1"."id" = "lessons"."serie_id" and "status" = 'published' and "laravel_reserved_1"."deleted_at" is null) or "status" = 'published' and "serie_id" is null) and "lessons"."deleted_at" is null

2 likes

Please or to participate in this conversation.