How to use order with a relational condition?

I'm trying to achieve this:

Get the Post with the least amount of visible Comments but base the order on visible Comments.

some examples:

if post has 0 comment, then it should be first
if post has 20 comments but none of them are visible, then it should be second
if post has 1 comment and the comment is visible, then it should be third
if post has 5 comments and 2 comments are visible, then it should be fourth

my try:

$post = Post::withCount('comments')
             ->whereRelation('comments', 'enabled', TRUE) // this line ruins it and excludes exactly the post im looking for
             ->orderBy('comments_count', 'asc')
             ->first();

madyz

3 years ago

Level 1

I guess I just needed to say it out loud. Got what I was looking for:

      $post = Post::withCount([
        'comments as enabled_comments_count'  => function ($query) {
          $query->where('enabled', TRUE);
        },
        "comments"
      ])->orderBy('enabled_comments_count')->orderBy('comments_count')->get();

Please or to participate in this conversation.