Laravel advanced query

Question

Affiliate has many affiliatesHistory, affiliatesHistory belongs to affiliate, how to make the following query?

Take affiliates, where has affiliatesHistory, if affiliatesHistory records count is equal to 1, then do not take affiliatesHistory, which has status of uninstalled.

$affiliates = $user->affiliates()
        ->whereDoesntHave('affiliatesHistory', function ($q) {
        $q->where('affiliates_histories.status', 'Installed earlier')
 ->orWhere('affiliates_histories.status', 'Uninstalled / Installed earlier');

The following query works, but I need to not take those affiliates, where affiliatesHistory count is equal to 1 and the status is uninstalled.

Any help will be appriaciated.

did you try and left join with raw query if you want to follow in that way i can write a answer — Abdul Hakeem
– Abdul Hakeem, Commented Jan 12, 2021 at 17:31

MP-programming · Accepted Answer · 2021-01-12 16:12:34Z

0

So, for what I understand you want to get the affiliates which affiliatesHistory status is Installed earlier. If this is the case then try this:

$user_affiliates =  $user->affiliates(); 
$affiliates = $user_affiliates->whereHas('affiliatesHistory', function($q){
   $q->where('status', 'Installed earlier');
})->get();

dd($affiliates);

answered Jan 12, 2021 at 16:12

MP-programming

866 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

M Khalid Junaid · Accepted Answer · 2021-01-12 18:50:51Z

0

For your case if there are more than one affiliatesHistory items then return else if there is only one affiliatesHistory then it should not contain Uninstalled status, I guess you can use conditional count to get desired results as

$affiliates = Affiliate::withCount([
    'affiliatesHistory',
    'affiliatesHistory as affiliatesHistoryUninstalled_count' => function ($query) {
        $query->where('status', 'Uninstalled');
    }
    ])->where('user_id', $user->id)
      ->havingRaw('affiliatesHistory_count > 1 OR (affiliatesHistory_count = 1 AND affiliatesHistoryUninstalled_count = 0)')
      ->get();

answered Jan 12, 2021 at 18:50

M Khalid Junaid

64.6k10 gold badges93 silver badges120 bronze badges

Collectives™ on Stack Overflow

Laravel advanced query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related