Support for filtering by pivot value?

[madsem]

I'm trying to get a relationship, but filtered by a pivot value.

Lander::query()
                    ->withoutGlobalScopes()
                    ->where('identifier', $this->landing_page_identifier)
                    ->withWhereHas('landerOfferSets', function ($q) {
                        $q->whereJsonContains('landers.lander_offer_sets', ['lander_element_identifier' => $this->element_identifier]);
                    })
                    ->first();

landers.lander_offer_sets:

[{"lander_offer_set_id": 7, "lander_element_identifier": "23556a9a-0031-332f-86a9-95730a4f8ad0"}, {"lander_offer_set_id": 8, "lander_element_identifier": "23556a9a-0031-332f-86a9-95730a4f8ad1"}]

I expected to get the LanderOfferSet with id 7, but I always get back both.
I'm unsure now if this is even possible to do with this package? (Or am I just querying wrong haha)

[staudenmeir]

Hi @madsem,
How is landerOfferSets() defined?

[madsem]

Hey @staudenmeir 👋

it's defined like so:

/**
     * Get the default Offer Sets for this Lander.
     *
     * @return \Staudenmeir\EloquentJsonRelations\Relations\BelongsToJson
     */
    public function landerOfferSets(): BelongsToJson
    {
        return $this->belongsToJson(LanderOfferSet::class, 'lander_offer_sets[]->lander_offer_set_id');
    }

[staudenmeir]

Can you log the executed query?

[madsem]

Hey @staudenmeir, thank you!

There seems to be something wrong, I am unsure what is going on.
When I trigger this query by visiting an api endpoint in my browser:

return Lander::query()
                    ->withoutGlobalScopes()
                    ->where('identifier', $this->landing_page_identifier)
                    ->withWhereHas('landerOfferSets', function ($q) {
                        $q->whereJsonContains('landers.lander_offer_sets', ['lander_element_identifier' => $this->element_identifier]);
                    })
                    ->first();

Telescope shows this in the query tab:

select
  *
from
  `landers`
where
  `identifier` = '5c0f6264-65e8-3b9a-8aae-33a6f96748ef'
  and exists (
    select
      *
    from
      `lander_offer_sets`
    where
      json_contains(
        `landers`.`lander_offer_sets`,
        json_object('lander_offer_set_id', `lander_offer_sets`.`id`)
      )
      and json_contains(
        `landers`.`lander_offer_sets`,
        '{\"lander_element_identifier\":\"23556a9a-0031-332f-86a9-95730a4f8ad1\"}'
      )
      and `lander_offer_sets`.`deleted_at` is null
  )
limit
  1

Here's the result when I manually run this in my sql client:

But in my browser, I get an exception message with a totally different query:
https://flareapp.io/share/17xQpR85

PS: My goal is to have the Lander with loaded relationship for the LanderOfferSet with id 7, ie: the one that has a matching lander_element_identifier in landers.lander_offer_sets.

But whatever I do, I always get back ALL LanderOfferSet, or an exception where it tries to query lander_offer_sets as a table.

[staudenmeir]

I don't see a way to constrain an eager loading query like this.

But in my browser, I get an exception message with a totally different query:
https://flareapp.io/share/17xQpR85

The eager loading query fails because you are referencing the landers table which isn't available here (only in the whereHas() subquery).

I only see a manual workaround like this:

$lander = Lander::query()->...->first();

$lander->setRelation(
   'landerOfferSets',
   $lander->landerOfferSets->where('pivot.lander_element_identifier' => $this->element_identifier)
);

For a query constraint, my only idea is something horrendous like this:

$landerOfferSets = $lander->landerOfferSets()->whereHas(
    'landers', // Reverse "HasManyJson" relationship
    function($query) use($lander) {
        $query->where('id', $lander->id)
            ->whereRaw("json_contains(`lander_element_identifier`, json_object('id', `lander_offer_sets`.`id`, 'lander_element_identifier', ?))", [$this->element_identifier]);
    }
)->get();