On Sunday, September 22, 2013 8:29:24 AM UTC-7, Robert Payne wrote:
> I'm attempting to convert a moderately complex query:
>
> SELECT a1.* FROM activities a1
> INNER JOIN activities_channelships cs ON cs.channel_id = a1.channel_id
> LEFT JOIN activities a2
> ON a1.channel_id = a2.channel_id AND a1.created_at < a2.created_at
> WHERE a2.id IS NULL
> AND cs.user_id = 1
> ORDER BY a1.created_at DESC;
>
> I have Sequel::Model setup for all of the tables, columns involved yet I
> get stumped pretty early on when trying to convert this over and looking at
> Sequels raw SQL output, here's what I have so far:
>
> Activity.join(ActivityChannelship, channel_id:
> :channel_id).left_join(Activity, channel_id: :channel_id).sql
>
> Problem being is the left_join emits another "activities" table select
> that conflicts with the first one. I'm not extremely robust in Sequel yet
> when moving to complex queries so I scoured the docs but wasn't able to
> find anything that may treat it more like the query above.
>
> Any help anyone may have in converting this query would be much
> appreciative.
>
Here's a pure dataset version. Converting it to use models is left as an
exercise for the reader:
DB[:activities___a1].
select_all(:a1).
join(:activities_channelships___cs, :channel_id=>:channel_id).
left_join(:activities___a2,
:channel_id=>:channel_id){Sequel.expr(:a1__created_at) < :a2__created_at}.
where(:a2__id=>nil, :cs__user_id=>1).
reverse_order(:a1__created_at)
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/groups/opt_out.
