I have the following models
class Courier < ActiveRecord::Base
has_many :coverages
end
class Coverage < ActiveRecord::Base
belongs_to :courier
belongs_to :country_code
end
class CountryCode < ActiveRecord::Base
end
and then i have the following query:
# could i translate this into cleaner arel?
result = Courier.find_by_sql(<<SQL
select * from
(
select cc.*, cv.rate from couriers cc, coverages cv
where cv.country_code_id=#{country.id} and cv.courier_id=cc.id
union
select cc.*, cv.rate from couriers cc, coverages cv
where cv.country_code_id is null
and cv.courier_id=cc.id
and cv.courier_id not in (select courier_id from coverages where country_code_id=#{country.id})
) as foo order by rate asc
SQL
)
In short: i am looking for all couriers that have a coverage for the given country-code or a coverage with an empty country-code instead (the fallback).
The query works, but i am wondering if there are any better ways to write it?