Retrieving eloquent models with constraints on both the parent and child/associated model

Question

I'm trying to retrieve all Eloquent Models that match a particular field in the Parent Model ('Event') and the child model ('Dates').

I've hit an issue whereby Laravel is stating that my field ('date') doesn't exist in the child class, but I can't understand why. Can someone please point me in the right direction and explain where I've gone wrong?

Essentially, what I'm trying to achieve is the retrieval of all Events with the approved tag being true AND where the event date is of a particular day, in this case the 10th.

I've done some searching around and looked at some of the examples in the Laravel documentation. I've set up the ('Event') model to have a one to many relationship with the ('dates') model. I can see that I can chain queries together, but things get a little confusing when dealing with more than one model at a time (in the same query)

This is my attempt at retrieving the data.

public function calender()
    {
        $events = Event::where('approved', true)->with('EventDates')->whereDay('date', '10')->get();
        return view('events.calender');
    }

This is a snippet from my ('Event') Model. I've only included the most relevant information here as there are many attributes .

class Event extends Model
{
    //

    public function user(){
        return $this->belongsTo(User::class);
    }

    public function dates()
    {
        return $this->hasMany('App\EventDate');
    }
}

This is a snippet from my ('EventDate') model migration file showing that 'date' is indeed a field of the ('EventDate') model. Once again, I've just included the most relevant function here.

class CreateEventDatesTable extends Migration
{
    public function up()
    {
        Schema::create('event_dates', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->timestamps();

            $table->date('date')->nullable();
            $table->time('startTime')->nullable();
            $table->time('endTime')->nullable();

            $table->unsignedBigInteger('event_id');
            $table->index('event_id');
        });
    }
}

I'd like to be able to retrieve a list of the matching Events that have the approved attribute set to true, and the Event Dates on a particular day (xxxx-xx-10)

Right now, I'm getting the error that the date column can't be found:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'date' in 'where clause' (SQL: select * from events where day(date) = 10 and approved = 1)

Answer 1

I think this is what you are looking for:

    $events = Event::where('approved', true)
        ->with(['dates' => function ($query) {
            return $query->whereDay('date', '10');
        }])
        ->get();

Note: I assume your relation between an Event and it's EventDate is called dates

In this way you are applying filtering by day on the related data (EventDate) and not on the Event model.