Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.
Hi Guys, I have a dashboard that is querying the same model several times in the same call with minor differences. Just looking to find out how I can clean this up a bit more and use less code/kill the repetition.
public function index()
{
$user = Auth::user();
$posts = Post::where('category', 'Post')->orderBy('created_at', 'Desc')->simplePaginate(5);
$investigations = Investigation::orderBy('created_at', 'Desc')->get();
$received_investigations = count(Investigation::whereYear('created_at', 2022)->where('status', 'Open')->where('type', 'Investigation')->get());
$received_reviews = count(Investigation::whereYear('created_at', 2022)->where('status', 'Open')->where('type', 'Review')->get());
$received_enquiries = count(Investigation::whereYear('created_at', 2022)->where('status', 'Open')->where('type', 'Enquiry')->get());
$open_investigations = count(Investigation::whereYear('created_at', 2022)->where('status', 'Open')->where('type', 'Investigation')->get());
$open_reviews = count(Investigation::whereYear('created_at', 2022)->where('status', 'Open')->where('type', 'Review')->get());
$open_enquiries = count(Investigation::whereYear('created_at', 2022)->where('status', 'Open')->where('type', 'Enquiry')->get());
$closed_investigations = count(Investigation::whereYear('created_at', 2022)->where('status', 'Closed')->where('type', 'Investigation')->get());
$closed_reviews = count(Investigation::whereYear('created_at', 2022)->where('status', 'Closed')->where('type', 'Review')->get());
$closed_enquiries = count(Investigation::whereYear('created_at', 2022)->where('status', 'Closed')->where('type', 'Enquiry')->get());
$stalled_investigations = count(Investigation::whereYear('created_at', 2022)->where('status', 'Stalled')->where('type', 'Investigation')->get());
$stalled_reviews = count(Investigation::whereYear('created_at', 2022)->where('status', 'Stalled')->where('type', 'Review')->get());
$stalled_enquiries = count(Investigation::whereYear('created_at', 2022)->where('status', 'Stalled')->where('type', 'Enquiry')->get());
$data = ([
'page_name' => 'Dashboard',
'user' => $user,
'posts' => $posts,
'investigations' => $investigations,
'received_investigations' => $received_investigations,
'received_reviews' => $received_reviews,
'received_enquiries' => $received_enquiries,
'open_investigations' => $open_investigations,
'open_reviews' => $open_reviews,
'open_enquiries' => $open_enquiries,
'closed_investigations' => $closed_investigations,
'closed_reviews' => $closed_reviews,
'closed_enquiries' => $closed_enquiries,
'stalled_investigations' => $stalled_investigations,
'stalled_reviews' => $stalled_reviews,
'stalled_enquiries' => $stalled_enquiries,
]);
return view('dashboard')->with($data);
}
For a start you can do the following:
Investigation::whereYear('created_at', 2022)
->where('status', 'Stalled')
->where('type', 'Enquiry')
->count()
....which will return integers and you don't need to count collections returned
@luukuummo Well first of all, you really don’t need to fetch all records just to count them. That’s wholly inefficient to pull rows into memory just for the sake of counting them. Just do a count query in the first place.
For example:
Investigation::whereYear('created_at', 2022)->where('status', 'Open')->where('type', 'Investigation')->count()
Instead of:
count(Investigation::whereYear('created_at', 2022)->where('status', 'Open')->where('type', 'Investigation')->get())
Secondly I'd advise reading about local scopes in Laravel: https://laravel.com/docs/9.x/eloquent#local-scopes. These will help you write simple scopes that will be help you reduce too much repetition. I am going to assume this thread is purely for code and not for performance... so you'd be able to write scopes that mean you can simply write...
Investigation::forCurrentYear()->get()
....simply as an example name of course... but that would look something like this in your model..
public function scopeForCurrentYear(Builder $query)
{
return $query->where('year', now()->year);
}
@jaseofspades88 thanks for this. I see the logic, but haven't grasped the point yet. I will give you feedback once I do so
@luuKuummo I would do something like this.
SELECT
COUNT(CASE WHEN column = 'Value 1' THEN 1 END) AS value1,
COUNT(CASE WHEN column = 'Value 2' THEN 1 END) AS value2,
COUNT(CASE WHEN column = 'Value 3' THEN 1 END) AS value3,
COUNT(CASE WHEN column = 'Value 4' THEN 1 END) AS value4
FROM table1
Both performant and better. Nice suggestion, @tray2
@luuKuummo Start small, write simple scopes and put maybe one or two into practice, then you can really push the boat out. There really are some good series to watch on here that give you all kinds of appreciation and understanding for different techniques.
@luukuummo For code repetition, I would follow @jaseofspades88 suggestion and use scopes
public function scopeForCurrentYear(Builder $query)
{
return $query->where('year', now()->year);
}
public function scopeOpen(Builder $builder)
{
return $builder->where('status', 'Open');
}
public function scopeStalled(Builder $builder)
{
return $builder->where('status', 'Stalled');
}
public function scopeInvestigations(Builder $builder)
{
return $builder->where('type', 'Investigation');
}
public function scopeReviews(Builder $builder)
{
return $builder->where('type', 'Review');
}
public function scopeEnquiries(Builder $builder)
{
return $builder->where('type', 'Enquiry');
}
public function index()
{
$received_investigations = Investigation::forCurrentYear()->open()->investigations()->count();
$received_reviews = Investigation::forCurrentYear()->open()->reviews()->count();
$received_enquiries = Investigation::forCurrentYear()->open()->enquiries()->count();
}
But for performance I would try to get them in a single query, using group by. I would try to avoid so many queries.
Please or to participate in this conversation.