Query builder Laravel

Friends, I have the following query running in my database manager.

SELECT us.name, us.id, COUNT(o.id) AS c_orders 
FROM users us, model_has_roles mhr, orders o 
WHERE us.id = mhr.model_id and mhr.role_id=2 
AND o.user_id = us.id 
GROUP BY o.user_id

The result is as follows:

name       id   c_orders
Luis        4     7
Alejandro   10    1

This is my method:

public function getOrders()
 {
     $sql = "SELECT us.name, us.id, COUNT(o.id) AS c_orders 
                        FROM users us, model_has_roles mhr, orders o
                        WHERE us.id = mhr.model_id and mhr.role_id=2
                        AND o.user_id = us.id                        
                        GROUP BY o.user_id";

                return ['ordenes' => $sql];
}

I don't know how to pass this query using query builder with laravel.

Obviously with my method I can't show what I get in my database manager. Instead I get this:

{ "ordenes": "SELECT us.name, us.id, COUNT(o.id) AS c_orders \n FROM users us, model_has_roles mhr, orders o\n WHERE us.id = mhr.model_id and mhr.role_id=2\n AND o.user_id = us.id \n GROUP BY o.user_id" }

How could I optimize this query using laravel's orm? I thank you that you can help me.

jlrdw

6 years ago

Level 75

I would suggest working some of the examples in the docs.

And the from scratch video series he covers more basic crud as well.

And you could use that query as is

https://laracasts.com/discuss/channels/laravel/sql-native-to-query-builder

Also I just gave a quick answer a while back to someone looking for join and group by together.

This is just an example:

$quy = Powner::query()->leftJoin('dc_pets', 'dc_powners.ownerid', '=', 'dc_pets.ownerid')
                ->select('dc_powners.ownerid', 'dc_powners.oname')
                ->selectRaw('count(dc_pets.petid) as countOfPets')
                ->groupby('dc_powners.ownerid')
                ->orderby('dc_powners.oname')
                ->get();

Results basically give:

ownerid, oname, countOfPets

Like:

5|Bob|3
4|Greg|9
2|Rob|1
// more

rjruiz

6 years ago

Level 1

With the following query at the native sql level I am getting the users that have the operator role and who have assigned work orders that then group them by the user_id column of my orders table. So to get that result I combine 3 users us, model_has_roles mhr and orders tables

What I am trying to say is that just like this, the query in native sql obviously in laravel is not working for me. From what I understand there is a way to pass that same query to Laravel something that escapes for the moment of my knowledge.

Please or to participate in this conversation.