Filter relations in laravel

user_6144857 0
Viewed 675

I have Posts and Comments models with hasMany relation:

public function comments()
{
    return $this->hasMany(Posts::class, 'posts_id', 'id');
}

In my controller I need to get all published posts (is_published = 1), with all published comments, that have at lease 1 published comment:

$dbRecords = Posts::all()->whereStrict('is_published', 1);
$posts = [];
foreach ($dbRecords as $post) {
    if (count($post->comments()) === 0) {
        continue;
    }

    foreach ($post->comments() as $comment) {
        if ($comment->is_published === 1) {
            $posts[] = $post;

            continue(2); // to the next post
        }
    }
}

But, such solution is ugly. Also I will get all published post, wit published and not published comments, so I will forced to filter comments once again in Resource.

Another solution I've found - to use raw query:

$dbRecords = DB::select("SELECT posts.* 
    FROM posts
    JOIN comments ON posts_id = posts.id
    WHERE posts.is_published = 1
      AND comments.is_published = 1
    HAVING count(posts.id) > 0;");
$users = array_map(function($row) { return (new Posts)->forceFill($row); }, $dbRecords);

But it does not solves the problem with the need of filteration of unpublished comments in Resource.

laravel php
2 Answers

In your case it would be like this:

// Retrieve all posts that have at least one comment
$posts = Post::has('comments')->with('comments')->get();

// Retrieve posts with at least one comment and which are published
$callback = function($query) {
    $query->where('is_published ', '=', '1');
}

$posts = Post::whereHas('comments', $callback)
    ->with(['comments' => $callback])
    ->where('is_published ', '=', '1')
    ->get();
user_116410290

What about this in eloquent

$posts = Post::query()->where('is_published', 1)->with(['comments' => function ($query) {
        $query->where('is_published', 1);
}])->get();
user_101105280
Related