count registrations in conferences where the registration type price > 0

I have a query to count the number of registrations in a confernece:

$registrationsCount = $conference->registrations->count();


But I want to get only the registrations in conferences that are associated with registration types which price is > 0. Do you know how to achieve that? For example if the conference "test conference" has two registration types "rt1 and rt2", the price of rt1 is 0 and the price of the rt2 is 10 and there are 5 registrations in the registration type "rt2" the query should return 5, because there are 5 paid registrations in the conference.

Conference model:

public function registrationTypes(){
return $this->hasMany('AppRegistrationType', 'conference_id');
}

public function registrations(){
return $this->hasMany('AppRegistration', 'conference_id');
}


Registration model:

public function registration_types(){
return $this->belongsToMany('AppRegistrationType', 'registration_registration_types');
}


public function conference(){
return $this->belongsTo('AppConference');
}


Registration Type model:

public function conference(){
return $this->belongsTo('AppConference');
}

public function registrations(){
return $this->belongsToMany('AppRegistration', 'registration_registration_types');
}


Participants model:

public function registration(){
return $this->belongsTo('AppRegistration');
}


public function registration_type(){
return $this->belongsTo('AppRegistrationType');
}


tables structure:

conferences: id, name
registrations: id, status, conference_id, user_that_did_registration
registration_types: id, name, price, conference_id
participants: id, registration_id, registration_type_id, name


3 Answers
3

You can do it like this :

$count = Registration::whereHas('registration_types', function($q) use($user) {
$q->where('price ', '>', 0);
})
->where('conference_id', $conferenceId)
->count();


registrations

registration_types

registration_registration_types

registration_types

id

registration_registration_types

registration_type_id

registrations

id

registration_registration_types

registration_id

price

conference_id

registration_registration_types

To be honest, I didn't fully understand your request, but I see the following two possibilities:

Conference::whereHas('registrationTypes', function ($query) {
$query->where('price', '>', 0);
})
->withCount('registrations')
->get();

// will give you something like this
[
{
"id": 1,
"name": "Laracon",
"registrations_count": 100
}
]


or alternatively, you could perform the price check within the count

Conference::withCount(['registrations' => function ($query) {
$query->whereHas('registration_types', function ($query) {
$query->where('price', '>', 0);
});
}])
->get();


conferences

registrations

conferences

id

registrations

conference_id

registration_types

registration_registration_types

registration_types

id

registration_registration_types

registration_type_id

registrations

id

registration_registration_types

registration_id

price

registrations_count

conferences

Try this code

$conference->registrations->sum(function ($registration) {
$registrationTypes = $registration->registration_types->where('price', '>', 0);
return $registrationTypes ? $registrationTypes->count() : 0;
});


where

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.