How to translate this query from SQL(mysql) to Eloquent(Laravel)

Question

In writing a custom query that returns users with their posts grouped and a count with the total posts made by them

query in SQL

SELECT users.nameUser, COUNT(posts.namePost) AS Total, GROUP_CONCAT(posts.namePost) AS List
FROM users
JOIN posts ON users.id = posts.user_id
GROUP BY users.nameUser;
+----------+-------+-----------------------+
| nameUser | Total | List                  |
+----------+-------+-----------------------+
| alfa     |     2 | PHP 7,Aire comprimido |
| beta     |     2 | HTML 5,MySQL 8        |
+----------+-------+-----------------------+

The question is, how can build this query with Eloquent Laravel or even with it query Builder?

check this url please: github.com/spatie/laravel-query-builder — Mohit
– Mohit, Commented Sep 22, 2018 at 1:31

Tpojka · Accepted Answer · 2018-09-22 00:36:51Z

1

You can pass raw query string as DB statement:

$data = DB::statement('
    SELECT users.nameUser, COUNT(posts.namePost) AS Total, 
    GROUP_CONCAT(posts.namePost) AS List
    FROM users
    JOIN posts ON users.id = posts.user_id
    GROUP BY users.nameUser
')->get();

answered Sep 22, 2018 at 0:36

Tpojka

7,0812 gold badges32 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Tpojka Over a year ago

Thanks. Do you have set User and Post models with relation between?

user10398682 Over a year ago

yeah I was forgotten it, both tables have a relationship

Tpojka Over a year ago

I wondered could you use something like User::with('posts')->all() and then use some of helper functions available to filter or sort result.

Lakhwinder Singh · Accepted Answer · 2019-06-22 15:41:11Z

1

This is only a way to do it, using Eloquent ORM and for certain tasks using Fluent

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

Useful stuff

You can use selectRaw() method instead of DB::raw() method
I use the User model to avoid include more query builder code
I use groupBy() method

edited Jun 22, 2019 at 15:41

Lakhwinder Singh

5,5825 gold badges30 silver badges52 bronze badges

answered Sep 21, 2018 at 23:59

user10398682

Collectives™ on Stack Overflow

How to translate this query from SQL(mysql) to Eloquent(Laravel)

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related