I need a less fragile way of writing this sql statement for ActiveRecord/Ruby right now i have :
sql = "SELECT * FROM (SELECT id FROM collection_districts WHERE
district_id in (#{params[:material][:content_consumer]}, #{id}) group
by colleciton_districts.id) tr having count(*)=2;"
cd = CollectionDistrict.find_by_sql(sql)
Ive been trying a few different ways and it's not producing what I need and have spent many hours wrestling with it. Hoping there is a RoR guru who can help. Thanks!
I'm having some trouble grasping the purpose of this query (or if it's even syntactically valid). So I'll leave you with some building blocks you can (hopefully) use to construct it.
Most of them are based on Arel that lies under the hood of ActiveRecord. All follow the format:
Block
Use cases
general form (with meaningfully-named @variables-placeholders) example (with identifiers originating from the magic black box) useful tricks regarding this block {0+}Some extra information that gives some more insight on "what it is"
Since it's Arel, you'll need some understanding of SQL syntax in order to imagine an AST of your query. Some commonly used pieces can be expressed by ActiveRecord itself, but not all.
FROM (subquery)For when you need to query not the table, but a set of rows generated by a subquery. You have to be careful though, as ActiveRecord will instantiate the records from the model you've started the query with (unless there's an association involved) and build conditions based on that model's table name. See "tricks" part.
@model.from(@subquery, @alias)
User.from(User.where(something: 42), 'super_users')
# A neat trick: query on a subset aliased to the same name as the table
# ActiveRecord's conditions will still work, but on a subset
# Known to break on some database servers: test before using
User.from(User.all, User.table_name)
# Placing where-conditions on aliased table: use a hash of conditions
User.from(User.where(something: 42), 'su').where(super_users: {field: value})
Be cautious though: this allows you to produce bizarre relations that fetch data from one table, but are instantiated like sets of records from another one. Make sure your code is clear enough if you'll be using such effects intentionally.
If you're fetching from more than one source, you'll have to use a slightly different form, Arel-style explicit aliasing:
.from([table.as('alias'), table2.as('alias2'), ...])
These can be used in a variety of ways: using in various SQL clauses (such as group), performing an implicit INNER JOIN (in form of a.id = b.a_id)... Specifically, group method can accept such a value.
Model.arel_table[:field]
District.arel_table[:id]
# writing .arel_table every time is tiresome and not DRY
# for referencing table columns more than once this makes sense:
districts = District.arel_table
districts[:id]
*)Well, just because you have it and you need it. It is also usable in a variety of ways: for instance, using it in a qualified column identifier (like districts[Arel.star]) gives a wildcard on columns (like districts.*). The important part though, is that accepts chaining aggregate functions on top, like Arel.star.count (which is exactly COUNT(*)).
Arel.star # => "*" Well, there's nothing more to it
Arel.star.count # => "COUNT(*)"
It's just a node of Arel's SQL AST. It can be used anywhere where it's valid in equivalent SQL. In Rails' select method, for instance.
IN-conditionThis one's easy for when you want to constraint a field to have one of the given values. This one is as easy as passing in an array of possible values into where:
Model.where(field: [value1, value2])
User.where(id: [1, 2]) # SELECT users.* WHERE users.id IN (1, 2)
This is where the syntax gets ugly. Well, sometimes you have to deal with it. For instance, having-clause may force you to do this. These conditions can also be fed into where.
expression.operator(expression)
a[:id].eq(b[:a_id]) # a.id = b.a_id
There's quite a bunch of operators available, they more-or-less map to English terms used to describe them: lt for "less than", lteq for "less than or equal", matches for LIKE or ILIKE, in for, well, IN... Here's a (possibly incomplete) list (found in Arel::Predications module):
does_not_match eq_all gt_any in lt_all lteq_any not_eq not_in_all
does_not_match_all eq_any gteq in_all lt_any matches not_eq_all not_in_any
does_not_match_any gt gteq_all in_any lteq matches_all not_eq_any
eq gt_all gteq_any lt lteq_all matches_any not_in
ActiveRecord doesn't do anything fantastic to support nested queries, but you can pull your raw SQL into Arel (which ActiveRecord sits on top of) using techniques from this question:
inner_query = YourModel.where(:stuff => "foo")
outer_query = YourModel.scoped # cheating, need an ActiveRelation
outer_query = outer_query.from(Arel.sql("(#{inner_query.to_sql}) as results")).
select("*")
If you don't mind making two queries, you can do it in a very ActiveRecordy way by first taking the IDs, then looking up the full objects.
district_ids = params[:material][:content_consumer].split(',') + [id]
ids = CollectionDistrict.where(district_id: district_ids)
.group(:id)
.having('count(*) = 2')
.pluck(:id)
CollectionDistrict.find(ids)
And finally if the list of districts is reasonably sized, you could load them up and do it in Ruby with some nice functional chaining.
CollectionDistrict.where(district_id: district_ids)
.group_by(&:id)
.select { |id, collection_districts| collection_districts.size == 2}
.map(&:last)
.flatten
.uniq
These will all have different performance characteristics, so if that's a concern you should do some benchmarking. If you're not worried about performance yet, I like the readability of the latter two options.
once again, scopes can reduce the complexity of your query
class ColletionDistrict < ActiveRecord::Base
scope :districs, ->(*d) {
where(district_id: d.flatten.compact.uniq)
}
end
CollectionDistrict.districts(params[:material][:content_consumer]).select(:id).group(:id).having('count(id) > 2')
