| Author |
Topic |
|
jackstow
Posting Yak Master
160 Posts |
 Posted - 2002-06-20 : 04:48:08
|
I want a user to be able to change the order of articles displayed on their site. They are all held in one table. Some articles will be active, some will be archived, some will be scheduled to show on a particular date, some will be relevant to particular sections of the site only. So how to order them. An related 'ordering' table would be OK but what about when the user wants to place a record between two other records (not 10 or 11 but 10.5?) and when a record is deleted. Is there anyway to insert a record between two other records? What is the best way to implement the user defined ordering? Any ideas gratefully received!p.s. anyone got any ideas about my webtask problem - http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=16812  |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-06-20 : 08:32:53
|
| The physical implementation is going to depend greatly on your existing schema, so to say ‘another table’ or ‘a column in a table’ is difficult. However, it is clear as day that if you want to order a rowset, you must have something to order by. Carrying a simple (tiny/small depending) integer column seems like the only way to fly. Then you need to implement some logic to reorder on insert/delete.<O> |
 |
|
|
jackstow
Posting Yak Master
160 Posts |
Posted - 2002-06-20 : 09:52:51
|
quote:
Then you need to implement some logic to reorder on insert/delete.
- well I had something that was doing that using a trigger - http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=12520 - but I was never totally happy with it (and it wouldn't be any use if you happen to be using a database that doesn't support triggers) - but I can't see any other way at the moment. |
|
|
|
colinm
Yak Posting Veteran
62 Posts |
Posted - 2002-06-20 : 10:38:52
|
| Perhaps some sort of linked list, would help with the inserts "between" records. |
|
|
|
M.E.
Aged Yak Warrior
539 Posts |
Posted - 2002-06-20 : 11:03:16
|
| Just an Idea...Create a second table that has 2 columns, one as a key that links to this article table and the other a 'order' number. This order number could easily be altered by you whenever you see fit.(I'll just call your articles a,b,c,d,e,f,....)articleID ordernumbera,100b,300c,200d,500e,600f,400Lets just say your article table is called articles with 'articleID' as the PKselect art.* from article art inner join ordertable ord on ord.articleID = art.ArticleID order by ordernumber.If you wish to insert another article that goes between article D and E, you would simply assign an ordernumber of 450 to it.If some articles are only to be shown on certaian days, make more columns on the ordertable called date_start and date_end. If you want include a 'status' column as well. select art.* from article art inner join ordertable ord on ord.articleID = art.ArticleID Where archive = 'current' and getdate() between ord.date_start and ord.date_endorder by ordernumberTheres probably alternative methods, but this will give you alot of control. For a article that never expires based on date... Just say date_start = jan 1 2000 and date end = jan 1 2049 or something along those lines.edit... fixing some thingsAnd sorry, no clue about you webtask problem-----------------------Take my advice, I dare yaEdited by - M.e. on 06/20/2002 11:07:37 |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-06-20 : 12:09:19
|
quote:
Perhaps some sort of linked list, would help with the inserts "between" records.
I'm curious. Assuming you could find a good set based method for keeping your linked list intact (everthing I learned about linked lists in Intro to C 101 was very iterative), how would you order by the linked list in a select statement?<O> |
|
|
|
jackstow
Posting Yak Master
160 Posts |
Posted - 2002-06-20 : 12:39:53
|
| Thanks for the ideas. M.E. - I can see how that would work, but wouldn't it mean you would have 300 chances to insert something between c (order number 200) and d (order number 500) - what would happen if you needed 301. I guess you could make the numbers millions apart rather than hundreds.colinm - I'm not familiar with 'linked lists'. I presume they have something like each record holding it's positional information (i.e. I am before this record but after this one) - but like Page47 says, how would you order by in a select?It would be really nice to work out a cool solution for this as it's the kind of thing clients ask for and seems so to them! |
|
|
|
M.E.
Aged Yak Warrior
539 Posts |
Posted - 2002-06-20 : 13:03:06
|
quote:
M.E. - I can see how that would work, but wouldn't it mean you would have 300 chances to insert something between c (order number 200) and d (order number 500) - what would happen if you needed 301. I guess you could make the numbers millions apart rather than hundreds.
hundreds was just some arbitrary number I choose. Pretty much put in whatever you want. You could make it decimal instead.... infinate number of decimals between 1 and 2 no (well, effectively I guess... I got the strange feeling someones gonna jump in here and give some SQL limit for decimals  )?-----------------------Take my advice, I dare ya |
|
|
|
colinm
Yak Posting Veteran
62 Posts |
Posted - 2002-06-21 : 06:20:14
|
quote:
I'm curious. Assuming you could find a good set based method for keeping your linked list intact (everthing I learned about linked lists in Intro to C 101 was very iterative), how would you order by the linked list in a select statement?
erm... cursors!!I'd just read that SQL Server uses linked list for hash joins. So the idea popped into my head. Not sure how it would work in practice. I think the internal hash buckets may use iterative methods, as you say. (they would probably have a relatively small depth, though) |
|
|
|
|
The order of things..