MySQL Summary Query in Laravel 5.2

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?

from Newest questions tagged laravel-5 - Stack Overflow http://ift.tt/2lkXkeR
via IFTTT