Many objects under objects

Question

I have a doubt about relationship and how to show it.

I have a channel which can have many channels, which can have more channels, which can have more channels..........

How can I relate them?

I thought that each channel can have a field that tells which channel it's related to, like:

id name related_channel
2  example1 null
3  example2 2
4  example3 2
5  example4 4

what do you think about it?

After relating them, how can show all the channels that are under a channel?

Thanks in advance!

score 1 · Answer 1 · answered Aug 25 '15 at 02:27

1

this approach is good, you just need to set a "parent_id" or "related_channel" in your case.

after that, if you want to get all childrens of a channel, just use SQL Query like this :

SELECT * FROM channel where related_channel=x

answered Aug 25 '15 at 02:27

segfault

95
7

NIce! Thanks man. The only problem in this query is that if I have this situation: channel1 > channel2 > channel3 When I use this query: SELECT * FROM channel where related_channel = 1 the channel3 wouldn't appear, because his related_channel is 2, but he is also child of channel1 – Morris Aug 25 '15 at 02:53

score 1 · Accepted Answer · edited May 23 '17 at 10:32

1

I am a big fan of the self-joins, especially with stored procs to do near recursive (but not) operations on them. I wrote up an example in this link. Let any of us know if you need any help like that. Good luck.

edited May 23 '17 at 10:32

Community

1
1

answered Aug 25 '15 at 02:29

Drew

24,851
10
43
78

Thanks Drew! That's exactly what I needed! – Morris Aug 25 '15 at 03:06
self-joins are awesome for re-hanging a whole sub-hierarchy, eh ? Glad you found some use of the link. – Drew Aug 25 '15 at 03:08

Many objects under objects

2 Answers2