Eloquent: Paginate Two Query Result With Different Condition (Has different with())

Hello fellow Laravel programmer.

---

I'm on Laravel 5.7 and I'm having 4 tables:

outlets - belongsTo merchants, also belongsToMany promos

promos - belongsTo merchants, also belongsToMany outlets

merchants - list of merchants that has many promos (hasMany promos, hasMany outlets)

outlet_promo - that connects outlets and promos, only store their IDs

---

Condition 1#:

Every merchant's promo that only active in specific outlet has outlet_promo values, such as

outlet_promo
outlet_id      promo_id
    1             1

Means that the promo_id 1 only active on outlet_id 1.

Condition 2#:

Now if a promo active on all merchant's outlets means that no relation on outlet_promo. (I'm avoiding too many storing in outlet_promo table)

Fetching Data

I'm trying to get all outlets where has active promos that can be active in some outlets only and active in all outlets, resulting me in this two query:

1. Getting all outlets where has active promos and its promo available in every outlets

$outlets_all = Outlet::with(['merchant.promos' => function ($q) {
            $q->doesntHave('outlets');
        }])->whereHas('merchant.promos', function ($q) {
            $q->whereDate('start_date', '<=',  date('Y-m-d'));
        })->doesntHave('promos.outlets');

2. Getting all outlets where has active promos and its promo only active in selected outlets

$outlets_selected = Outlet::with(['merchant.promos' => function ($q) {
            $q->has('outlets');
        }])->whereHas('promos', function ($q) {
            $q->whereDate('start_date', '<=',  date('Y-m-d'));
        });

The key difference is in with condition

Goals

Within these two queries I managed to get the desired output. But now the goal is I want to paginate both result in one object so I can lazy load the outlets on the view using AJAX. I want to display list of outlets that has promos (all outlets or selected outlets) with list of active promos that outlet has.

What I have tried so far

I tried to union() and unionAll() both queries, resulting correct outlets, but has wrong promos list from merchant.promos. It seems that the union operation messes up the promos. Every merchant's promos seems have same value, when actually they should be different.

I even tried to merge the query with two with() operation like this:

$merge = Outlet::with(['merchant.promos' => function ($q) {
            $q->has('outlets');
        }], ['merchant.promos' => function ($q) {
            $q->doesntHave('outlets');
        }])->whereHas('promos', function ($q) {
            $q->whereDate('start_date', '<=',  date('Y-m-d'));
        })->orWhereHas('merchant.promos', function ($q) {
            $q->whereDate('start_date', '<=',  date('Y-m-d'));
        })->doesntHave('promos.outlets');

And it just results the same as the union I did before.

I'll be tremendously grateful for any help. Thank you.

from Newest questions tagged laravel-5 - Stack Overflow https://ift.tt/2VtRLxj
via IFTTT