Laravel withSum where clause column not found

Question

I'm using Laravel 9 to perform a withSum on my relationship credit_transactions. I need to return all PersonalAccessToken models where the credit_balance is less than a certain value, for example, 50 - this way my application knows whether to top up their balance.

This is my query:

/** * Get all keys that can be auto topped-up */ public function getEnabledTopUpKeys() { return PersonalAccessToken::whereNotNull('region_code') ->where('auto_topup_enabled', true) ->whereNotNull('auto_topup_slug') ->withSum('credit_transactions AS credit_balance', 'delta') ->where('credit_balance', '<', 100) ->get(); }

This doesn't work, it throws an error:

Unknown column 'credit_balance' in 'where clause'

What am I missing to perform this?

aynber · Accepted Answer · 2022-12-29 18:35:46Z

withSum generates a sub query, so it can't be used in a where query. But that sub query creates an on-the-fly column that you can add to your query. For instance,

Order::withSum('orderItems', 'quantity') ->take(5) ->get();

creates an aliased column named order_items_sum_quantity, that I can then use as a having query:

Order::withSum('orderItems', 'quantity') ->having('order_items_sum_quantity', '>', 100) ->take(5)->get();

In your case, you may be getting a column named credit_balance_sum_delta that you can use in your query:

PersonalAccessToken::whereNotNull('region_code') ->where('auto_topup_enabled', true) ->whereNotNull('auto_topup_slug') ->withSum('credit_transactions AS credit_balance', 'delta') ->having('credit_balance_sum_delta', '<', 100) ->get();

If that isn't quite the correct column name, try getting one record without the having line, then check the results to find out the column name

Collectives™ on Stack Overflow

Laravel withSum where clause column not found

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related