On Sunday, August 11, 2013 1:20:13 AM UTC-7, Tamir Duberstein wrote:
> Suppose my application contains the follow classes:
>
> class Song < Sequel::Model
>> one_to_many :listens
>> end
>>
>
>
>> class Listen < Sequel::Model
>> many_to_one :song
>> end
>
>
> And I'd like to get a list of songs sorted by how many times they were
> played in the last month. I believe the query should be:
>
>> SELECT `songs`.* FROM `songs` LEFT OUTER JOIN listens ON listens.song_id
>> = songs.id GROUP BY id ORDER BY SUM(listens.created_at > '2013-07-11
>> 08:14:36 UTC') DESC;
>
>
> What's the idiomatic way to generate this? The best I've been able to do
> is:
>
>> Song.eager_graph(:listens).group(:id).reverse_order{sum(listens__created_at
>> > 1.month.ago}
>
>
> That's pretty good, but I wonder if there's a way to do it without
> breaking out of the DSL with `sum()` (it's treated as arbitrary SQL -- I
> could write foo() and it would work). Any ideas? Thanks!
>
I'm not sure what you mean by "breaking out of the DSL". It's not treated
as arbitrary SQL, it's treated as an SQL function call. It happens that
function calls in SQL are not quoted, so the appearance in the generated
SQL is similar, but it's very different than actually breaking out of the
DSL and using arbitrary SQL via Sequel.lit.
For the SQL you want to generate, you probably want to add
.select_all(:songs). FWIW, your SQL query does not appear to be portable,
at least the part where you are calling sum with a boolean expression.
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.
