0

A simplified example:

I have a SQL table called things. Things by themselves have an id and a name. Things are part of a tree, e.g. a thing can have a parent; Exactly how to this is stored is not important, important is however that it is possible to obtain a list of thing ids from the root node to the current thing.

I have another table, called properties. A property has a thing_id column, a name column and a value column.

I now want, for the current thing, to obtain all properties, ordered by thing_id, in order of the paths from root thing to current thing.

e.g., if the current thing is nested like this: Root(1) > Vehicle(4) > Car(2) > Hybrid(3), I would want the list of properties be returned with the properties that have a thing_id==1 first, followed by the ones with thing_id == 4, then thing_id==2 and finally thing_id==3.

How can this be done using SQL? (without using N+1 selects)

Qqwy
  • 5,214
  • 5
  • 42
  • 83
  • I am using Rails, and I would like a solution to at least work on both SQLite and MySQL2. – Qqwy Feb 14 '16 at 15:25

1 Answers1

1

In SQL this can be achieved with use of recursive query. Here is an example

DECLARE @item as varchar(10) 

with CTE (main_part, sub_part, NestingLevel) 
as    
( 
                select main_part, sub_part, 1 from tblParts 
                        where main_part = @item 

                union all 

                select tblParts.main_part, tblParts.sub_part, (NestingLevel + 1) from tblParts 
                inner join CTE on tblParts.main_part = CTE.sub_part 
) 

select * from CTE

In order to address this in MySQL you can try temporary table approach. Here is a good example of it: How to do the Recursive SELECT query in MySQL?

Community
  • 1
  • 1
IgorM
  • 1,348
  • 1
  • 12
  • 28
  • @JoachimIsaksson, I haven't try it on MySQL, but since both MS SQL and MySQL are RDBMS I think this approach is viable for MySQL as well. – IgorM Feb 14 '16 at 15:29
  • @JoachimIsaksson then you will probably need to go with stored procedure approach http://stackoverflow.com/questions/16513418/how-to-do-the-recursive-select-query-in-mysql – IgorM Feb 14 '16 at 15:34
  • Could you clarify your answer a little? I have some trouble figuring out what is going on. – Qqwy Feb 15 '16 at 23:08
  • @Qqwy, I checked MySQL docs and found that MySQL does not support WITH command. So it definitely won't work with MySQL. I believe this can solved with temporary table. See revised answer – IgorM Feb 16 '16 at 14:26