Author |
Topic |
filf
Yak Posting Veteran
67 Posts |
Posted - 2006-08-02 : 08:51:36
|
Hello.I have created two tables T1: contains record data for galleries.contains: [int_gallery_id, int_display_order, misc fields]T21: contains xref data for these galleries linking a child to a parent.contains: [int_gallery_id, int_parent_id, int_depth]Each gallery relates to a folder in a filesystem which are nested based on parent child relationships. I have only have two depths of folders currently but would possibly like to extend this to be flexible. A user can change the display order of the parents and then subsequently the children.My problem is the correct ORDER BY sql to order the galleries on select - based on display_order of the parent, then the display_order of the children, then the display_order of the next parent etc etc. I hope I am making sense - my xref table funnily enough looks much like the table in this example without the lineage.http://www.sqlteam.com/item.asp?ItemID=8866Thanks in advance. |
|
filf
Yak Posting Veteran
67 Posts |
Posted - 2006-08-02 : 11:57:01
|
Okay - I have not been very clear - I have been reading the trees and hierarchies articles but what I am trying to do is slightly different.I have two tables - gallery and gallery_xrefand a select which is joining them.SELECT t1.int_gallery_id , t2.int_parent_gallery_id, t1.int_display_order, t2.int_gallery_depthFROM gallery AS t1 INNER JOIN gallery_xref t2 ON t1.int_gallery_id = t2.int_gallery_idORDER BY ??????The data is being displayed as follows[t1.int_gallery_id], [t2.int_parent_gallery_id], [t1.int_display_order], [t2.int_gallery_depth]1, 1, 1, 12, 1, 1, 23, 1, 2, 24, 4, 2, 15, 5, 3, 16, 5, 1, 2NOTE: if a gallery is level 1 then the parent id is itself. This was an attempt at fixing the issue simply with order by.My problem is how to create the sql to order the returned rows based on t1.int_display_order as specified by a user. So if in the sample data they wanted gallery 5 to be displayed first then the user would be displayed first gallery5 then all of gallery 5's children, then the next level 1 gallery as specified by int_display_order.I am sure I have over complicated this and there is a simple solution staring me in the face - any help will be greatly appreciated.Thanks for looking. |
|
|
filf
Yak Posting Veteran
67 Posts |
Posted - 2006-08-02 : 13:04:14
|
Okay here is an angle which needs more work.I made a temp table and copied some data across into the following fields.[int_gallery_id], [int_portfolio_id], [int_parent_gallery_id], [str_gallery_name], [int_display_order], [int_child_display_order]65, 3, 65, [Parent 1], 1, 068, 3, 65, [child 1-2], 1, 166, 3, 65, [child 1-1], 1, 269, 3, 69, [Parent 3], 2, 071, 3, 69, [child 3-1], 2, 172, 3, 69, [child 3-2], 2, 270, 3, 70, [parent 4], 3, 067, 3, 67, [parent 2], 4, 073, 3, 67, [child 2-1], 4, 1I will look further into my xref table and add an additional field [parent_display_order] which can then be the field to order my results by in conjunction with the original display order.I will TEST and post my results - this should then work for all depths which are not displayed here. |
|
|
filf
Yak Posting Veteran
67 Posts |
Posted - 2006-08-06 : 07:31:11
|
Thought I should do my bit for the dev community and complete posts - My working solution is based on what the article mentioned above was talking about - the final table def with example data is as follows:[int_node] [int_parent_node] [int_gallery_id] [int_portfolio_i] [int_gallery_depth]100, 100, 6, 1, 1101, 101, 8, 1, 1102, 101, 9, 1, 2103, 103, 11, 1, 1The key to getting the data out in the order specified by the user is the query which is as follows:SELECT t1.int_gallery_id, t2.int_node, t1.str_gallery_name, t1.str_gallery_folder, t1.int_total_images, t1.int_max_images, t1.int_display_order as int_display_order, t1.bln_displayed as bln_displayed, t2.int_gallery_depth, t3.int_gallery_id AS int_parent_gallery_id, t4.int_display_order AS int_parent_display_order, FROM gallery t1 INNER JOIN gallery_xref t2 ON t1.int_gallery_id = t2.int_gallery_id INNER JOIN gallery_xref t3 ON t3.int_node = t2.int_parent_node INNER JOIN gallery t4 ON t4.int_gallery_id = t3.int_gallery_id WHERE t1.int_portfolio_id = 3 AND t1.bln_displayed = 1 ORDER BY t4.int_display_order, int_parent_gallery_id, int_gallery_depth, int_display_order';What this enables is the functionality I was after - for results to be ordered by parent order - but if a parent has children its children are displayed based on their order, then the next parent is displayed and any subsequent children - etc etc.I have not tried any deeper than one level of children with this code but - after a couple of other tweaks I will experiment.Don't know why I recieved no suggestions or help on this topic - was I not clear enough in terms of the desired functional outcome? |
|
|
filf
Yak Posting Veteran
67 Posts |
Posted - 2006-08-06 : 14:27:41
|
BAH - does not work on deeper levels of ordering - back to the drawing board - looking more closely at lineage now. |
|
|
|
|
|