I worked out from looking at the tests/source:
scope = with(:ranked_replies,
Reply
.select_append{rank{}.over(:partition=>:message_id,
:order=>created_at.desc).as(:rnk)}
.from_self(:alias=>:r)
.join(:emails, {Sequel.qualify(:emails, :message_id) =>
Sequel.qualify(:r, :message_id)})
.select(:email_id, :r__message_id, :rnk)
).with(:count_of_replies,
db[:ranked_replies]
.group_and_count(:email_id)
)
On Friday, May 30, 2014 10:52:11 AM UTC+1, [email protected] wrote:
>
> Hi,
>
> I have a couple more questions:
>
> If I have this code:
>
> scope = with(:ranked_replies,
> Reply.
> select_append{rank{}.over(:partition=>:message_id,
> :order=>created_at.desc)}.as(:rnk)
> .join(:emails, {Sequel.qualify(:emails, :message_id) =>
> Sequel.qualify(:ranked_replies, :message_id)})
> .exclude(:rnk=>1)
> .select(:email_id, Sequel.qualify(:ranked_replies, :message_id),
> :rnk)
> )
>
> I get the following exception:
>
> undefined method `join' for #<Sequel::SQL::AliasedExpression
>
> How do I continue joining the query after aliasing the .as(:rnk) column?
>
> And also does sequel support multiple selects on a CTE, for example:
>
> WITH ranked_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
> ), count_of_replies AS
> (
> SELECT email_Id, COUNT(*) AS "email_count" FROM ranked_replies GROUP BY
> email_id
> )
>
> Thanks again
>
>
> On Friday, May 30, 2014 8:10:27 AM UTC+1, Jeremy Evans wrote:
>>
>> 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.
