Eloquent and Query Builder have a lot of "hidden gems" which are not in the official docs. For example, if you want to perform GROUP BY and HAVING, there's a little trick for unnamed columns.
Let's say you want to filter all product categories with more than one products - which means write this query in Eloquent:
SELECT *, COUNT(*) FROM products GROUP BY category_id HAVING count(*) > 1
Basically, it's simple - you would just use groupBy() and having() functions, right? But we don't have the column name for having. Of course, we can assign it, and with Query Builder write something like:
DB::table('products')
->select('*', DB::raw('COUNT(*) as products_count'))
->groupBy('category_id')
->having('products_count', '>' , 1)
->get();
But there's a shorter way - there's a function havingRaw():
Product::groupBy('category_id')->havingRaw('COUNT(*) > 1')->get();
You can add your wanted columns into select(), but essentially that's a shortcut I wanted to briefly show you.
No comments yet…