Use of group_concat with eagger loading in Laravel

Question

Use of group_concat with eagger loading in Laravel

Navigation

#1 by (1 votes)

1

I have to return in a table in HTML, the name of the user and the names of posts; however, I have registered more than one post user; for which I require to use group_concat() for put them in a single cell; I'm trying it this way, but I'm not I get back no data from the posts table

public function listado()
    {
        $data = User::with(['posts' => function($query){
            $query->select(DB::raw("GROUP_CONCAT(namePost SEPARATOR '-') as 'N'"));
        }])->get();
        return view('posts')->with(['data' => $data]);
    }

I know I'm not invoking the posts table, but I do not know how to do it; I would appreciate your help

In SQL I do it this way and it's functional

MariaDB [blog]> select users.nameUser, GROUP_CONCAT(posts.namePost) AS N
    -> FROM users
    -> JOIN posts
    -> ON users.id = posts.user_id
    -> GROUP BY users.nameUser;

I make the clarification that within Eloquent I am making use of eagger loading; why do you work with the hasMany() and belongsTo() relationships?

The result I'm looking for is something like that

+----------+---------------+
| nameUser | N             |
+----------+---------------+
| alfa     | PHP 7,MySQL 8 |
| beta     | HTML 5        |
+----------+---------------+

mysql laravel laravel-5

asked by element 26.08.2018 в 02:38

source

1 answer

Regular expression decimal numbers Error: std :: out_of_range

score 1 · Accepted Answer

Responding to my own publication, I chose not to do so with eagger loading and the relaciones offered by Laravel; then I mixed the ORM and functions of Fluent the query Builder

The result of the query I got is the following

$data = User::select('users.nameUser')
    ->selectRaw('GROUP_CONCAT(namePost) AS Listado')
    ->join('posts', 'users.id', '=', posts.user_id)
    ->groupBy('nameUser')
    ->get();

The selectRaw() method allows me to execute SQL functions that do not exist within the ORM natively

The SelectRaw () method can be used instead of DB :: Raw () to carry out the creation of queries in SQL language reference