In our recent project we implemented Sphinx Search for an osCommerce based site. We included the full path (breadcrumb) of category into search. I thought the breadcrumb query might be helpful for many developers. Note we had the nested categories up to 9 levels, you can easily adjust query for any level. You may or may not need the id or name so you can remove to make query simple
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
SELECT node.categories_id AS node_id, d_node.categories_name AS node_name, u1.categories_id AS u1_id,d1.categories_name AS d1_name, u2.categories_id AS u2_id,d2.categories_name AS d2_name, u3.categories_id AS u3_id,d3.categories_name AS d3_name, u4.categories_id AS u4_id,d4.categories_name AS d4_name, u5.categories_id AS u5_id,d5.categories_name AS d5_name, u6.categories_id AS u6_id,d6.categories_name AS d6_name, u7.categories_id AS u7_id,d7.categories_name AS d7_name, u8.categories_id AS u8_id,d8.categories_name AS d8_name, u9.categories_id AS u9_id,d9.categories_name AS d9_name FROM categories node LEFT JOIN categories_description d_node ON d_node.categories_id = node.categories_id AND d_node.language_id = 1 LEFT JOIN categories u1 ON u1.categories_id = node.parent_id LEFT JOIN categories_description d1 ON d1.categories_id = u1.categories_id AND d1.language_id = 1 LEFT JOIN categories u2 ON u2.categories_id = u1.parent_id LEFT JOIN categories_description d2 ON d2.categories_id = u2.categories_id AND d2.language_id = 1 LEFT JOIN categories u3 ON u3.categories_id = u2.parent_id LEFT JOIN categories_description d3 ON d3.categories_id = u3.categories_id AND d3.language_id = 1 LEFT JOIN categories u4 ON u4.categories_id = u3.parent_id LEFT JOIN categories_description d4 ON d4.categories_id = u4.categories_id AND d4.language_id = 1 LEFT JOIN categories u5 ON u5.categories_id = u4.parent_id LEFT JOIN categories_description d5 ON d5.categories_id = u5.categories_id AND d5.language_id = 1 LEFT JOIN categories u6 ON u6.categories_id = u5.parent_id LEFT JOIN categories_description d6 ON d6.categories_id = u6.categories_id AND d6.language_id = 1 LEFT JOIN categories u7 ON u7.categories_id = u6.parent_id LEFT JOIN categories_description d7 ON d7.categories_id = u7.categories_id AND d7.language_id = 1 LEFT JOIN categories u8 ON u8.categories_id = u7.parent_id LEFT JOIN categories_description d8 ON d8.categories_id = u8.categories_id AND d8.language_id = 1 LEFT JOIN categories u9 ON u9.categories_id = u8.parent_id LEFT JOIN categories_description d9 ON d9.categories_id = u9.categories_id AND d9.language_id = 1 |
Leave A Comment