I need to know if the following can be resolved with a SQL query and how. I have done it with PHP in different ways but the memory skyrockets and the server takes a long time to return the results. :(

• I have a table called 'parts' with many rows (but many).
• Each of the rows has the columns part1, part2 and part3, all with different values.

Example:

 --------- ------- ------- 
| part1   | part2 | part3 | <-- Column Name
 --------- ------- ------- 
| chamber | wheel | door  | <-- Value
 --------- ------- ------- 

On the other hand I have a list of three values that come from an external source:

$list = ['mirror', 'seat', 'door'];

I need to select the rows whose columns contain all those values, regardless of the order. An example of a valid row would be:

 ------- -------- ------- 
| part1 | part2  | part3 |
 ------- -------- ------- 
| door  | mirror | seat  |
 ------- -------- ------- 

Sometimes the external list doesn't have 3 values, just 1 or 2:

$list = ['antenna'];

... Then the valid rows would be those that contain that value, for example:

 -------- -------- --------- 
| part1  | part2  | part3   |
 -------- -------- --------- 
| engine | mirror | antenna |
 -------- -------- --------- 

• Additional info: In each row, the columns do not have repeated values, so this feature can be ignored in the query.
• I need to know how to make a query for SELECT and another for COUNT.
• It is necessary that the result be resolved through a MySQL query, without using PHP for filtering.

CodePudding user response：

Possible solution.

CREATE TABLE parts
SELECT 'chamber' part1, 'wheel' part2, 'door' part3 UNION ALL
SELECT 'door', 'mirror', 'seat' UNION ALL
SELECT 'engine', 'mirror', 'antenna';
SELECT * FROM parts;

part1	part2	part3
chamber	wheel	door
door	mirror	seat
engine	mirror	antenna

SELECT parts.*
FROM parts
CROSS JOIN (
  SELECT 'mirror' UNION
  SELECT 'seat' UNION
  SELECT 'door'
) criteria (value) 
GROUP BY parts.part1, parts.part2, parts.part3
HAVING SUM(criteria.value IN (parts.part1, parts.part2, parts.part3)) = COUNT(*)

part1	part2	part3
door	mirror	seat

SELECT parts.*
FROM parts
CROSS JOIN (
  SELECT 'mirror' UNION
  SELECT 'antenna'
) criteria (value) 
GROUP BY parts.part1, parts.part2, parts.part3
HAVING SUM(criteria.value IN (parts.part1, parts.part2, parts.part3)) = COUNT(*)

part1	part2	part3
engine	mirror	antenna

SELECT parts.*
FROM parts
CROSS JOIN (
  SELECT 'antenna'
) criteria (value) 
GROUP BY parts.part1, parts.part2, parts.part3
HAVING SUM(criteria.value IN (parts.part1, parts.part2, parts.part3)) = COUNT(*)

part1	part2	part3
engine	mirror	antenna

fiddle

If you don't want to convert your criteria array to subquery text (which must be concatenated into) then you may convert it to JSON array form, provide it to MySQL and parse to separate values in static subquery using JSON_TABLE().

CodePudding user response：

An alternative approach, building on @CBroe's suggestion. If you can sort the criteria before you insert them into the SQL statement, then this might work for you.

First normalise the parts list, then reassemble a string of matching parts in sorted order, separated by commas:

SELECT id, GROUP_CONCAT(part ORDER BY part) AS p
FROM (
    SELECT id, part1 AS part FROM parts
    UNION ALL SELECT id, part2 FROM parts
    UNION ALL SELECT id, part3 FROM parts
) partslist
WHERE part IN (?, ?, ?)
GROUP BY id

Finally wrap in a overall query that extracts the matching rows:

SELECT parts.*
FROM (
    SELECT id, GROUP_CONCAT(part ORDER BY part) AS p
    FROM (
        SELECT id, part1 AS part FROM parts
        UNION ALL SELECT id, part2 FROM parts
        UNION ALL SELECT id, part3 FROM parts
    ) partslist
    WHERE part IN (?, ?, ?)
    GROUP BY id
) partsjoined
INNER JOIN parts ON partsjoined.id = parts.id
WHERE partsjoined.p = CONCAT_WS(',', ?, ?, ?)

(reduce the number of ? parameters as appropriate)

Again, this assumes you're able to sort the criteria parameters before binding them to the statement.