Joining table to itself? Could you suggest a fastest query to get these results?

Question

I have a taxonomies table like this:

+-----+--------------+----------------+--------------------+
| id  | name         | is_subcategory | parent_taxonomy_id |
+-----+--------------+----------------+--------------------+
|  80 | Headword     |              0 |                  0 |
|  81 | blonde       |              1 |                 80 |
|  82 | Parents      |              0 |                  0 |
|  83 | Children     |              1 |                 82 |
|  84 | Season       |              0 |                  0 |
|  85 | Winter       |              1 |                 84 |
|  86 | Charm Units  |              0 |                  0 |
|  88 | Etymology    |              1 |                 86 |
|  89 | Word History |              1 |                 86 |
|  90 | Spring       |              1 |                 84 |
|  91 | Summer       |              1 |                 84 |
|  93 | Trends       |              0 |                  0 |
| 109 | Interest     |              0 |                  0 |
| 110 | Sports       |              1 |                109 |
| 111 | Cups         |              0 |                  0 |
| 112 | hot          |              1 |                111 |
| 113 | Speakers     |              0 |                  0 |
| 114 | Hi def       |              1 |                113 |
| 115 | Dual powered |              1 |                113 |
| 118 | Office       |              0 |                  0 |
| 124 | States       |              0 |                  0 |
+-----+--------------+----------------+--------------------+

I need to fetch a list of all categories and subcategories from autocomplete, for example, if user types in sp - he should get this list:

Interest/Sports
Season/Spring
Speakers
Speakers/Dual powered
Speakers/Hi def

Am I correct - I should do a self join? How would I get results in such format?

Edit:

here's my query that seems to work:

SELECT a.id AS cat_id, b.id AS subcat_id, a.name AS cat_name, b.name AS sub_cat_name, CONCAT_WS(' / ', a.name, b.name) AS full_name 
FROM taxonomies as a
LEFT JOIN taxonomies AS b ON a.id = b.parent_taxonomy_id
WHERE (a.name LIKE 'sp%' OR b.name LIKE 'sp%')
ORDER BY full_name DESC;

How can we suggest a faster query if you don't show us what you already made? — Siyual, Jun 22 '15 at 15:17
@Siyual I haven't made any yet. I'm still thinking what would be the fastest approach... — Dannyboy, Jun 22 '15 at 15:18
Have you taken a look at [how-to-create-a-mysql-hierarchical-recursive-query](http://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query) and other linked questions? — wmorrison365, Jun 22 '15 at 15:19
If you only want to have a single layer. One parent and one child then joining to itself will be fine. If you can have an infinite number of parent to children relationships then look at @wmorrison365 's link. — CathalMF, Jun 22 '15 at 15:23
I asume the autocomplete do an ajax call every time on keypress. Why don't get all fields in a single ajax get, store it in an array and then filter it depending on the input text? — nada, Jun 22 '15 at 15:34
@Strawberry why? what's wrong with that? - is there a better way? what should I do instead? — Dannyboy, Jun 22 '15 at 16:15
@The I've tried - it didn't work: http://pastie.org/10253371 — Dannyboy, Jun 22 '15 at 16:19
@The u still need brackets? I don't get the correct results... Here's a new pastie: http://pastie.org/10253413 — Dannyboy, Jun 22 '15 at 16:44
`AND (a.name LIKE 'sp%' OR b.name LIKE 'sp%' )` try this one — The Reason, Jun 22 '15 at 16:56
@The but that brings more results then needed. http://pastie.org/10253457 — Dannyboy, Jun 22 '15 at 17:08

score 1 · Answer 1 · answered Jun 22 '15 at 17:02

Consider the following:

DROP TABLE my_table;

CREATE TABLE my_table
(id  INT NOT NULL PRIMARY KEY
,name         VARCHAR(20) NOT NULL UNIQUE
,parent_taxonomy_id INT NULL
);

INSERT INTO my_table VALUES
( 80,'Headword',NULL),
( 81,'blonde',80),
( 82,'Parents',NULL),
( 83,'Children',82),
( 84,'Season',NULL),
( 85,'Winter',84),
( 86,'Charm Units',NULL),
( 88,'Etymology',86),
( 89,'Word History',86),
( 90,'Spring',84),
( 91,'Summer',84),
( 93,'Trends',NULL),
(109,'Interest',NULL),
(110,'Sports',109),
(111,'Cups',NULL),
(112,'hot',111),
(113,'Speakers',NULL),
(114,'Hi def',113),
(115,'Dual powered',113),
(118,'Office',NULL),
(124,'States',NULL);

SELECT n.*
  FROM 
     ( SELECT x.id x_id
            , x.name x_name
            , y.id y_id
            , y.name y_name
         FROM my_table x 
         LEFT 
         JOIN my_table y 
           ON y.parent_taxonomy_id = x.id 
        WHERE x.parent_taxonomy_id IS NULL
     ) n 
 WHERE (x_name LIKE 'sp%' OR y_name LIKE 'sp%');

+------+----------+------+--------------+
| x_id | x_name   | y_id | y_name       |
+------+----------+------+--------------+
|   84 | Season   |   90 | Spring       |
|  109 | Interest |  110 | Sports       |
|  113 | Speakers |  114 | Hi def       |
|  113 | Speakers |  115 | Dual powered |
+------+----------+------+--------------+

score 0 · Answer 2 · answered Jun 22 '15 at 16:11

A speed up of the process could be done by reducing the number of results fetched and storing the taxonomy path:

+-----+--------------+----------------+--------------------+--------+
| id  | name         | is_subcategory | parent_taxonomy_id | path   |
+-----+--------------+----------------+--------------------+--------+
|  80 | Headword     |              0 |                  0 | 0      |
|  81 | blonde       |              1 |                 80 | 80/0   |
|  82 | Parents      |              0 |                 81 | 81/80/0|

The search for Pa would get you as response:

|  82 | Parents      |              0 |                 81 | 81/80/0|

But instead of now recursive joining on parent_taxonomy_id you would execute a query searching for records 80,81 and 0, which are quick key lookups.

Key to this is that you do not fetch all the possible categories (so limit the user to 5-10 top options for example) since that would lead to too many key lookups (even if you would optimize the lookups by de-duplicating them first).

In ajax style this works really well:

You can present the results of the first query immediate:

../Parents

Then delayed replace the ../ with the path:

0/Headword/blonde/parent

(record 0 was not in your data, you can define 0 yourself of course :) )

Joining table to itself? Could you suggest a fastest query to get these results?

2 Answers2