CODEWITHSUNDEEP
phplaravel
hretic
hretic

Reputation: 1085

query relation based on parent column

i have 2 tables 

accounts  : id , title , disabled , transaction_amount_limit , transaction_count_limit 

account_limits : id , account_id , transaction_amount , transaction_count , date

so each account has bunch of transaction each day ... i want to select the a account that hasn't reached its transactions limit .... current transaction for each account is stored in account_limits table

basically i want to say select account that doesn't have account_limits row or have account_limits but hasn't reached the limits account_limits.transaction_amount < accounts.transaction_amount_limit && account_limits.transaction_count < accounts.transaction_count_limit

something like 

select * from `accounts`

(  where not exists (select * from `account_limits` where `accounts`.`id` = `account_limits`.`account_id`)
OR 
where exists (select * from `account_limits` where `accounts`.`id` = `account_limits`.`account_id` &&  account_limits.transaction_amount < accounts.transaction_amount_limit  && account_limits.transaction_count < accounts.transaction_count_limit)
)

i have this so far 

    $account = Account::where('disabled' , 0 )->where(function($q){
        $q->whereDoesntHave('AccountLimit')->orWhere('....') ;
    })->get();

as @Flame suggested i tried 

Account::whereHas('accountLimits', function($query) {
    $query->where('account_limits.transaction_amount', '<', 'accounts.transaction_amount_limit')
    ->where('account_limits.transaction_count', '<', 'accounts.transaction_count_limit');
})->orHas('accountLimits', '=', 0);

the problem is for some reason 

where('account_limits.transaction_amount', '<', 'accounts.transaction_amount_limit')

in the output will translate to 

where `account_limits`.`transaction_amount`  < 'accounts.transaction_amount_limit'

and query fails , there's problem with quotations 

    this 

 'accounts.transaction_amount_limit' 

should be

 `accounts`.`transaction_amount_limit`

Upvotes: 2

Views: 2942

Answers (3)

Maga Serdarov
Maga Serdarov

Reputation: 1

It is good idea for me to keep transaction_count and transaction_limit in same table (accounts). Then you can compare this columns.

$accounts = Account::whereRaw("transaction_count < transaction_limit)->get();

Upvotes: 0

Flame
Flame

Reputation: 7628

Here is an example answer in the Eloquent syntax. Note that you need to add the relation:

// Account.php , your eloquent model
public function accountLimits()
{
    return $this->hasMany(AccountLimit::class);
}

And for the query:

Account::whereHas('accountLimits', function($query) {
    $query->where('account_limits.transaction_amount', '<', 'accounts.transaction_amount_limit')
    ->where('account_limits.transaction_count', '<', 'accounts.transaction_count_limit');
})->orHas('accountLimits', '=', 0);

This checks for your where-clause in the relation using whereHas, and if it is not a match, it will also add the records that match in the orHas, which finds all Accounts without accountLimits relationships.

Upvotes: 2

Nick
Nick

Reputation: 147216

As a straight MySQL query, something like this should work:

SELECT a.* 
FROM accounts a
JOIN limits l ON l.transaction_amount < a.transaction_amount_limit AND 
                 l.transaction_count  < a.transaction_count_limit

The JOIN condition will filter out any accounts that have met or exceeded either their transaction_amount or transaction_count limits.

Upvotes: 0

Related Questions