加入问题

Question

它看起来像这样：

nearbys(20, :units => :km).joins(:interests)
    .where(["users.id NOT IN (?)", blocked_ids])
    .where("interests.language_id IN (?)", interests
        .collect{|interest| interest.language_id})

这会产生以下 SQL：

SELECT 
    *, 
    (111.19492664455873 * ABS(latitude - 47.4984056) * 0.7071067811865475) + 
        (96.29763124613503 * ABS(longitude - 19.0407578) * 0.7071067811865475) 
        AS distance, 
    CASE 
        WHEN (latitude >= 47.4984056 AND longitude >= 19.0407578) THEN 45.0 
        WHEN (latitude < 47.4984056 AND longitude >= 19.0407578) THEN 135.0 
        WHEN (latitude < 47.4984056 AND longitude < 19.0407578) THEN 225.0 
        WHEN (latitude >= 47.4984056 AND longitude < 19.0407578) THEN 315.0 
    END AS bearing 
FROM 
    "users" 
    INNER JOIN "interests" ON "interests"."user_id" = "users"."id"
WHERE 
    (latitude BETWEEN 47.38664309234778 AND 47.610168107652214 
        AND longitude BETWEEN 18.875333386667762 AND 19.20618221333224 
        AND users.id != 3) 
    AND (users.id NOT IN (3)) 
    AND (interests.language_id IN (1,1))     
GROUP BY 
    users.id,users.name,users.created_at,users.updated_at,users.location,
    users.details,users.hash_id,users.facebook_id,users.blocked,users.locale,
    users.latitude,users.longitude 
ORDER BY 
    (111.19492664455873 * ABS(latitude - 47.4984056) * 0.7071067811865475) + 
    (96.29763124613503 * ABS(longitude - 19.0407578) * 0.7071067811865475)

它返回的结果是正确的，只是它用兴趣的 id 替换了用户的 id。我在这里缺少什么？

感谢您的帮助！

编辑：

我将问题范围缩小到地理编码的gem。

这工作完美：

User.where(["users.id NOT IN (?)", blocked_ids]).joins(:interests)
    .where("interests.language_id IN (?)", interests
        .collect{|interest| interest.language_id})

并返回：

[#<User id: 8, 
    name: "George Supertramp", 
    created_at: "2011-08-13 15:51:46", 
    updated_at: "2011-08-21 16:11:05", 
    location: "Budapest", 
    details: "{\"image\":\"http://graph.facebook.com/...", 
    hash_id: 1908133256, 
    facebook_id: nil, 
    blocked: nil, 
    locale: "de", 
    latitude: 47.4984056, 
    longitude: 19.0407578>]

但是当我添加 .near([latitude, longitude], 20, :units => :km) 它会返回，

[#<User id: 5, 
    name: "George Supertramp", 
    created_at: "2011-08-13 15:52:53", 
    updated_at: "2011-08-13 15:52:53", 
    location: "Budapest",
    details: "{\"image\":\"http://graph.facebook.com/...", 
    hash_id: 1908133256, 
    facebook_id: nil, 
    blocked: nil, 
    locale: "de", 
    latitude: 47.4984056, 
    longitude: 19.0407578>]

因为如果以某种方式与兴趣结果合并：

[#<Interest id: 5, 
    user_id: 8, 
    language_id: 1, 
    classification: 1, 
    created_at: "2011-08-13 15:52:53", 
    updated_at: "2011-08-13 15:52:53">]

看来问题出在分组上。我怎样才能在不分叉宝石的情况下绕过它。

Answer 1

我通过使用 include 而不是 join 暂时解决了这个问题。这是一个愚蠢的解决方案，它适用于小数据集，同时积极缓存。

这是代码：

User.where(["users.id NOT IN (?)", blocked_ids]).includes(:interests).near([latitude, longitude], 20, :units => :km).select{|user| user if ([user.interests.find_by_classification(1).language_id, user.interests.find_by_classification(2).language_id] - [self.interests.find_by_classification(1).language_id, self.interests.find_by_classification(2).language_id]).size < 2 }

Answer 2

我认为您连接表有一个 id 字段，这导致了问题。