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.
