Search problem with AND and OR and modified query

[Susticle]

Hello,

there is (simplified):

    a template "job"
        field "job_title"

    a template "facility"
        field "facility_title"
        repeater "facility_jobs"
            field "facility_job_title"
            field "facility_job_description"
            field "facility_job" => pagereference to pages using the template "job"

The facility template has a repeater to list jobs with an own name and description for this facility. In this repeater there is also a page reference to the template job. Everything is fine so far and everything works.

But I don't get a search solved as I want.

I need a result if *every* given word to search for matches facility_title OR facility_jobs.facility_job_title OR facility_jobs.facility_job.job_title.

So
template=facility
AND
(word1%=facility_title OR word1%=facility_jobs.facility_job_title OR word1%=facility_jobs.facility_job.job_title)
AND
(word2%=facility_title OR word2%=facility_jobs.facility_job_title OR word2%=facility_jobs.facility_job.job_title)
AND
(word3%=facility_title OR word3%=facility_jobs.facility_job_title OR word3%=facility_jobs.facility_job.job_title)

I need this in one big selector because I do manual changes on the generated sql statement later.

I created a selector array:

foreach($arr_words as $i_number => $str_word) {
    $arr_selector[] = array(
        'template' => 'facility',
        'field' => 'title',
        'operator' => '%=',
        'value' => $str_word,
        'group' => 'group'.$i_number
    );
    $arr_selector[] = array(
        'template' => 'facility',
        'field' => 'facility_jobs.facility_job_title',
        'operator' => '%=',
        'value' => $str_word,
        'group' => 'group'.$i_number
    );
    $arr_selector[] = array(
        'template' => 'facility',
        'field' => 'facility_jobs.facility_job.job_title',
        'operator' => '%=',
        'value' => $str_word,
        'group' => 'group'.$i_number
    );
}

Sadly this doesn't work. It creates an OR-result. I need group1 AND groupX, but I get group1 OR groupX. I tried lots of stuff, but don't get it solved. Because of later changes to the sql statements, performance reasons, pagination I only want to search once.

Any help is appreciated. Thank you! :-)

[Robin S]

Don't know about the selector arrays version but here is a selector string version:

$selector = "template=facility";
foreach($arr_words as $str_word) {
    $selector .= ", title|facility_jobs.facility_job_title|facility_jobs.facility_job.job_title%=$str_word";
}

BTW, when I have a load of fields I need to search across I often find it preferable to use a Pages::saveReady hook to populate a hidden "index" textarea field with the merged text of the other fields in that template and then just search that index field.

[adrian]

Take a look at this post (https://processwire.com/talk/topic/19024-selector-arrays-with-page-reference-fields-with-and-not-or/?do=findComment&comment=165462) and the rest of the thread.

I really like the concept of selector arrays and they can make things much more readable and avoid concatenation stuff, but currently they can be a little tricky to get things how you want.

Hope that helps.

[Susticle]

8 hours ago, Robin S said:

Don't know about the selector arrays version but here is a selector string version:

$selector = "template=facility";
foreach($arr_words as $str_word) {
    $selector .= ", title|facility_jobs.facility_job_title|facility_jobs.facility_job.job_title%=$str_word";
}

 

Thanks for your answer. This was the first way I tried it. But it results in an error message:

Quote

Error: Exception: Multi-dot 'a.b.c' type selectors may not be used with OR '|' fields

 

[Susticle]

7 hours ago, adrian said:

Take a look at this post (https://processwire.com/talk/topic/19024-selector-arrays-with-page-reference-fields-with-and-not-or/?do=findComment&comment=165462) and the rest of the thread.

 

Thank you very much. I'll look into this!

[Robin S]

29 minutes ago, Susticle said:

Error: Exception: Multi-dot 'a.b.c' type selectors may not be used with OR '|' fields

Oh, right. To work around that limitation you can most likely rewrite your multi-dot selectors as sub-selectors:
https://processwire.com/api/selectors/#sub-selectors
https://processwire.com/blog/posts/processwire-3.0.6-brings-pages-upgrades-and-link-abstraction/#improvements-to-sub-selectors

Although I'm not certain they can be used with OR pipes either. Maybe you'll need OR groups. Let us know how you get on.

[Susticle]

2 hours ago, Robin S said:

Oh, right. To work around that limitation you can most likely rewrite your multi-dot selectors as sub-selectors: [...] Although I'm not certain they can be used with OR pipes either. Maybe you'll need OR groups. Let us know how you get on.

Thank you!

I tried OR groups but didn't get them together to get the desired result.

In the meantime I found out that the cause of the problem seems to be the Multi-dot selector. Not just for the OR '|' fields but also for my approach with the array.

The parts of the array are working with AND logic until I add the part with the Multi-dot selector. So I did a little workaround: There are two searches now. The first one does an OR search in facility_jobs.facility_job.job_title with all given words. The resulting list of IDs is now a filter for the selector array instead of the Multi-dot selector. This seems to work quite well now.

Maybe this can be done with one request using sub-selectors. I will look into this later.