I'm working on a project written in Laravel 5.2 that has two tables that need to be queried to produce a summary for the amount of records created by year. Here is a simplified layout of the schema with some sample data:
Matters Table
id created_at
-----------------
1 2016-01-05 10:00:00
2 2016-03-09 11:00:00
3 2017-01-03 10:00:00
4 2015-05-06 11:00:00
Notes Table
id created_at
-----------------
1 2015-07-08 10:00:00
2 2016-03-16 11:00:00
3 2017-09-03 10:00:00
4 2017-11-06 11:00:00
Each table has several hundred thousand records, so I'd like to be able to (efficiently) query my data to produce the following results with the counts of each table by year:
year matters notes
----------------------------
2015 1 1
2016 2 1
2017 1 2
I need each column to be sortable. Currently, the fastest way I can think of to do this is to have two queries like the following and then combine the results of the two via PHP:
SELECT YEAR(matters.created_at) AS 'year', COUNT(1) AS 'matters'
FROM matters
GROUP BY YEAR(matters.created_at)
SELECT YEAR(notes.created_at) AS 'year', COUNT(1) AS 'notes'
FROM notes
GROUP BY YEAR(notes.created_at)
But I'm wondering if there is a better way, especially since I have to work in sorting each column based on the user's needs.
Any thoughts?
via Chebli Mohamed
Aucun commentaire:
Enregistrer un commentaire