I've got an issue where I'd like to be able to use the result of
group_and_count in a subquery, but that doesn't work. I'm wondering if I'm
just doing it wrong, or if there's a different way to accomplish the same
goal.
Briefly, here's the relevant bit of my schema:
service one_to_many offerings
community one_to_many offerings
offerings one_to_many service_orders
So, in a given community, I'd like to return all the services available in
the community grouped and ordered by the number of service orders. This
query works like so:
dataset = community.offerings_dataset.join(:service_orders, :offering_id =>
:id).group_and_count(:service_id).order(:count).reverse
=> #<Sequel::Postgres::Dataset: "SELECT \"service_id\", count(*) AS
\"count\" FROM \"offerings\" INNER JOIN \"service_orders\" ON
(\"service_orders\".\"offering_id\" = \"offerings\".\"id\") WHERE
(\"offerings\".\"community_id\" = 242) GROUP BY \"service_id\" ORDER BY
\"count\" DESC">
dataset.all
=> [#<Offering @values={:service_id=>71, :count=>3}>,
#<Offering @values={:service_id=>7, :count=>2}>,
#<Offering @values={:service_id=>4, :count=>2}>,
#<Offering @values={:service_id=>65, :count=>1}>,
#<Offering @values={:service_id=>1, :count=>1}>]
Now, ideally what I'd prefer to do is return a dataset like
`Service.where(id: dataset)` This doesn't work, though, since the `select`
clause in that query has `service_id` and `count` in it, `Service.where(id:
dataset).all` errors with "UndefinedColumn: column 'count' does not exist".
I naively tried putting a select on the end of the query:
`dataset.select(:service_id)`, but that fails because it overrides the
original select which is doing group and count.
So, I can accomplish what I want by falling back to ruby at this point:
services_array = dataset.map(&:service)
or
services_dataset = Service.where(id: dataset.map(&:service_id))
These seem like sort of clunky workarounds, though. Is there a different
way to get to the intended dataset without enumerating through the ruby
objects as a final step?
Thanks!
Andrew
--
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.
