Ruby on Rails的收益率进行查询意外的结果 [英] Ruby on Rails query yielding unexpected results
问题描述
这是一个后续到以前的版本： Ruby on Rails的查询不工作正确的。
This is a followup to an earlier thread: Ruby on Rails query not working properly.
如前所述，我有几个目录。特别是，上市的has_many：空间，通过：名称和的has_many：设施，通过：：产品
As noted, I have several listings. In particular, a listing has_many :spaces, through: :designations and has_many :amenities, through: :offerings.
我定义过滤器来限制得到所示的清单。
I define filters to restrict the listings that get shown.
这两个主要的有：
# filter by amenities
if params[:search][:amenity_ids].present? && params[:search][:amenity_ids].reject(&:blank?).size > 0
@listings = @listings.joins(:amenities).where(amenities: { id: params[:search][:amenity_ids].reject(&:blank?) }).group('listings.id').having('count(*) >= ?', params[:search][:amenity_ids].reject(&:blank?).size)
end
# filter by space type
if params[:search][:space_ids].present? && params[:search][:space_ids].reject(&:blank?).size > 0
@listings = @listings.joins(:spaces).where('space_id IN (?)', params[:search][:space_ids].reject(&:blank?)).uniq
end
（注意这些反映在较早的线程指示的溶液。）
(Note that these reflect the solution indicated in the earlier thread.)
第一重滤网可以说：让所有这一切都选定设施的清单
The first filter says: get all of the listings that have ALL of the selected amenities.
第二个过滤器说：让所有的匹配任何选定的空间类型房源
The second filter says: get all of the listings that match ANY of the selected space types.
不过，有一个问题仍然存在。如果我筛选的空间1型和2和设施1和2，我得到上市A（有空间，1型和2和市容2）。
But one issue remains. If I filter for space types 1 and 2 and amenities 1 and 2, I get listing A (which has space types 1 and 2 and amenity 2).
但我要presumably GET [] ，因为没有上市既有设施1和2。
But I should presumably get [] since no listing has both amenities 1 and 2.
这是怎么回事这些疑问？如果他们不是独立的，而是链式？
What is going on with these queries? Should they not be independent, but chainable?
下面是输出（I禁止其他过滤器的清晰度）：
Here is the output (I disabled the other filters for clarity):
Started GET "/listings/search?utf8=%E2%9C%93&search%5Baddress%5D=London%2C+United+Kingdom&search%5Bprice_min%5D=0&search%5Bprice_max%5D=1000.0&search%5Bprice_lower%5D=0&search%5Bprice_upper%5D=1000&search%5Bsize_min%5D=0&search%5Bsize_max%5D=1000&search%5Bsize_lower%5D=0&search%5Bsize_upper%5D=1000&search%5Bspace_ids%5D%5B%5D=1&search%5Bspace_ids%5D%5B%5D=2&search%5Bspace_ids%5D%5B%5D=&search%5Bamenity_ids%5D%5B%5D=1&search%5Bamenity_ids%5D%5B%5D=2&search%5Bamenity_ids%5D%5B%5D=&search%5Bsort_by%5D=Distance&commit=Apply+Filters" for ::1 at 2015-10-31 14:25:58 +0000
ActiveRecord::SchemaMigration Load (0.4ms) SELECT "schema_migrations".* FROM "schema_migrations"
Processing by ListingsController#search as HTML
Parameters: {"utf8"=>"✓", "search"=>{"address"=>"London, United Kingdom", "price_min"=>"0", "price_max"=>"1000.0", "price_lower"=>"0", "price_upper"=>"1000", "size_min"=>"0", "size_max"=>"1000", "size_lower"=>"0", "size_upper"=>"1000", "space_ids"=>["1", "2", ""], "amenity_ids"=>["1", "2", ""], "sort_by"=>"Distance"}, "commit"=>"Apply Filters"}
(1.5ms) SELECT MAX("listings"."price") FROM "listings"
(0.6ms) SELECT MAX("listings"."size") FROM "listings"
Listing Load (4.4ms) SELECT DISTINCT "listings".* FROM "listings" INNER JOIN "offerings" ON "offerings"."listing_id" = "listings"."id" INNER JOIN "amenities" ON "amenities"."id" = "offerings"."amenity_id" INNER JOIN "designations" ON "designations"."listing_id" = "listings"."id" INNER JOIN "spaces" ON "spaces"."id" = "designations"."space_id" WHERE "amenities"."id" IN (1, 2) AND (space_id IN ('1','2')) GROUP BY listings.id HAVING count(*) >= 2 LIMIT 24 OFFSET 0
Image Load (0.5ms) SELECT "images".* FROM "images" WHERE "images"."listing_id" = $1 ORDER BY "images"."id" ASC LIMIT 1 [["listing_id", 1]]
Space Load (0.6ms) SELECT "spaces".* FROM "spaces" INNER JOIN "designations" ON "spaces"."id" = "designations"."space_id" WHERE "designations"."listing_id" = $1 [["listing_id", 1]]
Rendered listings/_map_infowindow.html.erb (56.1ms)
Rendered listings/_price_slider.html.erb (0.7ms)
Rendered listings/_size_slider.html.erb (0.6ms)
Space Load (0.4ms) SELECT "spaces".* FROM "spaces"
Amenity Load (0.4ms) SELECT "amenities".* FROM "amenities"
Rendered scripts/_checkbox_toggle.html.erb (0.5ms)
Rendered listings/_search_filters.html.erb (75.5ms)
(0.4ms) SELECT "spaces"."name" FROM "spaces" INNER JOIN "designations" ON "spaces"."id" = "designations"."space_id" WHERE "designations"."listing_id" = $1 [["listing_id", 1]]
CACHE (0.0ms) SELECT "images".* FROM "images" WHERE "images"."listing_id" = $1 ORDER BY "images"."id" ASC LIMIT 1 [["listing_id", 1]]
User Load (0.7ms) SELECT "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1 [["id", 3]]
Avatar Load (0.7ms) SELECT "avatars".* FROM "avatars" WHERE "avatars"."user_id" = $1 ORDER BY "avatars"."id" ASC LIMIT 1 [["user_id", 3]]
Rendered listings/_listing_grid.html.erb (80.8ms)
(3.1ms) SELECT DISTINCT COUNT(DISTINCT "listings"."id") AS count_id, listings.id AS listings_id FROM "listings" INNER JOIN "offerings" ON "offerings"."listing_id" = "listings"."id" INNER JOIN "amenities" ON "amenities"."id" = "offerings"."amenity_id" INNER JOIN "designations" ON "designations"."listing_id" = "listings"."id" INNER JOIN "spaces" ON "spaces"."id" = "designations"."space_id" WHERE "amenities"."id" IN (1, 2) AND (space_id IN ('1','2')) GROUP BY listings.id HAVING count(*) >= 2
Rendered scripts/_map.html.erb (2.9ms)
Rendered scripts/_shuffle.html.erb (0.3ms)
Rendered listings/search.html.erb within layouts/application (178.7ms)
Rendered layouts/_head.html.erb (475.7ms)
Rendered scripts/_address_autocomplete.html.erb (0.3ms)
Rendered listings/_search_address.html.erb (13.7ms)
User Load (0.4ms) SELECT "users".* FROM "users" WHERE "users"."id" = $1 ORDER BY "users"."id" ASC LIMIT 1 [["id", 3]]
(0.5ms) SELECT DISTINCT "conversations"."id" FROM "conversations" WHERE (sender_id = 3 OR recipient_id = 3)
(0.5ms) SELECT DISTINCT "messages"."conversation_id" FROM "messages" WHERE ("messages"."user_id" != $1) AND "messages"."read" = $2 [["user_id", 3], ["read", "false"]]
CACHE (0.0ms) SELECT "avatars".* FROM "avatars" WHERE "avatars"."user_id" = $1 ORDER BY "avatars"."id" ASC LIMIT 1 [["user_id", 3]]
Rendered layouts/_navbar.html.erb (32.5ms)
Rendered scripts/_fade_error.html.erb (0.4ms)
Rendered scripts/_transparent_navbar.html.erb (0.3ms)
Completed 200 OK in 1045ms (Views: 688.6ms | ActiveRecord: 30.6ms)
我也尝试添加提高测试，以尽在better_errors一些测试外壳带电。我发现：
I have also tried adding raise 'test' in order to do some testing in the better_errors live shell. I discovered:
>> @listings
=> #<ActiveRecord::Relation []>
>> @listings = @listings.joins(:spaces).where('space_id IN (?)', params[:search][:space_ids].reject(&:blank?)).uniq
=> #<ActiveRecord::Relation [#<Listing id: 1, title: "Test 1", address: "New Inn Passage, London WC2A 2AE, UK", latitude: 51.5139664, longitude: -0.1167323, size: 1000, min_lease: 1, price: #<BigDecimal:7f89ec245c98,'0.1E4',9(18)>, description: "Test 1", user_id: 3, state: "public", created_at: "2015-10-30 17:37:04", updated_at: "2015-10-30 17:37:04">]>
>>
这是怎么回事，我该如何解决这个问题？
Why is this happening and how can I fix it?
任何帮助将是很大的AP preciated。
Any help would be greatly appreciated.
推荐答案
这个问题是你如何确定所有的设施都被匹配。
The issue is with how you are determining that all of the amenities have been matched.
当你只参加了设施则行（之前的分组）列表项的计数匹配的设施的数量，因此为条款做什么你想要的。
When you are only joining the amenities then the count of the rows (prior to grouping) for a listing is the number of matched amenities, so the having clause does what you want.
在加入空格表也行则数目（之前再次分组）的列表的匹配的设施的次数匹配的行数。在您的例子中，有2位和1个舒适性，因此计数为2，having子句你是满意的。
When you join the spaces table too, then the number of rows (again prior to grouping) for a listing is the number of matches amenities times the number of matched rows. In your example there are 2 spaces and 1 amenity, so the count is 2 and your having clause is satisfied.
如果不是过滤对 COUNT（*）您筛选的计数（不同amenities.id）则应计数被加入了该设施提供的行的数量，这应该产生所期望的结果。
If instead of filtering on count(*) you filtered on count(distinct amenities.id) then you should be counting the number of amenity rows that were joined, which should produce the desired result.
这篇关于Ruby on Rails的收益率进行查询意外的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
