[thesite] Tip Harvest: Prev / Next Tip

[Dean Mah]

rudy writes:

> 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

Right.


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

Except I am sorting on those fields.  It would probably make more
sense if I expanded the #orderby# variable.  It can be one of:
tip_date, upper(attrib_author), or tip_type_name.

Originally, the "Tip Name" column was showing the canonical tip name
from the tip_types table if provided and the attrib_tip if not.  As
well, the "Author" column was showing the matched member field from
the users table if found or the attrib_author if not.  Not sure how
you would work that into the query.


> nah, you don't need that
> 
> your code should be able to handle a week with no tips at all

Yeah, it does.  I was just trying to explain how you would get sparse
numbering like I used in the example.  If there are no tips in a week,
the link shouldn't even appear to display the summary.


> 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

This is what I tried:

  SELECT tip_id, messageid, tip_date, tip_type_name, author_id, who,
         attrib_type, attrib_author, tip_body
         (SELECT tip_id
            FROM thetips inner
           WHERE tip_date BETWEEN to_date('#startdate#', 'YYYYMMDD') AND
                                  to_date('#startdate#', 'YYYYMMDD') + 7
             AND tip_id = (SELECT max(tip_id)
                             FROM thetips
                            WHERE tip_date BETWEEN
                                  to_date('#startdate#', 'YYYYMMDD') AND
                                  to_date('#startdate#', 'YYYYMMDD') + 7
                              AND (#seqfield# < outer.#seqfield#
                                OR #seqfield# = outer.#seqfield#
                               AND tip_id < outer.tip_id)
                          )
         ) AS prev_id,
         (SELECT tip_id
            FROM thetips inner
           WHERE tip_date BETWEEN to_date('#startdate#', 'YYYYMMDD') AND
                                  to_date('#startdate#', 'YYYYMMDD') + 7
             AND tip_id = (SELECT min(tip_id)
                             FROM thetips
                            WHERE tip_date BETWEEN
                                  to_date('#startdate#', 'YYYYMMDD') AND
                                  to_date('#startdate#', 'YYYYMMDD') + 7
                              AND (#seqfield# > outer.#seqfield#
                                OR #seqfield# = outer.#seqfield#
                               AND tip_id > outer.tip_id)
                          )
         ) AS next_id
    FROM thetips outer, users u, tip_types tt
   WHERE tip_id = #url.d#
     AND author_id = userid (+)
     AND outer.tip_type_id = tt.tip_type_id (+)

If this query works, I will need to add some CF code to massage the
#seqfield# before using it so that I can refer to the tip_types table
and the upper function call.

Dean