On Tuesday, March 31, 2015 at 11:24:58 AM UTC-7, Andrew Burleson wrote:
>
> 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".
>
In general, it's a good idea to have the SQL you want in mind. In this
case, you basically want to SELECT services.*, count(*) GROUP BY
services.*. However, that's not valid SQL (at least not SQL-92), since you
can't group by services.*. So you need to select and group by all columns
in services (services.id, ...). Something like:
dataset = community.
offerings_dataset.
join(:service_orders, :offering_id => :id).
select_group(*Service.columns.map{|c| Sequel.qualify(:services, c)})
select_append{count{}.*}.
reverse(:count)
You can't use a subquery in the WHERE clause to do what you want, because a
subquery in WHERE can't add columns to SELECT. You can use a correlated
subquery in SELECT though, so this should be an alternative:
dataset = community.
offerings_dataset.
select_append(DB[:service_orders].where(:offering_id=>:services__id).select{count{}.*}.as(:count)).
reverse(:count)
You may want to benchmark both approaches and see which performs better on
your database.
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.
