MySQL Summary Query in Laravel 5.2

Question

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?

score 1 · Accepted Answer · edited May 23 '17 at 12:09

The year bit could be used to create a JOIN between the tables (and their respective result sets) to produce the combined output that can further be sorted by any field of choice:

SELECT t1.year_rec, IFNULL(t1.matters, 0) AS matters, IFNULL(t2.notes, 0) AS notes
FROM
    (
        SELECT DATE_FORMAT(created_at,  "%Y" ) AS year_rec, COUNT(id) AS matters
            FROM matters
        GROUP BY year_rec 
    ) t1
    LEFT JOIN 
    (
        SELECT DATE_FORMAT(created_at,  "%Y" ) AS year_rec, COUNT(id) AS notes
            FROM notes
        GROUP BY year_rec
    ) t2
ON t1.year_rec = t2.year_rec
--  ORDER BY --

Demo

Caveat:

Keeping in mind the nature of JOINs and that MySQL doesn't provide a direct means to write FULL OUTER JOIN, you'd notice that if a certain year doesn't have any records in the first table participating in the LEFT JOIN, then that year will be omitted from the final output produced. If we changed the query to have a RIGHT JOIN, then the omission will be carried out based on the second table instead. However, if that is not going to happen in your case (i.e. if you think there will always be records for every year in both the tables), you needn't worry about this situation, but it'll still be good to be aware of this loophole.

For further reading: Full Outer Join in MySQL

Thank you!!! This is precisely what I was looking for. I had forgotten you could join on a date formatted as a year. Thank you! — JasonJensenDev, Feb 28 '17 at 18:42

MySQL Summary Query in Laravel 5.2

1 Answers1