Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

I'm using DBD:Pg to connect to a Postgres server. This isn't a Perl question per se, but perhaps someone will be kind enough to answer it anyhow.

I have a table of object records (lb_objs) that are frequently accessed. I keep track of these accesses in another table, lb_trans. lb_trans has columns for the primary key of the object acted on (lb_trans.obj_id), a timestamp (lb_trans.instant), and an access type (lb_trans.action). I'm trying to craft a way to select a set of the objects (e.g. lb_objs.attrib = 1), and include in each returned row the transaction type from the most recent transaction on the object specified by that row. With two queries it's simple: 

SELECT id, attrib
FROM lb_objs
WHERE attrib = 1

[download]
followed by, for each result, 
SELECT action
FROM lb_trans
WHERE obj_id = ?
ORDER BY instant DESC
LIMIT 1

[download]
where ? is a placeholder set to lb_objs.id for the current lb_objs row.

However, I'd prefer to do it in one select, since I could reuse that for other similar queries more directly. I've though about using various joins and sub-selects, but I can't find a way to do it. Can anyone see a way to do this with just one select?

Replies are listed 'Best First'.
(Ovid) Re: (OT) Postgres Query
by Ovid (Cardinal) on May 12, 2001 at 02:08 UTC

    Hmm... an anonymous monk who not only uses <code> tags, but also crafts a well-written question and prefaces the title with OT. I'm willing to bet it's someone trying to avoid the dreaded --

    :-)

    Not that I mind. Just thought I'd engage in some idle speculation. I still would love to see an OT section - that couldn't be voted on.

    Cheers,
    Ovid

    Update: Ovid strikes again! The monk in question sent me a /msg a while later confessing to /h(is|er)/ crime. And no, I won't tell you who it is. :)

    Join the Perlmonks Setiathome Group or just click on the the link and check out our stats.

[reply]
Re: (OT) Postgres Query
by mr.nick (Chaplain) on May 12, 2001 at 02:30 UTC
    Untested: 
    select lb_objs.id,lb_objs.attrib,lb_trans.action from lb_objs,lb_trans
+ 
  where lb_objs.attrib = 1 and lb_objs.id = lb_trans.id 
  order by lb_trans.instant desc

    [download]

    Update: Hmm. Didn't understand all the requirements. I'm not sure how to fullfill it in that case.

[reply]
[d/l]
      Ah, but that returns all accesses, not just the most recent. So if one object was accessed twice since another was accessed once, iterating through the rows would return two rows for the first object before returning any for the second. The goal is to have one row per object, accompanied by info from the last transaction affecting that object.
[reply]
Re: (OT) Postgres Query
by thraxil (Prior) on May 12, 2001 at 20:18 UTC

    i can't think how to do it with your table schema in a single query, but i can at least offer a (admittedly kind of ugly) solution:

    you could add a last_action column to the lb_objs table which you update everytime you insert or update the row. you could probably also define a trigger to automatically keep the lb_trans table up to date (avoiding having to do 2 update/inserts each time).

    anders pearson // digital samurai
personal      // http://www.columbia.edu/~anders/  
weblog       // http://thraxil.dhs.org/
[reply]

Back to Seekers of Perl Wisdom