Jump to content
Susticle

Search problem with AND and OR and modified query

Recommended Posts

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! :-)

Share this post


Link to post
Share on other sites

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.

  • Like 1

Share this post


Link to post
Share on other sites

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.

  • Like 3

Share this post


Link to post
Share on other sites
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

 

Share this post


Link to post
Share on other sites
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.

  • Like 1

Share this post


Link to post
Share on other sites
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.

  • Like 1

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...