Jump to content

Find which field matched a selector in a find query


schwarzdesign
 Share

Recommended Posts

I'm working on a search page for a complex site where the search term is searched for in a couple dozen fields, including fields nested inside repeaters and repeater matrixes. While it's easy to get a list of pages that contain the search term somewhere, the result doesn't include the information which field matched the search term. I was hoping that there's an easy way to find out which field matched the find query. Of course, I could iterate over all fields that I'm searching through and check whether they match the search term with $page->matches       (something like the solution I posted here), but is there an easier way?

Link to comment
Share on other sites

Not "easier", but fun to dive into: if you have MySQL >= 5.7.8, you could convert the page data into JSON and store that string in the database in an InnoDB table with a JSON column when you save the page. Then use MySQL's JSON_SEARCH function to get the hits and fields (paths). This only works with exact or LIKE queries, though, and you will have a little additional work to convert the returned paths to field names. Of course, you will probably still have to write the nested loops to get the data into the database. Not sure how much FieldtypeRepeater->exportValue() can help you avoid that.

Table create statement example:

CREATE TABLE jsonsearch (
  pages_id INT NOT NULL,
  pagedata JSON,
  PRIMARY KEY(pages_id)
) Engine=InnoDB;

Hypothetical page data for equally hypothetical page 1013 as stored in JSON:

{
   "name": "vehicles",
   "title": "Our Vehicles",
   "data": [
      {
         "type": "motorbike",
         "color": "blue"
      },
      {
         "type": "car",
         "specs": {
            "colors": [
               "red",
               "green"
            ],
            "subtype": "motorbike"
         }
      },
      {
         "type": "bike",
         "color": "black"
      }
   ]
}

Query example:

/* Get page id and fields where any field's value equals "motorbike" */

select id, json_search(jsondata, 'all', 'motorbike') as paths
from testjson
where json_search(jsondata, 'all', 'motorbike') is not null;

/* Output:
id		paths
1013	["$.data[0].type", "$.data[1].specs.subtype"]
*/


/* Get page id and fields where field's value contains "bike" */
select id, json_search(jsondata, 'all', '%bike%') as paths
from testjson
where json_search(jsondata, 'all', '%bike%') is not null;

/* Output:
id		paths
1013	["$.data[0].type", "$.data[1].specs.subtype", "$.data[2].type"]
*/

It also allows searching by a field name and by full and partial paths.

/**
 * Search all "type" fields in the page's "data" array (repeater) for the string "bike".
 * This would find results with bike contained in a field data.type but not in
 * data.relatedvehicles.type
 */

select id, json_search(jsondata, 'all', '%bike%', NULL, "$.data[*].type") as paths
from testjson
where json_search(jsondata, 'all', '%bike%', NULL, "$.data[*].type") is not null;

/* Output:
id		paths
1013	["$.data[0].type", "$.data[2].type"]
*/


/**
 * Search all "type" fields at any level for the string "bike".
 * This would find results with bike contained both in data.type AND in
 * data.relatedvehicles.type
 */

select id, json_search(jsondata, 'all', '%bike%', NULL, "$**.type") as paths
from testjson
where json_search(jsondata, 'all', '%bike%', NULL, "$**.type") is not null;

/* Output:
id		paths
1013	["$.data[0].type", "$.data[2].type"]
*/

Converting the JSON path syntax back to something that makes sense for PW would just mean to json_decode() the paths value, cut off the first two chars and remove the digits within curly braces (though the numeric value are of course the index number for calling eq() on a Repeater/PageArray to get the individual item).

  • Like 3
Link to comment
Share on other sites

Hi @BitPoet, wow, thanks for that great mini-tutorial! I haven't used the JSON data type at all yet, but this looks super interesting. Probably a bit overkill for my current project, but that is definitely something that would be worth considering for more individual searching requirements, or possibly a search module. I'll keep that in mind ?

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...