This request $request->user()->incomes()->get();
produce something like that:
SELECT * FROM users WHERE id = 2
SELECT * FROM incomes WHERE user_id = 2
You don't need all the columns from each table, this query consume a lot and look at the number of models in the php debug bar, it's crazy !
I didn't test because I don't have the data and time but maybe try something like :
Income::select(DB::raw('SUBSTRING(date_income_received, 1, 4) as year'))
->whereUserId(Auth::id())
->groupBy('year')
->pluck('year');
This way you request only the date_income_received column in one table like in your query builder tip. (I used DB::raw('SUBSTRING()') because you wrote having an issue with SQLITE)
If you want the user's data in the result, you should use eager loading like here laravel.com/docs/8.x/eloquent-rela... and select only the column(s) you need. This way avoid N+1 issues.
You can find good tips that improve your Eloquent perf here reinink.ca/articles.
@simpledevme
you are correct! The following Eloquent query has the same improved performance. I'll edit the article this evening. Thank you for that information!
return Income::selectRaw("substr(date_income_received, 1, 4) as year")
->whereUserId($request->user()->id)
->groupBy('year')
->pluck('year');
For further actions, you may consider blocking this person and/or reporting abuse
We're a place where coders share, stay up-to-date and grow their careers.
So how would you improve the Eloquent query to obtain the same result? I’m open to suggestions.
This request $request->user()->incomes()->get();
produce something like that:
SELECT * FROM users WHERE id = 2
SELECT * FROM incomes WHERE user_id = 2
You don't need all the columns from each table, this query consume a lot and look at the number of models in the php debug bar, it's crazy !
I didn't test because I don't have the data and time but maybe try something like :
Income::select(DB::raw('SUBSTRING(date_income_received, 1, 4) as year'))
->whereUserId(Auth::id())
->groupBy('year')
->pluck('year');
This way you request only the date_income_received column in one table like in your query builder tip. (I used DB::raw('SUBSTRING()') because you wrote having an issue with SQLITE)
If you want the user's data in the result, you should use eager loading like here laravel.com/docs/8.x/eloquent-rela... and select only the column(s) you need. This way avoid N+1 issues.
You can find good tips that improve your Eloquent perf here reinink.ca/articles.
Hope this helps.
@simpledevme you are correct! The following Eloquent query has the same improved performance. I'll edit the article this evening. Thank you for that information!