Build the given mysql query in Laravel Style

Hello,

I want to build the following query in Laravel style.

SELECT au.username, COUNT(a.id) as TotalTrx, SUM(a.amount) AS totalSales 
FROM app_users AS au 
LEFT JOIN accounts AS a ON (au.id=a.user_id)
WHERE 
a.status=0 AND a.source=3 AND (au.parent_id = 10001 OR  au.id= 10001 ) 
GROUP BY au.username ORDER BY totalSales DESC

here app_users table has a User model, accounts has a Account model.

Relationships:

User hasMany Account and Account belongsTo User.

What would be the simplest way to get the results?

Update: I want to rebuild this query too!

SELECT totalS.date AS finalDate, IFNULL(totalS.total_sales,0) AS finalSales, IFNULL(totalP.total_payments,0) AS finalPayments
FROM
(   
    SELECT c.datefield AS date, IFNULL(SUM(a.epoints),0) AS total_sales
    FROM accounts AS a
    RIGHT JOIN calendar AS c ON (DATE(FROM_UNIXTIME(jtime)) = c.datefield)
    WHERE (
            c.datefield BETWEEN
                (SELECT MIN(DATE(FROM_UNIXTIME(jtime))) FROM accounts) 
                AND
                (SELECT MAX(DATE(FROM_UNIXTIME(jtime))) FROM accounts)
            )
            AND
            a.source=3 AND a.status=0
    GROUP BY date
    ORDER BY date DESC

) AS totalS
LEFT JOIN
(
    SELECT c.datefield AS date, IFNULL(SUM(a.epoints),0) AS total_payments
    FROM accounts AS a
    RIGHT JOIN calendar AS c ON (DATE(FROM_UNIXTIME(jtime)) = c.datefield)
    WHERE (
            c.datefield BETWEEN
                (SELECT MIN(DATE(FROM_UNIXTIME(jtime))) FROM accounts)
                AND
                (SELECT MAX(DATE(FROM_UNIXTIME(jtime))) FROM accounts)
            )
            AND
            a.source=1 AND a.status=0 AND fromid='ADMIN'
    GROUP BY date
    ORDER BY date DESC

) AS totalP ON (totalS.date=totalP.date) 
WHERE totalS.date BETWEEN '2015-01-15' AND '2015-01-26'
ORDER BY finalDate ASC;

[email protected]

11 years ago

Best Answer

Level 18

something along the line of:

User::leftJoin('accounts', 'app_users.id', '=', 'accounts.user_id')
    ->where('accounts.status', '=', 0)
    ->where('accounts.source', '=', 3)
    ->orWhere(function($query)
    {
        $query->where('app_users.parent_id', '=', 10001)
            ->where('app_users.id', '=', 10001);
    })
    ->groupBy('app_users.username')
    ->orderBy('totalSales', 'DESC')
    ->get([
        'app_users.username',
        DB::raw('COUNT(accounts.id) as TotalTrx'),
        DB::raw('SUM(accounts.amount) as totalSales')
    ]);

1 like

pmall

11 years ago

Level 56

I would use a combinaison of relationships and collections methods.

$user = User::Where('id', 10001)->orWhere('parent_id', 10001)->firstOrFail();
$accounts = $user->accounts()->where('status', 0)->where('source', 3)->get();

$TotalTrx = count($accounts);
$totalSales = $accounts->sum(function($account){ return $account->amount; }); // maybe it can be written $accounts->sum('amount');

Of course you can do custom models scopes for the two queries to shorten this a bit.

1 like

divanoli

11 years ago

Level 15

Hello @pmall, @sleeman2007! Thanks for your replies.

@sleeman2007: Your code seems OK but you are confused with my AND conditions.

a.status=0 AND a.source=3 AND (au.parent_id = 10001 OR  au.id= 10001 )

All three conditions should pass. and you took my 3rd condition partials as AND. But I have got the proper idea from your code. Thanks for that.

@pmall:

$user = User::Where('id', 10001)->orWhere('parent_id', 10001)->firstOrFail();

I expect a collection of users not a single object. orWhere('parent_id', 10001) would result many users that has the given parent_id.

divanoli

11 years ago

Level 15

Right now this code from @sleeman2007 does the trick. But waiting for @pmall's reply to check if is there any other way around.

$chartUsers = User::leftJoin('accounts', 'app_users.id', '=', 'accounts.user_id')
            ->where('accounts.status', '=', 0)
            ->where('accounts.source', '=', 3)
            ->Where(function($query)
            {
                $query->where('app_users.parent_id', '=', 10001)
                    ->orWhere('app_users.id', '=', 10001);
            })
            ->groupBy('app_users.username')
            ->orderBy('totalSales', 'DESC')
            ->get([
                'app_users.username',
                DB::raw('COUNT(accounts.id) as TotalTrx'),
                DB::raw('SUM(accounts.amount) as totalSales')
            ]);

pmall

11 years ago

Level 56

My reply would be there is no benefits of using eloquent over raw query for a query like that.

If you want to use laravel and get the maximum benefits from it, you need to adapt a bit the structure of your app and your database. How about storing totalTrx and totalSales in the user table ?

divanoli

11 years ago

Level 15

hello @pmall,

I can store them in the user table. but i have to update them for each and every transactions. @sleeman2007: I have updated my question with an extra query. How do I rebuild it in Laravel style. Note: I don't want to use eloquent here.

Please or to participate in this conversation.