Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

Sinres's avatar

How to refactor query eloquent collection

Hello!

I have query which is heavily expanded and i wonder how i can refactor them. Any ideas? :-)

$controls = Control::with(['shop', 'coordinator'])
            ->where(function ($query) {
                $query->whereDate('control_end_date', '>=', today()->subWeeks()->startOfWeek()->format('Y-m-d'));
                    $query->where(function (Builder $query) {
                        return $query->selectRaw('sum(gross_selling_price*quantity) AS sum_turnover')
                            ->from('documents')
                            ->where('doc_date', today())
                            ->whereColumn('shop_id', 'controls.shop_id')
                            ->whereIn('doc_type', [1, 4]);
                    }, '<=', DB::raw('turnover_during_control'));
            })
            ->orWhere(function ($query) {
                $query->whereDate('control_end_date', '<=', today()->subWeeks(1)->startOfWeek()->format('Y-m-d'));
                $query->where('turnover_during_control', '>=', 'turnover_after_control');
            })
            ->whereDate('created_at', '>=', today()->subWeeks()->startOfWeek())
            ->get();
0 likes
8 replies
lbecket's avatar

Does the query work and you're just looking to simplify it or does it not work and you're trying to understand why? If it works, then maybe it's good enough to get the job done. If it doesn't, then it's pretty hard to offer guidance without any insight to your schema.

lbecket's avatar

@Sinres I like the idea about scopes, but only to the extent that the logic you're isolating is reusable. If efficiency is a problem, then that's an argument for refactoring, which might have to start with your schema. However, if it works and runs efficiently enough for your needs (and isn't likely to need heavy modification over time), then I say move on and just accept that it's not pretty.

1 like

Please or to participate in this conversation.