join - ruby on rails - 如何计算有一个或多个关联对象的记录的数目?

我有一个Property模型,has_many :photos ,我想计算多个照片的properties的数目。

那我该怎么做?

我已经尝试过了


> Property.where('properties.photos.count > ?', 0).count



 (3.1ms) SELECT COUNT(*) FROM"properties" WHERE (properties.photos.count > 1)


ActiveRecord::StatementInvalid: PG::UndefinedTable: ERROR: missing FROM-clause entry for table"photos"


LINE 1: SELECT COUNT(*) FROM"properties" WHERE (properties.photos....


 ^


: SELECT COUNT(*) FROM"properties" WHERE (properties.photos.count > 0)


from /ruby-2.3.0@myproject/gems/activerecord-3.2.22.5/lib/active_record/connection_adapters/postgresql_adapter.rb:1163:in `async_exec'


Caused by PG::UndefinedTable: ERROR: missing FROM-clause entry for table"photos"


LINE 1: SELECT COUNT(*) FROM"properties" WHERE (properties.photos....



到:


> Property.joins(:photos).where('photos.count > ?', 0).count



 (3.7ms) 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 ruby-2.3.0@myproject/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...



更先进的:


>Property.includes(:photos).group(['property.id', 'photos.id']).order('COUNT(photos.id) DESC').count



(0.6ms) SELECT COUNT(DISTINCT"properties"."id") AS count_id, property.id AS property_id, photos.id AS photos_id FROM"properties" LEFT OUTER JOIN"photos" ON"photos"."property_id" ="properties"."id" GROUP BY property.id, photos.id ORDER BY COUNT(photos.id) DESC


ActiveRecord::StatementInvalid: PG::UndefinedTable: ERROR: missing FROM-clause entry for table"property"


LINE 1: ...CT COUNT(DISTINCT"properties"."id") AS count_id, property.i...


 ^


: SELECT COUNT(DISTINCT"properties"."id") AS count_id, property.id AS property_id, photos.id AS photos_id FROM"properties" LEFT OUTER JOIN"photos" ON"photos"."property_id" ="properties"."id" GROUP BY property.id, photos.id ORDER BY COUNT(photos.id) DESC


from ruby-2.3.0@myproject/gems/activerecord-3.2.22.5/lib/active_record/connection_adapters/postgresql_adapter.rb:1163:in `async_exec'


Caused by PG::UndefinedTable: ERROR: missing FROM-clause entry for table"property"


LINE 1: ...CT COUNT(DISTINCT"properties"."id") AS count_id, property.i...



它们都产生类似的错误。

我究竟做错了什么?

注:我所需要的是photos.count 换句话说,如果我的数据库中有5000个属性,我想只返回照片的属性。

时间:

编辑:


Property.joins(:photos).group('photos.property_id').having('count(photos.property_id) > 1').count



#=> {1234=>2} # 1234 is property id 2 is count of photos 



你将获得属性ID及它关联照片的数量。

旧答案:

你可以获得至少一张与之相关的照片


Property.includes(:photos).where.not(photos: { property_id: nil })



当你使用Rails 3.2 .not时,你必须使用,


Property.includes(:photos).where("property_id IS NOT null")



因为你想要的是Property的Photo,那么INNER JOIN就是你需要的。


Property.joins(:photos) 



如果你想要一个范围


class Property < ActiveRecord::Base


 scope :with_photos, -> {joins(:photos)} 


end 



使用Rails 3.2获取计数


Property.with_photos.count(distinct: true) 



你也可以使用:Rails 3.2


Property.count(joins: :photos, distinct: true) 



ActiveRecord::Calculations#count Doc

这将执行


SELECT 


 COUNT(DISTINCT properties.id) 


FROM 


 properties


 INNER JOIN photos ON photos.property_id = properties.id



我在项目中做过,你可以这样尝试


Photo.group(:property_id).count



你将获得带有照片计数的属性id


results = { 3314=>3, 2033=>3, 3532=>2, 3565=>6, 3510=>1, 3022=>7, 648=>2, 570=>3, 4678=>3, 3540=>1, 3489=>4, 536=>1, 1715=>4 }



请按以下步骤操作:


class Property < ApplicationRecord


 has_many :photos



 def self.with_photos


 self.all.reject { |p| p.photos.empty? }


 end


end



Property.with_photos.count



更有效的(Rails 4+ ):


Property.joins(:photos).uniq.count



更有效的(Rails 5.1 +):


Property.joins(:photos).distinct.count




Property.includes(:photos).where("SELECT count(photos.id) > 0 FROM photos WHERE property_id = properties.id")



作为作用域:


scope :with_photos, -> { where("SELECT count(photos.id) > 0 FROM photos WHERE property_id = properties.id") }



...