Why doesn't groupBy(some_alias) work in laravel5?

Question

Laravel

There is such a query to the database in SQL

SELECT 
    COUNT(*),
    SUBSTRING_INDEX(SUBSTRING_INDEX(`vars`, '"target":"', -1), '"', 1) AS `target`
FROM `table` 
GROUP BY `target`

Everything is fine with it, I run it in phpMyAdmin - it does exactly what I need.
I'm trying to write it in laravel5 realities, but an error occurs - "an unknown target column"

$table = $table
    ->select(DB::raw('COUNT(*), SUBSTRING_INDEX(SUBSTRING_INDEX(`vars`, \'"target":"\', -1), \'"\', 1) AS target'))
    ->groupBy('target');

I remove " ->groupBy('target') " - the request is executed without errors.
Please tell me what I'm doing wrong and how to do it right.
I found this option " ->groupBy(DB::raw('target')) ", but it doesn't work either.

Answer 1

try swapping " ->groupBy('target') " with ->select(DB::raw('COUNT(*), SUBSTRING_INDEX(SUBSTRING_INDEX(`vars`, \'"target":"\', -1) , \'"\', 1) AS target'))

Answer 2

Remove the request logs that give an error and you will immediately understand what's going on.
Typically, Laravel executes nested queries, getting the number of records and relationships first, and then the records themselves.
Please post the logs so I can help you figure out the error.

Answer 3

<expression> as target is explicitly specified in the sql query,
so sql understands in the group by expression what exactly to group by.
In the Laravel expression, there is no column named target. But apparently, the construction "...as name" or "name=<long expression>" should also work there

Answer 4

It is possible without Laravel realities:
DB::select(here is your query)