convert mysql query into eloquent

That large of a query id use as is with the DB facade. Do you need an example.

yes @jlrdw please

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

Here is what a basic group by looks like:

$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();

The order of commands matter.

Results basically give:

ownerid, oname, countOfPets

Like:

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

it's not what I'm trying to do. I didn't see any join there.

Yes it is possible:

$rows = DB::table('tbl_users', 'c')
    ->select(['c.id', 'c.username', 'c.first_name', 'c.last_name'])
    ->selectRaw('(a.amount + b.amount + d.amount + e.amount + f.amount) AS total')
    ->leftJoinSub(function ($query) {
        $query->from('earnings_1')
            ->select('user_id')
            ->selectRaw('Sum(amount) AS amount')
            ->groupBy('user_id');
    }, 'a', 'a.user_id', '=', 'c.id')
    ->leftJoinSub(function ($query) {
        $query->from('earnings_2')
            ->select('user_id')
            ->selectRaw('Sum(amount) AS amount')
            ->groupBy('user_id');
    }, 'd', 'd.user_id', '=', 'c.id')
    ->leftJoinSub(function ($query) {
        $query->from('earnings_3')
            ->select('user_id')
            ->selectRaw('Sum(amount) AS amount')
            ->groupBy('user_id');
    }, 'e', 'e.user_id', '=', 'c.id')
    ->leftJoinSub(function ($query) {
        $query->from('earnings_4')
            ->select('user_id')
            ->selectRaw('Sum(amount) AS amount')
            ->groupBy('user_id');
    }, 'f', 'f.user_id', '=', 'c.id')
    ->leftJoinSub(function ($query) {
        $query->from('earnings_5')
            ->select('user_id')
            ->selectRaw('Sum(amount) AS amount')
            ->groupBy('user_id');
    }, 'b', 'b.user_id', '=', 'c.id')
    ->orderByDesc('total')
    ->get();

But honestly, I think the SQL version is more maintainable on the long run:

$sql = <<<SQL
SELECT c.id, 
       c.username, 
       c.first_name, 
       c.last_name, 
       ( a.amount + b.amount + d.amount + e.amount + f.amount ) AS total 
FROM   tbl_users c 
       LEFT JOIN (SELECT user_id, 
                         Sum(amount) AS amount 
                  FROM   earnings_1 
                  GROUP  BY user_id) a 
              ON a.user_id = c.id 
       LEFT JOIN (SELECT user_id, 
                         Sum(amount) AS amount 
                  FROM   earnings_2 
                  GROUP  BY user_id) d 
              ON d.user_id = c.id 
       LEFT JOIN (SELECT user_id, 
                         Sum(amount) AS amount 
                  FROM   earnings_3 
                  GROUP  BY user_id) e 
              ON e.user_id = c.id 
       LEFT JOIN (SELECT user_id, 
                         Sum(amount) AS amount 
                  FROM   earnings_4 
                  GROUP  BY user_id) f 
              ON f.user_id = c.id 
       LEFT JOIN (SELECT user_id, 
                         Sum(amount) AS amount 
                  FROM   earnings_5 
                  GROUP  BY user_id) b 
              ON b.user_id = c.id 
ORDER  BY total DESC;
SQL;

$rows = DB::select($sql);

thank you @rodrigo.pedra I found out the the a.amount + b.amount + d.amount + e.amount + f.amount has an issue if one of them is null it will result a null do you something to do with that?

You're welcome!

Well, that is a problem in SQL, almost all operations on NULL result in NULL.

You can use the IFNULL() or COALESCE() SQL functions to workaround that:

->selectRaw('(COALESCE(a.amount, 0.0) + COALESCE(b.amount, 0.0) + COALESCE(d.amount, 0.0) + COALESCE(e.amount, 0.0) + COALESCE(f.amount, 0.0)) AS total')

Hope it helps. Have a nice day =)

wow thank you @rodrigo.pedra

I'm so grateful that you helped me on my two questions :)

You're welcome! Have a nice day =)