Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.
Hey.
I have the following query which I would like to convert to eloquent:
select EXTRACT(YEAR_MONTH FROM created_at) as period, SUM(total) as total
from tickets
where deleted_at is null and spam = 0 and unknown = 0 and folder_id in (52, 54, 56) and created_at between '2015-10-10' and '2017-11-06'
group by YEAR(created_at), MONTH(created_at)
The variables for the query are the folders ids and the start and end date for the between clause. The rest should be treated as constant.
How would you do that?
What is the problem here, it is just a bunch of where clauses and some raw db statements.
Here the problem I see is you store the date as a timestamps but query the year and the month. Just add columns for year and mount and everything will be easier. When using an orm like eloquent you should not keep thinking in term od raw queries but in terms of objects you want to retrieve.
@pmall That's not an option. The query above is for statistics. There is an object Ticket which I'm doing a-lot with.
Have you tried something? It looks simple to me.
This deleted_at is null will be eliminated because eloquent does not include trashed by default.
This EXTRACT(YEAR_MONTH FROM created_at) as period can be done with Carbon easily.
Select the tickets objects then get the sum from them with the collection methods. If you make stats on months and years, then store them in individual columns.
It is kind of weird to try to use eloquent to retrieve something else than eloquent objects if you see my point.
Eloquent is not for everything, if you find yourself doing some weird, complex queries just use DB.
@pmall @RachidLaasri You both are right. I will just use the DB facade.
Please or to participate in this conversation.