I have two models - Property
& Photos
.
class Property < ActiveRecord::Base
has_many :photos, dependent: :destroy
end
class Photo < ActiveRecord::Base
belongs_to :property
end
All I want to do is create a scope that returns only the Properties that actually have photos (i.e. photos.count > 0
).
I have tried a million iterations of queries, and they don't work for one reason or another.
See some examples of what I have tried and the results:
[32] pry(main)> Property.includes(:photos).where('photos.count > 0').count
(4.1ms) SELECT COUNT(DISTINCT "properties"."id") FROM "properties" LEFT OUTER JOIN "photos" ON "photos"."property_id" = "properties"."id" WHERE (photos.count > 0)
ActiveRecord::StatementInvalid: PG::GroupingError: ERROR: aggregate functions are not allowed in WHERE
LINE 1: ..."photos"."property_id" = "properties"."id" WHERE (photos.cou...
^
: SELECT COUNT(DISTINCT "properties"."id") FROM "properties" LEFT OUTER JOIN "photos" ON "photos"."property_id" = "properties"."id" WHERE (photos.count > 0)
from /.rvm/gems/ruby-2.3.7@myapp/gems/activerecord-3.2.22.5/lib/active_record/connection_adapters/postgresql_adapter.rb:1163:in `async_exec'
Caused by PG::GroupingError: ERROR: aggregate functions are not allowed in WHERE
LINE 1: ..."photos"."property_id" = "properties"."id" WHERE (photos.cou...
^
from /.rvm/gems/ruby-2.3.7@myapp/gems/activerecord-3.2.22.5/lib/active_record/connection_adapters/postgresql_adapter.rb:1163:in `async_exec'
[33] pry(main)> Property.joins(:photos).where('photos.count > 0').count
(11.1ms) SELECT COUNT(*) FROM "properties" INNER JOIN "photos" ON "photos"."property_id" = "properties"."id" WHERE (photos.count > 0)
ActiveRecord::StatementInvalid: PG::GroupingError: ERROR: aggregate functions are not allowed in WHERE
LINE 1: ..."photos"."property_id" = "properties"."id" WHERE (photos.cou...
^
: SELECT COUNT(*) FROM "properties" INNER JOIN "photos" ON "photos"."property_id" = "properties"."id" WHERE (photos.count > 0)
from /.rvm/gems/ruby-2.3.7@myapp/gems/activerecord-3.2.22.5/lib/active_record/connection_adapters/postgresql_adapter.rb:1163:in `async_exec'
Caused by PG::GroupingError: ERROR: aggregate functions are not allowed in WHERE
LINE 1: ..."photos"."property_id" = "properties"."id" WHERE (photos.cou...
[38] pry(main)> Property.joins(:photos).count("properties.id").count
(158.2ms) SELECT COUNT(properties.id) FROM "properties" INNER JOIN "photos" ON "photos"."property_id" = "properties"."id"
NoMethodError: undefined method `count' for 72604:Fixnum
from (pry):38:in `__pry__'
[39] pry(main)> Property.joins(:photos).count("properties.id")
(50.6ms) SELECT COUNT(properties.id) FROM "properties" INNER JOIN "photos" ON "photos"."property_id" = "properties"."id"
=> 72604
[40] pry(main)> Photo.joins(:properties).count("photos.id")
ActiveRecord::ConfigurationError: Association named 'properties' was not found on Photo; perhaps you misspelled it?
from /.rvm/gems/ruby-2.3.7@myapp/gems/activerecord-3.2.22.5/lib/active_record/associations/join_dependency.rb:112:in `build'
[41] pry(main)> Photo.joins(:property).count("photos.id")
(65.0ms) SELECT COUNT(photos.id) FROM "photos" INNER JOIN "properties" ON "properties"."id" = "photos"."property_id"
=> 72604
[42] pry(main)> Photo.joins(:property).count("photos.id")
(60.5ms) SELECT COUNT(photos.id) FROM "photos" INNER JOIN "properties" ON "properties"."id" = "photos"."property_id"
=> 72604
[43] pry(main)> Property.joins(:photos).count("properties.id").distinct
(46.4ms) SELECT COUNT(properties.id) FROM "properties" INNER JOIN "photos" ON "photos"."property_id" = "properties"."id"
NoMethodError: undefined method `distinct' for 72604:Fixnum
from (pry):43:in `__pry__'
[44] pry(main)> Property.joins(:photos).distinct.count("properties.id")
=> 0
This should be simple, but for some reason it's much more difficult than I expected.
Thoughts?
Aucun commentaire:
Enregistrer un commentaire