1

I'm having quite the challenge with selecting hierarchical data in MySQL although I feel the schema should be optimized for it since it's based on the WordPress database model.

I've got 4 tables:

name

+---------+---------------+
| name_id |     name      |
+---------+---------------+
| 1       |     apple     |
+---------+---------------+
| 2       |     orange    |
+---------+---------------+
| 3       |     grape     |
+---------+---------------+
| 4       |     banana    |
+---------+---------------+

name_data

+---------+---------+---------------+
| data_id | name_id |     type      |
+---------+---------+---------------+
| 1       | 1       |   category    |
+---------+---------+---------------+
| 2       | 2       |   category    |
+---------+---------+---------------+
| 3       | 3       |   category    |
+---------+---------+---------------+
| 4       | 4       |   category    |
+---------+---------+---------------+

name_relationships

+---------+---------+-----------+------------+
| rel_id  | data_id | object_id | data_level |
+---------+---------+-----------+------------+
| 1       | 2       | 10        | 0          |
+---------+---------+-----------+------------+
| 2       | 2       | 11        | 0          |
+---------+---------+-----------+------------+
| 3       | 2       | 12        | 0          |
+---------+---------+-----------+------------+
| 4       | 4       | 12        | 1          |
+---------+---------+-----------+------------+

objects

+---------+---------------+
|object_id|     object    |
+---------+---------------+
| 10      |     object1   |
+---------+---------------+
| 11      |     object2   |
+---------+---------------+
| 12      |     object3   |
+---------+---------------+
| 13      |     object4   |
+---------+---------------+

I hope this example isn't over simplified...But I need to be able to select the rows where object_id occurs n number of times without doing a COUNT. The reason I can't use COUNT is because this is an innodb engine, and that will cause a table scan.

So, based on the data above, let's say I only need rows with name_relationships.data_id 2 (name_relationships.object_id 10 & 11) but not rows with name_relationships.data_id 2 and 4. This should prevent the rows with name_relationships.object_id 12 from being selected. But if I base the query on name_relationships.data_id = 2 it still selects name_relationships.object_id 12 because it matches one of the two rows.

Sample query:

SELECT *
FROM objects o
LEFT JOIN name_relationships a ON a.object_id = o.object_id
LEFT JOIN name_data b ON b.data_id = a.data_id
LEFT JOIN name c ON c.name_id = b.name_id
WHERE c.name = 'orange' AND a.data_level IN(0) AND a.data_level NOT IN(1)

I hope this makes sense but if not let me know and I can elaborate. Here is an environment with sample data.

EDIT :
Suppose that orange is a base category, and grape, banana is a subcategory of orange. in this example orange will have many subcategories but I only need to select objects that are related to orange but not grape or banana. Or I may need to select orange and grape but not banana.
This is what I need to do.

EDIT #2:

I am now using this query, and it works exactly like I want but it's too slow. I also added the column meta_count to posts to add anther condition.

SELECT posts.post_id,posts.post_name,posts.title,posts.type,
GROUP_CONCAT(IF(meta_data.type = 'category', meta.meta_name,null)) AS category,
GROUP_CONCAT(IF(meta_data.type = 'tag', meta.meta_name,null)) AS tag
FROM posts
LEFT JOIN meta_relationships ON meta_relationships.object_id = posts.post_id
LEFT JOIN meta_data ON meta_relationships.meta_data_id = meta_data.meta_data_id
LEFT JOIN meta ON meta_data.meta_id = meta.meta_id
WHERE meta_relationships.meta_order IN (1,2) AND posts.meta_count = 2
GROUP BY meta_relationships.object_id
HAVING category = 'animals,birds'
  • 1
    You are missing something in your logic... since name_relationships.data_id = 2 matches 3 rows (logically, forget about sql for the moment) and you only need the first two then there must be another condition which you did not mention. What is that condition? Btw, "The reason I can't use COUNT is because this is an innodb engine, and that will cause a table scan." - why do you think count necessarily causes a table scan? – zgguy Apr 22 '15 at 9:48
  • 2
    "I can't use COUNT is because this is an innodb engine, and that will cause a table scan." Where did you read that (nonsense)? – ypercubeᵀᴹ Apr 22 '15 at 9:52
  • @zgguy - There is no other condition, I suppose that I'm looking for another condition that will satisfy this requirement. I know that COUNT doesn't always cause a table scan, for instance if there's a WHERE clause. But in my case, I tried to COUNT inside a subquery of WHERE, and it was causing a table scan. – EternalHour Apr 22 '15 at 18:31
1

The question is not very clear. If what you want is to have as result **every object that is related to name 'orange' but not to name 'banana', here is one way:

SELECT o.*                  -- Change this to only the columns needed.
FROM objects AS o           -- If there are data needed from other tables,
                            -- join here.
WHERE EXISTS 
      ( SELECT 1 
        FROM name_relationships AS nr
          JOIN name_data AS nd USING (data_id)
          JOIN name AS n  USING (name_id)
        WHERE nc.name = 'orange' 
          AND n.object_id = o.object_id
      )
  AND NOT EXISTS 
      ( SELECT 1 
        FROM name_relationships AS nr
          JOIN name_data AS nd USING (data_id)
          JOIN name AS n  USING (name_id)
        WHERE n.name = 'banana' 
          AND nr.object_id = o.object_id
      ) ;
  • Thanks for the answer. Unfortunately it doesn't solve the problem I am having. The problem appears when I need to select a category which also has one or more subcategories. I need to be able to select the first, second, etc categories while ignoring (without having to specify) the others. Hopefully I can provide a better example in the question. – EternalHour Apr 28 '15 at 0:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.