Query using nested field by Entry facade

[saqueib]

how I can query entry by nested relation using Entry facade

$entry = \Statamic\Facades\Entry::query()
->where(????)

I have data like this in yaml file, days is a replicator field with sessions entries

---
id: 5923d4f9-14fa-4012-b06c-28e0b2b190ec
blueprint: conference
title: 'Super Conference 3'
days:
  -
    id: YxernXku
    title: 'Conference Day 1'
    type: day
    enabled: true
    sessions:
      - 5cf7e474-6261-4c61-90bb-790e742950eb
      - c7dda0b8-6036-4a12-93a1-3d77d94f5a1c
      - cefd29b2-0cb1-4ce7-bdb2-938c0d8b053f
  -
    id: JesxrnXku
    title: 'Conference Day 2'
    type: day
    enabled: true
    sessions:
      - aa16074e-75dd-4444-8983-51b7eb081129
      - b2ad339f-6c37-42ee-b4f6-b24556e7b94a
      - 1dc5dafb-4d07-433c-a898-d3dbaef536e1

I want to get conference by session id like 5cf7e474-6261-4c61-90bb-790e742950eb ???

[ryanmitchell]

It's not yet possible, but there are a few PRs pending that will enable it:

#7454
#8476

For now you just ->get() then ->filter()

[saqueib]

@ryanmitchell thanks for update. using get() on all 500 entires is crashing the server ☹️

[duncanmcclean]

Until #7454 / #8476 is merged, you can achieve this but it's a little hacky and you need to know how many sessions will be selected...

$entry = \Statamic\Facades\Entry::query()
    ->where('days->0->sessions', $sessionId)
    ->orWhere('days->1->sessions', $sessionId)
    ->orWhere('days->2->sessions', $sessionId)
    ->orWhere('days->3->sessions', $sessionId)
    ->orWhere('days->4->sessions', $sessionId)
    ->orWhere('days->5->sessions', $sessionId)
    ->orWhere('days->6->sessions', $sessionId)
    ->orWhere('days->7->sessions', $sessionId)
    ->orWhere('days->8->sessions', $sessionId);

Again, it's a little hacky but it works 🤷‍♂️

[duncanmcclean]

What does your $sessionId (or similarly named) variable look like? Is it an array or a string?

[saqueib]

its uuid "8397c34e-9e3a-4b18-9671-5833b7d5210d"

[saqueib]

[ryanmitchell]

Im guessing that days->x->sessions is an array - if so you should be using whereJsonContains

[saqueib]

updated it to

$entry = \Statamic\Facades\Entry::query()
        ->where('collection', 'conferences')
        ->whereJsonContains('days->0->sessions', $sessionId)
        ->orWhereJsonContains('days->1->sessions', $sessionId)
        ->orWhereJsonContains('days->2->sessions', $sessionId)
        ->orWhereJsonContains('days->3->sessions', $sessionId)
        ->orWhereJsonContains('days->4->sessions', $sessionId)
        ->orWhereJsonContains('days->5->sessions', $sessionId)
        ->orWhereJsonContains('days->6->sessions', $sessionId)
        ->orWhereJsonContains('days->7->sessions', $sessionId)
        ->get();

which is coming as empty array [] ☹️