Problem with join

It looks like this:

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

This produces the following 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 (l开发者_如何学JAVAatitude < 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)

The result it returns is correct, except it replaces the id of the user with the id of the interest. What am I missing here?

Thanks for the help!

Edit:

I narrowed the problem down to the geocoded gem.

This works perfectly:

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

and returns:

[#<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>]

but when I add .near([latitude, longitude], 20, :units => :km) it returns

[#<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>]

because if somehow merges with the interest result:

[#<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">]

It seems the problem is with the grouping. How can I circumvent it without forking the gem.

---

I've solved the problem temporarily by using include instead of join. It is a stupid solution and it works on small sets of data while aggressively cached.

Here is the code:

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 }

---

I think you join table has an id field which is causing the issue.