[thesite] Tip Harvest: Prev / Next Tip

[rudy]

>Yeah.  What I meant is that you are still doing extra select
>statements to get the data.  Just instead of grabbing the entire
>ordered list, you are grabbing the next and prev tip_id's.

well, yes, except you wouldn't be getting "the entire ordered list"
from the database at the same time as the tip details for the one
tip you want to display

>Actually, no.  I'm getting tips for the past week and joining against
>the users table and the tip_types table.  

okay, not month but week -- still, that's what i've been calling
the "tip details" query (the outer joins are irrelevant)

>  SELECT tip_id, tip_date, tip_type_name, author_id, who,
>         attrib_type, attrib_author
>    FROM thetips, users, tip_types
>   WHERE tip_date BETWEEN to_date('#startdate#', 'YYYY-MM-DD') AND
>                          to_date('#enddate#', 'YYYY-MM-DD')
>     AND author_id = userid (+)
>     AND thetips.tip_type_id = tip_types.tip_type_id (+)
>ORDER BY #orderby# #orderdir#

that there is the summary query

>Yes.  It also sorts ASC or DESC based on which way you sorted on the
>field previously.  (I needed to add those little up/down arrow icons
>to make that more obvious.)

i missed that

that's pretty cool

>> (b.t.w. what's the 410 for?)
>
>Simply random number used for the example.  It would represent another
>tip_id in the list.  Assume a potentially sparse table where not all
>tip_id's refer to an actual tip, e.g., tip was deleted after it was
>loaded.

nah, you don't need that

your code should be able to handle a week with no tips at all

>> application session variable?   cached query?
>
>Ummmm... those are beyond me at the moment. 

my point exactly


>Can you re-write your query based on the above query, i.e., taking the
>joins into account?

no, that query stays absolutely the same

however you are picking up the prev/next when somebody clicks
on an individual tip, just keep doing that

show me the detiul query, that's where the subqueries go


rudelicious