Searching with Join in Rails 3. Please help!

Question

Hello!

I am developing a small application in Rails 3. In this application users can be connected to each other. I am mainly using two tables to store user data and relations. I want to search these tables with either firstname or lastname to get the contacts of a user.

Table ONE - Profiles

In this table I am storing Firstname, Lastname and Id of a user.

Table TWO - Contacts

In this table I am storing the ID (profile_id) of the user and the ID of the user he is connected to (friend_id).

I am using the following query, but it does not get the correct results.

Profile.find_by_sql("SELECT * FROM contacts
              INNER JOIN profiles ON contacts.friend_id = profiles.id WHERE profiles.firstname = '#{@keyword}' OR  profiles.lastname = '#{@keyword}'")

What is wrong with is and how can it be more effective?

Thankful for all help!

Answer 1

You can just use ActiveRecord stuff ...

class Profile
  has_many :contacts, :foreign_key => 'friend_id', :class_name => 'Contact'
end

class Contact
end

Profile.joins(:contacts).where({:contacts => {:firstname' => 'Cyril', :lastname => 'Mougel'}).all

Answer 2

Three things stand out as being "wrong" with your query.

First, you are using find_by_sql for a relatively simple query. If you're going to write queries like this all over your application, you might as well just use PHP.

Second is the way you're passing the query params into the sql. This will leave you wide open for SQL injection attacks since input is not escaped.

Both of these problems can be addressed using built in ActiveRecord methods. As some of the other answers have indicated, this is a pretty simple refactor.

The third issue I see is not using standard Rails naming conventions for foreign keys. Instead of Contacts.friend_id, it should be Contacts.profile_id. This is not a requirement, but the more conventional your code is, the less "hacks" you'll need to use with your model relationships. The only acceptable exception to this rule is if you are retrofitting an existing database schema, but it doesn't sound like this is your situation.

I recommend looking at RailsGuides for more information on standard conventions.