1

For a project using Rails 4.2.5 and Postgres 9.3, I am trying to implement some query similar to "Query #3" suggested in PostgreSQL - fetch the row which has the Max value for a column

-- use Window Functions
-- performs a SINGLE scan of the table
SELECT DISTINCT ON (usr_id)
  last_value(time_stamp) OVER wnd,
  last_value(lives_remaining) OVER wnd,
  usr_id,
  last_value(trans_id) OVER wnd
 FROM lives
 WINDOW wnd AS (
   PARTITION BY usr_id ORDER BY time_stamp, trans_id
   ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
 );

Beside model.find_by_sql and hacky model.joins, is it possible to add the WINDOW clause? I find a hack using joins to specify both WHERE and WINDOW clauses, but the readability is out of window (pun intended :). I also like to add a WITH clause before the SELECT, and then the joins hack won't work at all.

Any suggestion is greatly appreciated.

Community
  • 1
  • 1
builder
  • 251
  • 4
  • 11
  • I go straight to `find_by_sql` for anything non-trivial. ORMs tend to want to think of queries in pieces but reality doesn't quite work that way. You might be able to do something by working with AREL but that's hardly a solution if you're worried about readability. Any particular reason that you don't want to `find_by_sql`? – mu is too short Jan 12 '16 at 00:26
  • I tried to stay within Rails way. Thanks for confirming the fact that the project reality has graduated from ORMs for me :). `find_by_sql` is in. – builder Jan 12 '16 at 01:42
  • Sometimes the "Rails way" is dumb, sometimes it is shortsighted, sometimes it is a straight jacket. I'm sure the true believers will disagree with me on this of course. – mu is too short Jan 12 '16 at 02:41

0 Answers0