How to add having clause to withCount in laravel?-CodePudding

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?

CodePudding user response：

...
->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