Jul 30, 2018

Level 3

query to check there are registration types with price "> 0"

I have a table registration_types that is associated with a conference, a conference can have multiple registration types.

So the registration_types table have columns like: id, name, price, conference_id.

And I want to do a query to check if for a specific conference there are registration types where the price is not free, that is the price is ">0".

I already have the conference id with:

        $conference = Conference::where('id',$id)->firstOr(function(){
            return redirect('/');
        });
        $conferenceID = $conference->id;

But now do you know how to know if there are registration types where the price is not 0 for that $conferenceID?

Models:

RegistrationType model:

    class RegistrationType extends Model
    {
    
        public function conference(){
            return $this->belongsTo('App\Conference');
        }
    }

Conference model:

    class Conference extends Model
    {
        public function registrationTypes(){
            return $this->hasMany('App\RegistrationType', 'conference_id');
        }
    }

Like this is not working

$paidConferencesExist = $conference->whereHas("registrationTypes", function($query){
                $query->where('price', '>', '0');
            });

adamjhn

7 years ago

Level 3

Like this is not working

$paidConferencesExist = $conference->whereHas("registrationTypes", function($query){
                $query->where('price', '>', '0');
            });

The $paidConferencesExist shows:

 Collection {#344 ▼
  #items: array:3 [▼
    0 => Conference {#333 ▶}
    1 => Conference {#342 ▶}
    2 => Conference {#345 ▶}
  ]
}

instead of show the registration types where the price is ">0". But it should show the total price of all registration types so is possible to check if the conference is free or not.

RonB1985

7 years ago

Level 18

Not sure but try it like this:

$paidConferencesExist = $conference->whereHas("registrationTypes", function($query){
                $query->where('price', '>', 0);
            });

1 like

lostdreamer_nl

7 years ago

Best Answer

Level 53

$paidConferences = Conference->whereHas("registrationTypes", function($query){
    $query->where('price', '>', '0');
})->get();

This actually will give the conferences that have registrationTypes with a price > 0;

Check it with this:

$paidConferences = Conference->whereHas("registrationTypes", function($query){
    $query->where('price', '>', '0');
})->get();
dd($paidConferences->toArray());

Every conference in there should have at least 1 registrationType with a price > 0

If you already have a conference loaded up, but want to see if it is a paid one, you could do this:

$isPaid = (bool) $conference->registrationTypes->where('price', '>', 0)->count();
dd($isPaid);

// or put it in a mutator in your Conference model:
public function getIsPaidAttribute() 
{
    return $this->registrationTypes->where('price', '>', 0)->count() > 0;
}
$conference = Conference::find(1);
dd( $conference->is_paid );

1 like

Hitostacha

7 years ago

Level 1

You should add the with() if you want to try @lostdreamer_nl 's method like this:

$conference = Conference::find(1)->with('getIsPaidAttribute');
dd( $conference);

lostdreamer_nl

7 years ago

Level 53

@Hitostacha not needed. As there is only 1 object, we dont have the N+1 query problem.

And as Eloquent will check if the key you are accessing is a relation, and if the relation is not loaded yet, it will load it for you.

This will work fine ;)

Just watch out with the following:

$conferences = Conference::get();
foreach($conferences as $conference) {
    echo $conference->is_paid;
}

That would give a N+1 problem and should be done as:

$conferences = Conference::with('getIsPaidAttribute')->get();
foreach($conferences as $conference) {
    echo $conference->is_paid;
}

Also: find()->with() is the wrong way around, either with()->find() or find()->load()

1 like

Hitostacha

7 years ago

Level 1

I haven't tried it that way, that's nice to know.

Also: find()->with() is the wrong way around, either with()->find() or find()->load()

My bad, you're right

Please or to participate in this conversation.