Add Where Nested Support

[likeadeckofcards]

We need to look into adding WhereNested in the query builder. This should work similar to the whereIn and that the resulting query needs to be distributed appropriately based on the current find request.

Some tests and their resulting find requests:

User::where('age', '>', 10)
    ->where(function($query) {
             $query->where('name_first', 'michael')
                   ->orWhere('name_last', 'deck');
    });
        
Find Requests:
[
   [
        'age' => '>10',
        'name_first' => 'michael'
    ],
    [
        'age' => '>10',
        'name_last' => 'deck',
    ]
]

[qt2git]

Hi

Is there a way to accomplish the or behaviour with version 1 or 2 from this package?
I tried to do it according to the function in FMBaseBuilder.php but it seems not to work as expected.

Initial situation: Records of events with various states (active, bookable ef.)
Task: Text search for records in mutliple fields with static conditions (active => true, bookable => true)
Behaviour: When adding an "or" condition, it searches in multiple fields but omits the static conditions (see above)

Is there a way to solve this with version 1?
--> we do use the package "jetstream-filemaker" that depends on version 1.

Thanks for a hint.
BR. Martin

[Smef]

I don't think this is implemented at all yet, but you can do it just by writing regular find requests.

User::where('age', '>', 10)->where('name_first', 'michael')->orWhere('age', '>', 10)->where('name_last', 'deck');

You can upgrade Jetstream FileMaker to v2, which should also install Eloquent FileMaker v2. That's probably the easiest solution!

With v2 I recommend enabling the cache_session_token config option as well in your database config.

[qt2git]

Hi
Wow, that was quick :-) Thanks.

I tried your example, but I do not get the desired result.

Event::where('d_Standort_n', '=', $eventLocale) ->where('xCalc_WebOnline_ctu', '=', 1) ->where('d_NameD_t', $this->search) ->orWhere('xCalc_Kursleitung_ctu', $this->search)

While the first two where-clauses need to be intact and the following two are either or...

Is this possible?

Thanks also for the hint about version 2 from the filemaker-jetstream package.

[Smef]

You need to move your orWhere

Event::where('d_Standort_n', '=', $eventLocale)
->where('xCalc_WebOnline_ctu', '=', 1)
->orWhere('xCalc_Kursleitung_ctu', $this->search)
->where('d_NameD_t', $this->search);

[qt2git]

Hi

Unfortunately this does not encapsulate the query right. Because the first two where conditions are mandatory and the "orWhere" is a supplement.
In SQL code the query would look like this (not the brackets around the text query conditions):
SELECT * FROM veranstaltung WHERE d_Standort_n = 2 AND xCalc_WebOnline_ctu = 1 AND ( d_NameD_t LIKE "%des%" or xCalc_Kursleitung_ctu LIKE "%des%" )

or in eloquent style:
$data = Veranstaltung::where('d_Standort_n', 1) ->where('xCalc_WebOnline_ctu', 1) ->where(function ($query) use ($search) { $query->where('d_NameD_t', 'LIKE', '%' . $search . '%') ->orWhere('xCalc_Zielgruppe_ctu', 'LiKE', '%' . $search . '%'); }) ->get();

Is there a way to solve this with filemaker-eloquent?

BR and thank you for your support!

[Smef]

Ah, I see. This is just a matter of organizing the FileMaker finds correctly. You can think about how you'd manually enter this into multiple FileMaker finds.

        $data = Veranstaltung::query()
            ->where('d_Standort_n', 1)->where('xCalc_WebOnline_ctu', 1)->where('d_NameD_t',  $search )
            ->orWhere('d_Standort_n', 1)->where('xCalc_WebOnline_ctu', 1)->where('xCalc_Zielgruppe_ctu',  $search )
            ->get();

would give you two find requests, like

        [
            [
                'd_Standort_n' => 1,
                'xCalc_WebOnline_ctu' => 1,
                'd_NameD_t' => $search,
            ],
            [
                'd_Standort_n' => 1,
                'xCalc_WebOnline_ctu' => 1,
                'xCalc_Zielgruppe_ctu' => $search,
            ]
        ]

[qt2git]

Hi

You're a HERO!
Of course. My bad!
Like this, I can handle the search correct.

Thanks a lot for your help.,
and have a good WE!

[macbookandrew]

Ran into this while doing some testing for #78. You probably know this already, but for future reference, the nested query is not getting converted to params when making the request: