Rails JOIN TABLES

Question

Hi, i'm trying to use the GeoKit plugin to calculate the distance between 2 points. So the idea is, i do a search for an article, and the results i want to order by distance. So i have a form where I enter the article (that im looking for) and my address. Then rails must find all articles that match with my query and order by address.

So now, i have two models: Article and User. Articles belongs_to User and User has_many Articles. At the User model i have the info related with my latitude and longitude.

So my Article object has three fields:

• id
• name
• user_id (FK to User model)

And my user model has four fields

• id
• name
• lat (latitude)
• lng (longitude)

OK, to have access to the user info thru articles i do the query:

@articles = Article.find(:all,:conditions=>"vectors @@ to_tsquery('büch')",:joins=>" INNER JOIN users ON users.id = articles.user_id",:include=>:user,:origin=>"Augustusplatz,8,leipzig,germany")

it works. But when i wanna add an :order=>'distance ASC' it fails because the order by query is using a Article.lat, and Article.lng fields to calculate the distance, but these fields lat and lng, are User object's members and not Article member.

BTW if I get the query generated by rails and i change the order by clause where uses articles.lat/lng to users.lat/lng it works.

Answer 1

I'm not really an SQL expert, and I find joins very hard to wrap my head around (even more than figuring out multiple NOT's like if(!(foo != !bar & (!baz))) ) but I do feel that either the :joins line or the :include line is redundant, or even wrong.

(I cleaned up your query so I could understand it, please do the same with the question):

@articles = Article.find(:all,
    :conditions=>"vectors @@ to_tsquery('büch')",
    :joins=>" INNER JOIN users ON users.id = articles.user_id",
    :include=>:user,
    :origin=>"Augustusplatz,8,leipzig,germany")

I guess either the model of the user and article should define their relation, so you don't need a manual join or that you remove the include.

But I don't see from this code where the distance is actually calculated (probably some automagic in GeoKit?). The distance should be "named" with the SQL someting like:

SELECT (some_heavy_calculation(user.lat, user.long)) AS distance, ... ;

So you can refer to the distance in the order by clause, I don't think the database cares if the ruby object doesn't contain the value.

Perhaps if you showed us the generated SQL, wich causes errors, and you'r manipulated SQL we could understand better...

Answer 2

I didnt want to put the sql generated in the first post otherwise it would be too much thing :-)

I was using just include, that works, but i dont know why, include makes a left join, and i need a inner join. If i remove the include i cannot access the user information thru the article object (like for example articles[0].user.name)

here it is my sql. if i change in the order by the fields articles.lat and articles.lng to users.lat and user.lng it works:

SELECT "articles"."id" AS t0_r0, "articles"."name" AS t0_r1, "articles"."price" AS t0_r2, "articles"."user_id" AS t0_r3, "articles"."created_at" AS t0_r4, "articles"."updated_at" AS t0_r5, "articles"."vectors" AS t0_r6, "users_articles"."id" AS t1_r0, "users_articles"."name" AS t1_r1, "users_articles"."address" AS t1_r2, "users_articles"."created_at" AS t1_r3, "users_articles"."updated_at" AS t1_r4, "users_articles"."lat" AS t1_r5, "users_articles"."lng" AS t1_r6, "users_articles"."zipcode" AS t1_r7 FROM "articles"  LEFT OUTER JOIN "users" users_articles ON "users_articles".id = "articles".user_id     INNER JOIN users ON users.id = articles.user_id WHERE (vectors @@ to_tsquery('büch'))  ORDER BY                   (ACOS(least(1,COS(0.896021391737553)*COS(0.216084610510851)*COS(RADIANS(articles.lat))*COS(RADIANS(articles.lng))+
              COS(0.896021391737553)*SIN(0.216084610510851)*COS(RADIANS(articles.lat))*SIN(RADIANS(articles.lng))+
              SIN(0.896021391737553)*SIN(RADIANS(articles.lat))))*3963.19)

Regards,

Victor