Laravel query builder - How to either group by alias, or do raw groupBy

mysql laravel group-by laravel-5 query-builder
Geoff Clayton picture Geoff Clayton · Mar 31, 2015 · Viewed 19.5k times · Source

My Laravel 5 app includes a dynamic query builder for report running. I need some group by clauses in there and have run into a problem. If I use actual sql in there I can have issues as sometimes there needs to be a sql command in amongst the sql (as opposed to straightforward column names), ie - DAYNAME(table_name.date_column). Laravel mangles this: 

\`DAYNAME(table_name\`.\`date_column)\`

For the select part of my query I can use selectRaw, but there does not seem to be an equivaent for group by.

I thought of using aliases (all the selects are aliased) but Laravel wraps them in "`" characters as well. Besides - my app needs to work with both MySQL and SQL Server and as far as I know the latter does not allow aliases in the group by section of the query.

I can find the method in Illuminate\Database\Query\Grammars\Grammar where the group by is compiled (compileGroups) and I suppose I could override it but I'm not too sure how I would go about that (have read the Laravel docs).

Answer

user1669496 picture user1669496 · Mar 31, 2015

If you want to do raw groupBy, you can do something like this...

...->groupBy(DB::raw('some_alias'))

Related questions

PDOException SQLSTATE[HY000] [2002] No such file or directory

I believe that I've successfully deployed my (very basic) site to fortrabbit, but as soon as I connect to SSH to run some commands (such as php artisan migrate or php artisan db:seed) I get an error message: [PDOException] …

Read More
How to do this in Laravel, subquery where in

How can I make this query in Laravel: SELECT `p`.`id`, `p`.`name`, `p`.`img`, `p`.`safe_name`, `p`.`sku`, `p`.`productstatusid` FROM `products` p WHERE `p`.`id` IN ( SELECT `product_id` FROM `product_category` WHERE `category_id` IN ('223…

Read More
Laravel migration: unique key is too long, even if specified

I am trying to migrate a users table in Laravel. When I run my migration I get this error: [Illuminate\Database\QueryException] SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes (SQL: alter …

Read More