[thesite] Tip Harvest: Prev / Next Tip

[Dean Mah]

rudy writes:

> >Interesting.  Still running a query against the database though...
> 
> what do you mean "still" running?  
> 
> when you run the query to get the tip details, i'm suggesting just 
> use that query to get the prev/next for display under that tip's details

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.


> but think about how you produced the summary list 
> 
> you ran a query for that month, right?
>
>    [  or else i guess i don't know where 
>       your data is coming from -- i just assumed 
>       that everything is in the tips table(s)...      ]

Actually, no.  I'm getting tips for the past week and joining against
the users table and the tip_types table.  Just wait when we allow tips
to be of multiple type!

Here's the query:

  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#


> and when you click on one of those column headings, 
> this attaches a query string to the url, orderby=xxxxx
> 
> i'm assuming this simply re-runs the query that produces the
> summary list, just using a different order by clause

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.)


> >   tip_detail.cfm?tip_id=3343&tip_list=3,3343,1,4423,410
>
> pass only prev/next?  that won't work, cause after the first page,
> how do you know what the next tip id is?

[ re-ordered context ]

> thus, your quest for "passing" the entire summary list

Exactly.


> (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.


> you could do it if they were stored in some persistent way in cold fusion
> 
> application session variable?   cached query?

Ummmm... those are beyond me at the moment.  I've only ever read
Seth's code and I don't think that he used app. sess. var. or cached
queries.


> we've had this discussion before, it's one of my favourite threads
> [but maybe only because it's so easy for me to forget things at my
> age...]

Don't get me started on that.... Damn, forgot the witty aphorism that
I was going write here.


> my suggestion lets you go from detail page to detail page following
> prev and next links without "passing" any of them

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


> you also need to know when you are sitting on the first or the last,
> so that you can "gray out" those links

That's fine.  The query comes back with null values or at least ""
that I check to remove the links as appropriate.  Unless you're
talking about some kind of cached query here.  In which case, I
gotcha.


Dean