title: SQLite query problem
body: <p>I'm trying to select all the recent discussions replied by the user. But the following query doesn't work. Returns 0 entries. I'm new to sql, I guess there are something wrong in my using of "IN". Please help trouble-shooting this: </p>
  
  <pre><code>    SELECT * FROM discussions 
      WHERE discussable_id IN 
      (SELECT commentable_id FROM comments 
      WHERE commentable_type = 'Discussion' AND user_id = 1);
  </code></pre>
  
  <p>also, what if I want to sort the above query by the comment.created_at? How do I do that? Plus, what if I want to also include all the discussions created by the user, and also sort all the query by the latest activity(could be discussion.created_at or comment.created_at). I'm in rails by the way. I've been trying to solving this for few hours. Would really appreciate any input. Thanks so much!</p>
  
  <p>UPDATE2: Looks like I should replace discussable_id in the query with "id", did you spot it? :) such a tedious problem. Now, how do I order the returned discussions by comment.created_at? Should I open another question?</p>
  
  <p>UPDATE1:
  Sorry, here are the activerecord:</p>
  
  <pre><code># Table name: discussions
  #
  #  id               :integer         not null, primary key
  #  title            :string(255)     not null
  #  content          :text(255)       not null
  #  created_at       :datetime
  #  updated_at       :datetime
  #  user_id          :integer
  #  discussable_id   :integer
  #  discussable_type :string(255)
  class Discussion &lt; ActiveRecord::Base
    has_many    :comments, :as =&gt; :commentable, :dependent =&gt; :destroy
    #this is the scope that I'm having trouble with:
    scope :user_replied_group_discussions, lambda {|user| replied_by(user) }
  
    def last_reply
      if self.comments.any?
        self.comments.last.created_at
      else
        self.created_at
      end
    end
    private
      def self.replied_by(user)
        discussion_ids = %(SELECT commentable_id FROM comments 
                       WHERE commentable_type = 'Discussion' AND user_id = :user_id)
        where("discussable_type = 'Group' AND discussable_id IN (#{discussion_ids}) ", 
             { :user_id =&gt; user })
      end
  end
  
  # Table name: comments
  #
  #  id               :integer         not null, primary key
  #  content          :text
  #  created_at       :datetime
  #  updated_at       :datetime
  #  commentable_id   :integer
  #  commentable_type :string(255)
  #  user_id          :integer
  #
  
  class Comment &lt; ActiveRecord::Base
  
    belongs_to :commentable, :polymorphic =&gt; true
    belongs_to :user
  
  end
  </code></pre>
  
  <p>Anyway, I tried to run the sql query in the sqlite3 console, returns 0 entry. Thanks!</p>