Easier query for multiple LIKE clauses

[Psycho]

I'm looking for a way to perform multiple LIKE conditions on a field without a string of ORs. In a perfect world, MySQL would support something such as 

WHERE field LIKE (SELECT matchField FROM table WHERE . . . )

Here is my situation:

My records in a table we'll call "data" all have a value called an AreaPath which is essentially a hierarchical list. This value is coming from the source data and I have no control over it. Many items will have the same area path. Here is a mock example of what the data might look like:

• Cars\Autos\Peugeot
• Cars\Autos\Peugeot\ Peugeot  404\
• Cars\Autos\Peugeot\ Peugeot  404\Coupe\1952
• Cars\Autos\Peugeot\ Peugeot  405\
• Cars\Autos\Toyota
• Food\Bread\Croissant
• Food\Pasta\Spaghetti

During an import of the data, I'll be creating/updating a table called "areas" with all the unique area paths.

areaId | areaPath
  1      Cars\Autos\Peugeot
  2      Cars\Autos\Peugeot\Peugeot  404\
  3      Cars\Autos\Peugeot\Peugeot  404\Coupe\1952
  4      Cars\Autos\Peugeot\Peugeot  405\
  5      Cars\Autos\Toyota
  6      Food\Bread\Croissant
  7      Food\Pasta\Spaghetti

In my database, I have a table called "Groups" where a user can define a group by name:

groupId | groupName
  9       French

Then, the user will be able to define area paths that will be associated with the group (table groupareas). But, the user does not have to define all the distinct area paths only the top levels. For example, if the user selects the area path "Cars\Autos\Peugeot", then it would encompass that specific area path and ALL paths below it (e.g. "Cars\Autos\Peugeot\Peugeot 404\", "Cars\Autos\Peugeot\Peugeot 505\", "Cars\Autos\Peugeot\Peugeot 404\Coupe", etc.)

groupAreaId | groupId | areaId
  1             9         2
  2             9         6

The above example would define the group "French" to include the area paths of "Cars\Autos\Peugeot" and "Food\Bread\Croissant".

So, what I need to do is be able to dynamically query all the records that would belong to a particular group based on that data. Like I said above, in a perfect world I could do something like this

SELECT *
FROM data
WHERE areapath LIKE (
    SELECT CONCAT(areaPath, '%')
    FROM areas
    JOIN groupareas
      ON areas.areaId = groupareas.areasId
      AND groupareas.groupId = ?
  }

But, that won't work. So, my fallback solution would be to run two queries. First query the area paths associated with a group and then programmatically build a query with OR conditions. 

    SELECT CONCAT(areaPath, '%')
    FROM areas
    JOIN groupareas
      ON areas.areaId = groupareas.areasId
      AND groupareas.groupId = ?

Then iterate through the results to build this. Note: I would have to also convert the backslashes.

SELECT *
FROM data
WHERE
(    areapath LIKE "Cars\\Autos\\Peugeot%"
  OR areapath LIKE "Food\\Bread\\Croissant%"
)

While that would work, I will be running lots of different queries against the data to run various reports on different groups. Having to programmatically create the queries will be inefficient. I'm looking to see if there is a way to pull all the records for any "group" in one query by just changing the groupID. I hope this all makes sense.

[Danishhafeez]

To achieve this without dynamically building a query with OR conditions, you can use a JOIN with a LIKE condition.

SELECT DISTINCT d.*
FROM data d
JOIN groupareas ga ON d.areaId = ga.areaId
JOIN areas a ON ga.areaId = a.areaId
JOIN (
    SELECT groupId, GROUP_CONCAT(areaPath) AS areaPaths
    FROM groupareas ga
    JOIN areas a ON ga.areaId = a.areaId
    GROUP BY groupId
) AS grouped_areas ON ga.groupId = grouped_areas.groupId
WHERE CONCAT(d.areapath, '\\') LIKE CONCAT(grouped_areas.areaPaths, '\\%')
AND ga.groupId = ?;

The inner subquery (grouped_areas) fetches all the area paths associated with each group and concatenates them into a single string using GROUP_CONCAT. Each group's area paths are grouped by the groupId.

The main query then joins the data table with groupareas, areas, and grouped_areas subquery. It uses CONCAT to compare each areapath from the data table with the concatenated area paths of the group.

The LIKE condition checks if the areapath of each data record starts with any of the concatenated area paths for the group. This effectively matches all records whose areapath is a child of any of the defined area paths for the group.

Finally, the DISTINCT keyword ensures that each record is only returned once, preventing duplicates.

Best Regard

Danish Hafeez | QA Assistant

ICTInnovations