Eloquent nested sub condition

I'm confuse on how to structure my tables or how to code a correct eloquent to get data I want, please enlighten me. I'm rookie.

I have tables

members
- id
- name
location_lists
- id
- date
- zone
store_lists
- id
- location_list_id
- member_id
- store_code

LocationList :

public function store_lists()
{
    return $this->hasMany(StoreList::class, 'location_list_id');
}

StoreList :

public function location_list()
{
    return $this->belongsTo(LocationList::class);
}
public function members()
{
    return $this->belongsTo(Member::class); //??? or hasOne() ???
}

While location_lists store many store_lists data let's say 1 location might have 50 store_lists records

What I want to achieve is to get all members who listed in a store_lists table with a specific date

Will merging location_lists and store_lists better? If it is will it take more query time to get what I want?

And for the code I'm trying to achieve which is all wrong but i hope u get the idea.

Member::where( function ($q) {
    $_date = substr(\Carbon\Carbon::today(), 0, 10);
    $today = LocationList::where('date', $_date)->get();
    // StoreList somewhere???
    foreach ($today as $t) {
        $q->where('location_list_id', $t->id); //<--- wrong again ...
    }
})

What is the correct eloquent I should use please. Thank you.

Dchubb

8 years ago

Best Answer

Level 4

Try this

$today = Carbon::today();
$members = Member::whereHas('store_lists', function ($q) use ($today) {
    $q->whereHas('location_list', function ($q) use ($today) {
        $q->whereDate('date', '=', $today);
    });
})->get();

1 like

rumm.an

8 years ago

Level 17

You want to list all member whose associated store_list has a date equal to what you want. Right?

Member::whereHas('store_list', function($query) use ($today) {
    $query->where('date', $today);
})->get();

You can also eagerload other relationships if you want. But make sure you have defined the store_list relationship on Member model.

rumm.an

8 years ago

Level 17

Oh! My bad. I didnt see date is on location_list. What you can do is define an intermediate relationship on Member class like so :

public function location_list()
{
    return $this->store_list->location_list();
}

and then you can query the model relation loacation_list in whereHas as in above example.

godxavia

8 years ago

Level 2

Thanks to all of you. With some of the work around with relationships finally I had it right.

Glad to have your back. Cheers.

Please or to participate in this conversation.