I’m still trying to get my head round more advanced MySQL but I think I’m slowly getting there. I need some help with a query I’m trying to do but I’m not sure how to go about it and have been scratching my head for the past week on how to do it.
I have 2 tables - content_links
lid
text
url
level
active
content_menus
mid
name
links
skeleton
level
active
So the idea here is that there is a table full of urls and a table where the urls are put into menus. In my template engine I’ll call a function to build a certain menu - eg “index”, to do this I tried the following query -
SELECT `content_links`.`text`, `content_links`.`url`, `content_links`.`level`, `content_menus`.`links`, `content_menus`.`skeleton`, `content_menus`.`level`
FROM `content_menus`
RIGHT OUTER
JOIN `content_links`
ON `content_links`.`lid` = IN(`content_menus`.`links`)
WHERE `content_links`.`active` = '1'
AND `content_links`.`level` <= '4'
AND `content_menus`.`active` = '1'
AND `content_menus`.`level` <= '4'
AND `content_menus`.`name` = `index`;
The above query resulted in an error. I’m still trying to get to grips with different types of MySQL join, I’m also not sure if I can even use IN() with the join.
learn outer joins later (you’ll be using LEFT OUTER JOIN exclusively, never RIGHT OUTER JOIN)
no, you can’t
what you have is a bad design which will forever give you very poor performance – your best bet is to redesign the tables, removing the links column which has multiple values in it, and adding a relationship table which allows you to associate any link with any menu
in the meantime, try this –
SELECT content_links.text
, content_links.url
, content_links.level
, content_menus.links
, content_menus.skeleton
, content_menus.level
FROM content_menus
INNER
JOIN content_links
ON [COLOR="Blue"]FIND_IN_SET(content_links.lid,content_menus.links) > 0[/COLOR]
AND content_links.active = 1
AND content_links.level <= 4
WHERE content_menus.active = 1
AND content_menus.level <= 4
AND content_menus.name = 'index';
Okay, I was sticking to inner joins until I had a query where the join content was NULL and that’s when I used a LEFT OUTER JOIN.
The above query works but if you’re saying it will slow performance then I’ll take your word for it and change the table design.
Okay as I’ve only just started this part then I have the option of redesigning the tables. I assume you mean something along the lines of - content_links
lid
text
url
level
active
content_menus
mid
text
level
active
_relationships_menus
rel_id
lid
mid
parent (to replace skeleton - allows for tiered menus)
Considering the only information I have is the menu name, eg “index” what would the query be with this table design?
Thanks so much for your help again r937, you have helped me start to understand more advanced MySQL since I last posted.
So the idea of parent is so you can have drop down menus, so if the parent is set to “0” then it’s considered a parent link, if it’s set to “>0” then it’s a child of that id. The thing is no link will always be a parent or always be a child, it depends on the menu. That’s why I thought it needed a relationship id so you could reference it in the parent column.
no, i would use a hierarchy table for the menus, where each row has a parent_id that refers to its parent menu (or else NULL, signifying a menu at the very top level)