[thesite] Tip Harvest: Prev / Next Tip

[rudy]

doh!

let's see if it works when the unnecessary sub-sub-query is collapsed

  SELECT tip_id, messageid, tip_date, tip_type_name, author_id, who,
         attrib_type, attrib_author, tip_body
       , (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 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.

i can see how the function call is messed up by the table name qualifier

we're fux0red on both the tip type and author name, though, without doing
a join inside each of the subselects...

... but it would be only one join, not two like the outer query


shit, let me think about it


ooops, pardon me, this is a public list

i meant to say "shoot, let me think about it"


we also have to handle tips that don't have a type or author, right?


gotta go make dinner for the kids

back tonight, perhaps late