How to add having clause to withCount in laravel?

Question

I want to get posts with total amount of comments created before 2022-05-12 23:59:59. I also have attached timezone filter. I tried the following:

Route::get('/', function () { $base = Post::withCount([ 'comment' => function ($query) { $query->select( 'id', 'post_id', DB::raw('convert_tz(created_at, "UTC", "US/Eastern") as created_at_tz') )->having('created_at_tz', '<=', '2022-05-12 23:59:59')->count(); }, ]); return $base->get(); });

I get error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'posts.id' in 'where clause' select count(*) as aggregate from ( select convert_tz(created_at, "UTC", "US/Eastern") as created_at_tz from `comments` where `posts`.`id` = `comments`.`post_id` having `created_at_tz` <= 2022 -05 -12 23: 59: 59 ) as `temp_table`

What am I doing wrong?

You definitely don't need a select when you're using a withCount, since it's only going to retrieve the number of comments, not the comments themselves. — aynber
– aynber, Commented Jun 2, 2022 at 11:57

MAXIMAN · Accepted Answer · 2022-06-02 12:05:22Z

1

... ->withCount([ 'comment' => function ($query) { $query->whereRaw('convert_tz(created_at, "UTC", "US/Eastern") <= '2022-05-12 23:59:59')); }]) ...

try this and update me with response

I believe that count at the end is not necessary

edited Jun 2, 2022 at 12:05

answered Jun 2, 2022 at 11:58

MAXIMAN

1652 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Axel Over a year ago

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'as created_at_tz having

created_at_tz` <= ?) as comment_count from posts' at line 1`

Collectives™ on Stack Overflow

How to add having clause to withCount in laravel?

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related