MySQL query components table

Question

Problem overview

I am attempting to query an item table that is constrained to an item_component table by two foreign keys - representing a kind of "parent-child" component relationship where an item can be comprised of one or more items.

While some of the human-readable fields are in a character-separated-values format, the actual data is stored in a normalized relational-db format. Below, I give a minimal reproducible example of the structures, with data, and my failed attempts. To be clear, I am not storing the primary data in CSV format, and I am not attempting to query CSV strings.

What I have

I have (something like) the following tables:

item:
+----+---------------------+
| id |      item_name      |
+----+---------------------+
|  1 | apple               |
|  2 | banana              |
|  3 | cherry              |
|  4 | date                |
|  5 | elderberry          |
|  6 | apple_banana        |
|  7 | apple_cherry        |
|  8 | apple_banana_cherry |
|  9 | unexpected_name     |
+----+---------------------+

item_component:
+----+---------+-------------------+
| id | item_id | component_item_id |
+----+---------+-------------------+
|  1 |       6 |                 1 |
|  2 |       6 |                 2 |
|  3 |       7 |                 1 |
|  4 |       7 |                 3 |
|  5 |       8 |                 1 |
|  6 |       8 |                 2 |
|  7 |       8 |                 3 |
|  8 |       9 |                 2 |
|  9 |       9 |                 5 |
+----+---------+-------------------+

Note that "singular" item entries do not map to item_component.item_id, and item entries in item_component do not have to follow any naming convention.

What I'd like to do

I'd like to perform queries on the item table using the item_name field for items that map to component_item_id. Because item_name is not required to have any logical association to an item's components' item_names, I can not simply perform a string-match search on item_name.

I'm failing to use words well, so the best way I can think to explain my needs are with examples:

1. Search for a single term

   Input: include="apple",
          exclude=""
   
   Output:
   +----+---------------------+
   | id |      item_name      |
   +----+---------------------+
   |  1 | apple               |
   |  6 | apple_banana        |
   |  7 | apple_cherry        |
   |  8 | apple_banana_cherry |
   +----+---------------------+
   

2. Search for multiple "AND" terms

   Input: include="apple+banana",
          exclude=""
   
   Output:
   +----+---------------------+
   | id |      item_name      |
   +----+---------------------+
   |  7 | apple_cherry        |
   |  8 | apple_banana_cherry |
   +----+---------------------+
   

3. Search for multiple "OR" terms

   Input: include="apple|date",
          exclude=""
   
   Output:
   +----+---------------------+
   | id |      item_name      |
   +----+---------------------+
   |  1 | apple               |
   |  4 | date                |
   |  6 | apple_banana        |
   |  7 | apple_cherry        |
   |  8 | apple_banana_cherry |
   +----+---------------------+
   

4. Search for multiple "AND" terms, with exclude terms

   Input: include="apple+cherry",
          exclude="banana"
   
   Output:
   +----+--------------+
   | id |  item_name   |
   +----+--------------+
   |  7 | apple_cherry |
   +----+--------------+
   

5. Search for multple "AND" terms and "OR" terms

   Input: include="(apple+cherry)|elderberry"
          exclude=""
   
   Output:
   +----+---------------------+
   | id |      item_name      |
   +----+---------------------+
   |  5 | elderberry          |
   |  7 | apple_cherry        |
   |  8 | apple_banana_cherry |
   |  9 | unexpected_name     |
   +----+---------------------+
   

6. Search for multple "AND" terms and "OR" terms, with exclude terms

   Input: include="(apple+cherry)|elderberry"
          exclude="banana"
   
   Output:
   +----+---------------------+
   | id |      item_name      |
   +----+---------------------+
   |  5 | elderberry          |
   |  7 | apple_cherry        |
   +----+---------------------+
   

These examples are not exhaustive, but should exemplify the concepts I'm looking for to cover all use cases.

Attempts I have made

The example input for these tests are:

include="apple+cherry"
exclude="banana"

1. queries:

   SELECT id, item_name
     FROM item
    WHERE id IN (
          SELECT item_id
            FROM item_component
           WHERE component_item_id IN (
                 SELECT id
                   FROM item
                  WHERE item_name IN ('apple', 'cherry')
                    AND item_name NOT IN ('banana')
           )
   );
   
   SELECT i.id, i.item_name
     FROM item AS i
          INNER JOIN item_component AS ic
             ON ic.item_id = i.id
          INNER JOIN item AS i2
             ON i2.id = ic.component_item_id
    WHERE i2.item_name IN ('apple', 'cherry')
      AND i2.item_name NOT IN ('banana')
    GROUP BY i.id;
   

   all resulted in the same output:

   +----+---------------------+
   | id | item_name           |
   +----+---------------------+
   |  6 | apple_banana        |
   |  7 | apple_cherry        |
   |  8 | apple_banana_cherry |
   +----+---------------------+
   

2. query:

   SELECT id, item_name
     FROM item
    WHERE id IN (
          SELECT item_id
            FROM item_component
           WHERE component_item_id IN (
                 SELECT id
                   FROM item
                  WHERE item_name IN ('apple', 'cherry')
           )
             AND component_item_id NOT IN (
                 SELECT id
                   FROM item
                  WHERE item_name NOT IN ('banana')
           )
   );
   

   resulted in empty set