thank you for taking the te to do that On Friday, May 30, 2014, Jeremy Evans <[email protected]> wrote:
> On Thursday, May 29, 2014 8:13:52 AM UTC-7, [email protected] > <javascript:_e(%7B%7D,'cvml','[email protected]');> wrote: >> >> I have the following query: >> >> WITH old_replies AS >> ( >> SELECT email_id, r.message_id, rnk >> FROM >> (SELECT RANK() OVER(PARTITION BY message_id ORDER BY created_at DESC) AS >> rnk, * >> FROM replies ) AS r >> INNER JOIN emails e >> ON r.message_id = e.message_id >> WHERE rnk != 1 >> ) >> >> SELECT * FROM emails >> WHERE message_Id NOT IN (SELECT message_id FROM old_replies) AND id NOT IN >> (SELECT email_id FROM old_repies >> >> Is it in any way possible to create such a CTE as above and then use it to >> filter the results of an existing Sequel model class. >> >> > Assuming you have a model named Email: > > Email.with(:old_replies, > DB[:replies]. > select_append{rank{}.over(:partition=>:message_id, > :order=>created_at.desc)}.as(:rnk). > from_self(:alias=>:r). > join(:emails___e, [:message_id]). > exclude(:rnk=>1). > select(:email_id, :r__message_id, :rnk)). > exclude(:message=>DB[:old_replies].select(:message_id)). > exclude(:id=>DB[:old_replies].select(:email_id)) > > Something like that should work, but I didn't actually test it (I'm in the > airport waiting for my flight to Kiev to attend RubyC). > > Thanks, > Jeremy > > -- > You received this message because you are subscribed to a topic in the > Google Groups "sequel-talk" group. > To unsubscribe from this topic, visit > https://groups.google.com/d/topic/sequel-talk/E2NCXLvsfxQ/unsubscribe. > To unsubscribe from this group and all its topics, send an email to > [email protected] > <javascript:_e(%7B%7D,'cvml','sequel-talk%[email protected]');> > . > To post to this group, send email to [email protected] > <javascript:_e(%7B%7D,'cvml','[email protected]');>. > Visit this group at http://groups.google.com/group/sequel-talk. > For more options, visit https://groups.google.com/d/optout. > -- Cheers Paul Cowan Cutting-Edge Solutions (Scotland) blog: http://thesoftwaresimpleton.com/ website: http://www.cuttingedgesolutionsscotland.com/ -- You received this message because you are subscribed to the Google Groups "sequel-talk" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/sequel-talk. For more options, visit https://groups.google.com/d/optout.
