Monday, 7 January 2019

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



from query relation based on parent column

No comments:

Post a Comment