Remove unmatched criteria records from Eloquent collection

Question

I have some problems with Eloquent Eager Loading. I have added whereHas to remove the blog don't meet the comment criteria but the comment stills return empty array. My intention is to completely remove it from the json record.

How can I completely remove the json data chain that does not meet my condition?

My current code:

User::select("id", "name", "email")
    ->with(['blog', 'blog.author', 'blog.comments' => function ($query) {
        $query->where('comment', 'John is here');
    }, 'blog.comments.owner'])
    ->whereHas('blog.comments', function ($query) {
        $query->where('comment', 'John is Here');
    })
    ->get();

My current json output is:

{
    "id": 1,
    "name": "John Smith",
    "email": "john.smith@hotmail.com",
    "blog": [
        {
            "id": 1,
            "created_at": "2021-04-09T18:08:06.000000Z",
            "updated_at": "2021-04-09T10:33:03.000000Z",
            "title": "First Blog",
            "description": "Awesome",
            "users_id": 1,
            "cover": null,
            "author": {
                "id": 1,
                "name": "John Smith",
                "email": "john.smith@hotmail.com",
                "email_verified_at": null,
                "created_at": "2021-04-08T13:29:13.000000Z",
                "updated_at": "2021-04-08T13:29:13.000000Z",
                "role": 0
            },
            "comments": [
                {
                    "id": 1,
                    "comment": "John is here",
                    "blog_id": 1,
                    "user_id": 1,
                    "created_at": null,
                    "updated_at": null,
                    "owner": {
                        "id": 1,
                        "name": "John Smith",
                        "email": "john.smith@hotmail.com",
                        "email_verified_at": null,
                        "created_at": "2021-04-08T13:29:13.000000Z",
                        "updated_at": "2021-04-08T13:29:13.000000Z",
                        "role": 0
                    }
                }
            ]
        },
        {
            "id": 6,
            "created_at": "2021-04-12T07:41:43.000000Z",
            "updated_at": "2021-04-12T08:01:18.000000Z",
            "title": "Second Blog",
            "description": "Awesome",
            "users_id": 1,
            "cover": "images/json_1618213303.png",
            "author": {
                "id": 1,
                "name": "John Smith",
                "email": "john.smith@hotmail.com",
                "email_verified_at": null,
                "created_at": "2021-04-08T13:29:13.000000Z",
                "updated_at": "2021-04-08T13:29:13.000000Z",
                "role": 0
            },
            "comments": []
        }
    ]
}

My expected output would be:

{
    "id": 1,
    "name": "John Smith",
    "email": "john.smith@hotmail.com",
    "blog": [
        {
            "id": 1,
            "created_at": "2021-04-09T18:08:06.000000Z",
            "updated_at": "2021-04-09T10:33:03.000000Z",
            "title": "First Blog",
            "description": "Awesome",
            "users_id": 1,
            "cover": null,
            "author": {
                "id": 1,
                "name": "John Smith",
                "email": "john.smith@hotmail.com",
                "email_verified_at": null,
                "created_at": "2021-04-08T13:29:13.000000Z",
                "updated_at": "2021-04-08T13:29:13.000000Z",
                "role": 0
            },
            "comments": [
                {
                    "id": 1,
                    "comment": "John is here",
                    "blog_id": 1,
                    "user_id": 1,
                    "created_at": null,
                    "updated_at": null,
                    "owner": {
                        "id": 1,
                        "name": "John Smith",
                        "email": "john.smith@hotmail.com",
                        "email_verified_at": null,
                        "created_at": "2021-04-08T13:29:13.000000Z",
                        "updated_at": "2021-04-08T13:29:13.000000Z",
                        "role": 0
                    }
                }
            ]
        }
    ]
}

Could you replace `->get()` with `->toSql()` and share the SQL that is running ? — matiaslauriti, Apr 21 '21 at 07:41
select `id`, `name`, `email` from `users` where exists (select * from `blogs` where `users`.`id` = `blogs`.`users_id` and exists (select * from `comments` where `blogs`.`id` = `comments`.`blog_id` and `comment` = ?)) — Miracle Hades, Apr 21 '21 at 07:49
This is a little bit out of my league, I mean, it is too much for me to help you, sorry — matiaslauriti, Apr 21 '21 at 07:51

score 1 · Accepted Answer · answered Apr 21 '21 at 10:16

1

Try this,

User::select("id", "name", "email")
    ->with([
        'blog' => function ($query) {
            $query->whereHas('comments', function ($query) {
                $query->where('comment', 'John is Here');
            });
        }, 
        'blog.comments' => function ($query) {
            $query->where('comment', 'John is here');
        },
        'blog.author',  
        'blog.comments.owner'])
    ->get();

You should be applying the constraint on blog eager loading as well. Your query only filters comments of a blog

answered Apr 21 '21 at 10:16

wpsiew

191
1
4

I have a question for the eloquent model, how can I construct a join query incorporate with the eloquent model but the output must return me as collection. I have attempted to use something like "User::join("user_role","id","=","user.role_id")" but this can lead another problem when someone decide to change table name, it can directly crash entire code. – Miracle Hades Apr 22 '21 at 06:31
I would suggest accessing the related record like so `$user->roles`, but you first have to define the many-to-many `roles` relationship between `User` model and `Role`. – wpsiew Apr 22 '21 at 11:53

Remove unmatched criteria records from Eloquent collection

1 Answers1