Use SUM of value in related table in where clause

Level 75

The aggregate may need a raw clause, try it. Usually if an aggregate, I just use QB techniques:

Just 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')
                ->where('dc_powners.ownerid', '<', 4)
                ->groupby('dc_powners.ownerid')
                ->orderby('dc_powners.oname')
                ->get();

Not sure about relations above, but aggregate usually needs an as.

Level 4

Thanks @jlrdw , it seems to me your example searches for ownerid's smaller than 4. I would need countOfPets < 4.

I might be missing something, I'll try it out tomorrow.

Level 75

That was just example of using an aggregate, adjust to your needs.

Level 4

I adapted you example to:

$tracks = Track::query()->leftJoin('votes', 'tracks.id', '=', 'votes.track_id')
                ->select('tracks.id', 'votes.track_id')
                ->selectRaw('sum(votes.vote) as score')
                ->where('score', '<', 0)
                ->groupby('tracks.id')
                ->get();

But this gives me:

Column not found: 1054 Unknown column 'score' in 'where clause' (SQL: select tracks.id, votes.track_id, sum(votes.vote) as score from tracks left join votes on tracks.id = votes.track_id where score < 0 and tracks.deleted_at is null group by tracks.id)

I must admit that I need to learn more about aggregates. I don't really know what it means.

Level 75

Try moving this

->selectRaw('sum(votes.vote) as score')

above the other select.

Also no one just whips these queries out, sometimes they take trial and error.

Here:

$quy = Powner::query()->leftJoin('dc_pets', 'dc_powners.ownerid', '=', 'dc_pets.ownerid')

dc_powners is parent table, dc_pets is child. Have to be in correct order.

The regular sql is:

SELECT dc_powners.ownerid, dc_powners.oname, COUNT(dc_pets.petid) AS CountOfpetid
FROM dc_powners LEFT JOIN dc_pets ON dc_powners.ownerid = dc_pets.ownerid
GROUP BY dc_powners.ownerid
ORDER BY dc_powners.oname;

Just the where clause is not in there.

Results basically give:

ownerid, oname, countOfPets

Like:

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

Just example.

Level 4

I really appreciate your support @jlrdw . Believe me, I spend hours searching the web and trying out numerous combinations before I posted the question.

I think I found the solution. My query worked nicely without the where clause but what I needed was to use SUM() in the where clause. Apparently, you can't do that. you can, however, in a havingRaw().

This query seems like what I was looking for.

$tracks = Track::query()->leftJoin('votes', 'tracks.id', '=', 'votes.track_id')
                ->select('tracks.*', 'votes.track_id')
                ->selectRaw('sum(votes.vote) as score')
                ->havingRaw('SUM(vote) < ?', array(0))
                ->groupby('tracks.id')
                ->get();

This seems to give me all tracks where the sum of votes.vote is smaller than 0.

This is not the complete query I need so... up to the next issue ;-)

Thanks again for your help!

1 like

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

Level 75

@KIMMER - So your latest query you show does work? If so, glad you got it.

Like I said, many queries do take some trial and error and head scratching, it's part of being a developer.

God speed with your programming.

FYI and this is just me, I do use eloquent for simple relations, but usually queries like above, I use regular pdo and sql.

At run time eloquent converts to the "regular" sql any way.

A good example of the db facade:

And there's pdo instance, getPdo()

examples

https://laracasts.com/discuss/channels/guides/getpdo-usage

https://laracasts.com/discuss/channels/guides/length-aware-paginator

https://christophersax.com/2016/custom-lengthawarepaginator-in-laravel/

Note when using db facade or getPdo, use proper bindings.

Here is a good guide to pdo:

https://phpdelusions.net/pdo

Didn't know if you were new to pdo or not, but still good guide.

1 like

Level 4

@jlrdw, thanks for the tips.

I did some more testing and up until now the solution with havingRaw() seems to work nicely. I still need to do some testing on larger sets of tracks with more votes but I don't expect more than maybe some tweaking.

I used PDO in the past and I'll surely read up on your suggestions. I have a long way to go on the app I'm building. I'm sure it will need some optimising along the way.

DavidPetrov

Level 2

@KIMMER - Thank god I found that post! Helped me a lot! I've only got one question regarding your working query:

$tracks = Track::query()->leftJoin('votes', 'tracks.id', '=', 'votes.track_id')
                ->select('tracks.*', 'votes.track_id')
                ->selectRaw('sum(votes.vote) as score')
                ->havingRaw('SUM(vote) < ?', array(0))
                ->groupby('tracks.id')
                ->get();

How is ->havingRaw('SUM(vote) < ?', array(0)) working? Shouldn't it be ->havingRaw('score < ?', array(0)) since you've already selected your sum as score? I'm trying to do exactly the same kind of filtering but the having clause keeps failing (either a syntax error or not filtering at all)... Here's my example:

App\Order::query()->leftJoin('revenues', function($j){ return $j->on('orders.id', '=', 'revenues.order_id')->where('revenues.is_paid', true);})
                ->select('orders.*', DB::raw('(case when abs(sum(revenues.price)) < 1 then null else sum(revenues.price) end) as paid_from_customer'))
                ->having('paid_from_customer', '>', 0)
                ->groupby('orders.id');

Which throws an error unknown column 'paid_from_customer'. Tried many variations and can't seem to make it work. Ideas?

Level 4

Hi @davidpetrov , I'm glad my question is of help to you.

I think ->havingRaw('score < ?', array(0)) does not work because "score" is not a actual column in the DB table. I don't know the exact term for it but it is created in the selectRaw clause.

I think the same goes for your paid_from_customer, I think. It seems that'ts what the error is saying. 'paid_from_customer' is not a column in the DB but created in your select().

Hope this helps

DavidPetrov

Level 2

@KIMMER - That's actually the strange problem I'm encountering... The aggregate sum is working fine, but what's actually a real column in my orders table is not being recognized by the query builder... I read a couple of documentation statements saying that different cores for sql had different treatment of the having clause, hence some only allowed aggregates and selected columns to be used, but even after selecting the column manually I still get the same error... So I'm stuck.

Level 4

Sorry, I don't think I can help you further but you might get more help if you create a new question here.

Probably you have but Have you tried havingRaw()?

Level 75

HAVING is usually for the derived column of a as.

WHERE is usually prior to group by for columns in the database, not derived.

But this stuff can be tricky when not used everyday. But look up both in the MySql manual, they will have examples.

boboboy

3 years ago

Level 1

Hello @jlrdw I have question, when I am use aggregate in eloquent relation like

    $products =  Product::with(['brand', 'category', 'variants'])
        ->filter()
        ->withCount([
            'reviews as rating' => function ($query) {
                $query->select(DB::raw('coalesce(round(avg(rating),1),0)'));
            },
            'reviews as review_count'
        ])
        ->withMin('variants as price', 'price')
        ->active()
        ->sort($request)
        ->between($request)
        ->min($request)
        ->like($request)
        ->simplePaginate(20)
        ->withQueryString()
        ->toArray();

and the scope like

public function scopeBetween($query, $request)
{
    $between = explode(',', $request->get('between'));

    return $query->when($request->has('between'), fn () => $query->havingBetween($between[0], [$between[1], $between[2]]));
}

public function scopeMin($query, $request)
{
    $min = explode(',', $request->get('greater_or_equal'));

    return $query->when($request->has('greater_or_equal'), fn () => $query->having($min[0], '>=', $min[1]));
}

why the error showing no price column?

DavidPetrov

Level 2

@JLRDW - Thank you both for your responses! I've opened a thread here: https://laracasts.com/discuss/channels/eloquent/weird-behaviour-on-paginating-with-join-and-having-clause

I've come to a weird conclusion... having actually works fine just the way I stated it, but what causes the issue is the paginator! That means, that error is only being thrown when I add ->paginate(10) instead of ->get() at the end of the query... Any ideas why? Here's the query. Works perfectly:

$orders = App\Order::leftJoin('revenues', function($j){ return $j->on('orders.id', '=', 'revenues.order_id')->where('revenues.is_paid', true);})
                ->havingRaw('price_to_customer - (case when abs(sum(revenues.price)) < 1 then null else sum(revenues.price) end) > ?', [0])
                ->groupby('orders.id')
                ->get();

Doesn't work:

$orders = App\Order::leftJoin('revenues', function($j){ return $j->on('orders.id', '=', 'revenues.order_id')->where('revenues.is_paid', true);})
                ->havingRaw('price_to_customer - (case when abs(sum(revenues.price)) < 1 then null else sum(revenues.price) end) > ?', [0])
                ->groupby('orders.id')
                ->paginate(15);

Error thrown:

Column not found: 1054 Unknown column 'price_to_customer' in 'having clause' (SQL: select count(*) as aggregate from `orders` left join `revenues` on `orders`.`id` = `revenues`.`order_id` and `revenues`.`is_paid` = 1 group by `orders`.`id` having price_to_customer - (case when abs(sum(revenues.price)) < 1 then null else sum(revenues.price) end) > 0)

I didn't want to invade this topic, we can continue anew in my thread. Thank you a lot for your support!

tazimul_lcast

4 years ago

Level 1

Many Many thanks @jlrdw and @kimmer . This post really helps me.

1 like

coreygranderson

11 months ago

Level 1

Hi Laracast Community!

What you're trying to achieve is definitely possible using a GROUP BY and HAVING clause rather than just a WHERE clause, since you're filtering based on an aggregate (the sum of votes). You can join the tracks table with the votes table, group the results by track ID, calculate the total score using SUM(vote), and then filter using HAVING SUM(vote) > X or < X depending on your requirement. The HAVING clause is specifically designed for filtering aggregated results, which the WHERE clause doesn't handle. Let me know if you need help writing the exact SQL query! Try this App: www.telelatinohd.app

Best Regards!

Snapey

11 months ago

Level 122

@coreygranderson Do you know, you sound just like chatgpt?