filter query join is not working for me
so, I am trying to make a filter with join on another table. The query is working fine but for some reason it's not working in filament. Do you have any idea why and if this is even possible to do, I really need this functionality on my page:
Tables\Filters\Filter::make('least_money_made')->query(function (Builder $query) {
return $query->select('funnels.*')
->leftJoin('orders', 'funnels.id', '=', 'orders.funnel_id')
->selectRaw('COALESCE(SUM(orders.total_price), 0) AS total_money_made')
->groupBy('funnels.id')
->orderBy('total_money_made', 'ASC')
->limit(1);
});3 Replies
⬆️
i've also tried with relationship, and i didn't manage to get it to work:
Tables\Filters\Filter::make('least_money_made')->query(function (Builder $query) {
return $query->withCount('orderRelation')
->with(['orderRelation' => function ($query) {
$query->selectRaw('funnel_id, SUM(total_price) as total_money_made')
->groupBy('funnel_id');
}])
->get()
->sortBy(function ($funnel) {
return $funnel->orderRelation_count > 0 ? $funnel->orderRelation->sum('total_money_made') : PHP_INT_MAX;
})
->first();
}),Check the resulting query with somethng like Debugbar. I guess the issue is that Filters are wrapped in
->where()s. But there should be a baseQuery or something similar method which maybe works for applying the left join.I will try to do that, the query checks out, i get the result
I've also tried with the baseQuery method and it doesn't work
I couldn't make this one work, so we moved it into a widget because we need this info from anywhere