Using withCount() within filters not working.

[mrl22]

Hello

I was hoping that someone could point me in the right direction.

I am trying to create a filter based on a relationship count on version 3, but I am getting an error as it can not find the count field.

Here are snippets of my code:

    public function filters(): array
    {
        $filters = [];
        
        $filters[] = SelectFilter::make('Status', 'type')
            ->options([
                '' => 'All',
                'none_dispatched' => 'None Dispatched',
                'part_dispatched' => 'Part Dispatched',
                'dispatched' => 'Dispatched',
                'delivered' => 'Delivered',
            ])
            ->filter(function (Builder $builder, string $value) {

                if ($value === 'none_dispatched')
                    $builder->where('crate_items_count', 0);

            });

        return $filters;
    }

    public function builder(): Builder
    {
        return ScheduleItem::query()
            ->withCount([
                'crate_items',
            ]);
    }

The error I get is:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'crate_items_count' in 'where clause'

SELECT
  count(*) AS aggregate
FROM
  `schedule_items`
WHERE
  `crate_items_count` = 0

It ignores the withCount() and runs the query as an aggregate.

I really appreciate any help you can provide.

[lrljoe]

From memory this can be done in the filter using having() rather than where:

$query->having('crate_items_count', '>', 2)

Would need to double check though as I've been away for a bit!

[mrl22]

Thank you for this.

I have also tried having(), but I get duplicate column errors. There are roughly 5 column names that are in both tables/models.

[lrljoe]

If you're referencing an actual field using where, then prefix with the table name to ensure uniqueness if it exists in multiple relations.
I use this approach to ensure the correct "name" or "updated_at" is used (as these exist in almost all of my models)

having() runs with the joins, so knows about relationship counts/sums etc

[mrl22]

Thank you for getting back to me. I will play with this more when I return to this project. I am jumping around 2-3 Laravel projects at the moment.

[lrljoe]

Will close the discussion for now, if the above solution doesn't work, then just re-open it :)