How to I make my custom mySQL query to in rails 3?

Question

Im trying to display recently added comments from tattoos a user has posted. So If I posted a tattoo, and then user_b posted "hey I like your tattoo" then Im trying to get just the comment.

First of all Im using the acts_as_commentable_with_threading gem which doesnt create a foreign key for the table im trying to join. So my controller cant look for tattoo_id, it has to look for commentable_id

In the controller I would have to call the Comment model and then pass some SQL stuff into it but apparently I have no clue how to pass custom SQL queries into ruby because even tho my query string works in terminal, I get all sorts of nonsense when trying to use it in rails.

Im basically trying to do this:

SELECT comments.id FROM comments,tattoos WHERE commentable_id = tattoos.id AND 
tattoos.member_id = #{current_user}

where #{current_user} will be the current_user passed in.

Answer 1

You don't have to jump through so many hoops to accomplish this. acts_as_commentable assigns a polymorphic association, so you should set it up like this:

class Comment < ActiveRecord::Base
  belongs_to :user
  belongs_to :commentable, :polymorphic => true
end

class Tattoo < ActiveRecord::Base
  has_many :comments, :as => :commentable
end

class User
  has_many comments
end

Then you can access the association as usual:

Tattoo.where(:member_id => current_user).first.comments

See http://railscasts.com/episodes/154-polymorphic-association for a general tutorial on how polymorphic associations work. It just so happens that this railscast uses exactly :commentable as the polymorphic example, so you should be able to follow along directly if you want.

Answer 2

I think Ben's approach is best but for future reference if you do come across something more complicated you can always use sql for example:

Comment.find_by_sql("SELECT comments.* FROM comments,tattoos WHERE commentable_id = tattoos.id AND tattoos.member_id = ?", current_user)