CODEWITHSUNDEEP
javasql
Phillip Schmidt
Phillip Schmidt

Reputation: 8818

Need a recommendation for how to organize SQL result

I'm building a multi-tiered menu. So, for example, each main menu item might have 5-10 submenu items, and each of those might have 5-10 third level items. The menu items, no matter what tier they are from, are kept in a single table. The query I (currently) use to pull the data out looks like this:

SELECT *
FROM menu_items AS m1
LEFT JOIN (SELECT name AS slName, id AS slId, parent_id AS slPid FROM menu_items)
m2 ON m1.id = m2.slPid
LEFT JOIN (SELECT name AS tlName, id AS tlId, parent_id AS tlPid FROM menu_items)
m3 ON m2.slId = m3.tlPid
WHERE parent_id = 0

The result of this query visually looks like the menu I want, but now I'm thinking I may want to try and restructure so that I can stick it cleanly in an object, rather that just leaving it as a DataTable or whatever.

Here's a sample result of the query above:

For reference, sl means second level, tl means third level, etc.

{id=1, name=Industry, level=1, parent_id=0, slname=Equipment & Machinery, slid=4, slpid=1, tlname=null, tlid=null, tlpid=null}
{id=1, name=Industry, level=1, parent_id=0, slname=Food, slid=5, slpid=1, tlname=null, tlid=null, tlpid=null}
{id=1, name=Industry, level=1, parent_id=0, slname=Fabricated Metal Products, slid=8, slpid=1, tlname=null, tlid=null, tlpid=null}
{id=1, name=Industry, level=1, parent_id=0, slname=Automotive, slid=9, slpid=1, tlname=null, tlid=null, tlpid=null}
{id=1, name=Industry, level=1, parent_id=0, slname=Chemicals and Allied Products, slid=10, slpid=1, tlname=null, tlid=null, tlpid=null}
{id=2, name=Application, level=1, parent_id=0, slname=App Stuff 1, slid=11, slpid=2, tlname=null, tlid=null, tlpid=null}
{id=2, name=Application, level=1, parent_id=0, slname=App Stuff 2, slid=12, slpid=2, tlname=null, tlid=null, tlpid=null}
{id=2, name=Application, level=1, parent_id=0, slname=App Stuff 3, slid=13, slpid=2, tlname=null, tlid=null, tlpid=null}
{id=2, name=Application, level=1, parent_id=0, slname=App Stuff 4, slid=14, slpid=2, tlname=null, tlid=null, tlpid=null}
{id=3, name=Product, level=1, parent_id=0, slname=Prod Stuff 1, slid=15, slpid=3, tlname=null, tlid=null, tlpid=null}
{id=3, name=Product, level=1, parent_id=0, slname=Prod Stuff 2, slid=16, slpid=3, tlname=null, tlid=null, tlpid=null}
{id=3, name=Product, level=1, parent_id=0, slname=Prod Stuff 3, slid=17, slpid=3, tlname=null, tlid=null, tlpid=null}

So the problem is now I end up with a bunch of rows which aren't really very nicely formatted to create a Menu object out of. I'm just looking for recommendations on how to restructure. Maybe change up the sql query so get a better base set of data or maybe an idea on how to format my current SQL.

Upvotes: 1

Views: 147

Answers (1)

Andy Poquette
Andy Poquette

Reputation: 410

You could do something like:

ID   NAME            PARENT 
 1   Industry        1(or null. either self referencing or null = top level)
 2   Food            1
 3   Burgers         2
 4   Application     4
 5   Software        4
 6   Product         6

In your database. Essentially, the Name loops back and your code can set it up so that it walks up the chain until the parent column is either null or references the ID of that row.

So this menu would end up looking like:

Industry
  Food
    Burgers
Application
  Software
Product

Upvotes: 1

Related Questions