On Thursday, May 29, 2014 8:13:52 AM UTC-7, [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 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.
