Currently I have the following Model
:
SELECT m.city_area_id AS city_area_id
FROM menus m
JOIN oc_category c ON (m.id = c.menu_id)
JOIN oc_category oc ON (oc.parent_id = c.category_id)
JOIN oc_product_to_category ptc ON
ptc.category_id = oc.category_id AND
ptc.product_id = $dish_id
The expected behavior of this Model is to find the city area ID given a dish.
It works this way:
- Find our product's [our input] category ID through the table
oc_product_to_category
. - Using the
category_id
from this relationship, we head over tooc_category
to find theparent_id
of this particular product. Once you've reached the top-most category ID, there would be a
menu_id
[themenu_id
is0
if it's not the top-mostcategory_id
, and theparent_id
would also be0
since there will be no more parents] for us to use.Once we have the
menu_id
, we will be able to find this menu's city area ID.
The following is an example of a category_id
row that has a parent_id
. As you can tell, the menu_id
is 0
, and the parent_id
is a non-zero value.
+-------------+-------+-----------+-----+--------+------------+--------+---------------------+---------------------+------------------+---------+
| category_id | image | parent_id | top | column | sort_order | status | date_added | date_modified | visible_official | menu_id |
+-------------+-------+-----------+-----+--------+------------+--------+---------------------+---------------------+------------------+---------+
| 784 | NULL | 783 | 0 | 0 | 1 | 1 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | 1 | 0 |
+-------------+-------+-----------+-----+--------+------------+--------+---------------------+---------------------+------------------+---------+
My issue:
My current SQL query works if for each category_id
, there is only one parent_id
level to traverse. So, for those products that have two parent category_id
s [as opposed to one], my script would return an empty set.
My question is - how do I intelligently traverse from a leaf to the upmost parent no matter how many parents this particular category ID may have?
EDIT: Looks like my SQL row's formatting is all botched. I have no idea how to add a horizontal scrollbar to my code block =(