title: rails: query and filter n:m related objects using active record
body: <p>I'm trying to use active-record query possible connections between airports.
  I described the models I created already in another question here:</p>
  
  <p><a href="https://stackoverflow.com/questions/16388623/nm-self-join-with-ruby-on-rails-active-record">n:m self-join with ruby on rails active record</a></p>
  
  <p>Basically, what I can do now is that:</p>
  
  <pre><code>ny = Airport.create({"city" =&gt; "New York"})
  la = Airport.create({"city" =&gt; "Los Angeles"})
  
  ny.destinations &lt;&lt; la
  la.destinations &lt;&lt; ny
  </code></pre>
  
  <p>I ran into an issue querying the data I'm looking for, which is quite simple in SQL but I had no luck with active record yet.</p>
  
  <pre><code>ny = Airport.where('city = ?', 'New York')
  ny.destinations
  </code></pre>
  
  <p>returns the correct objects, but all of them.</p>
  
  <p>The SQL query looks like that:</p>
  
  <pre><code> SELECT "airports".* FROM "airports" INNER JOIN "connections" ON "airports"."id" = "connections"."destination_id" WHERE "connections"."airport_id" = 3
  </code></pre>
  
  <p>I'd like to filter those results by cities starting with "s" for example, so an SQL query could look like that:</p>
  
  <pre><code>SELECT "airports".* FROM "airports" INNER JOIN "connections" ON "airports"."id" = "connections"."destination_id" WHERE "connections"."airport_id" = 3 AND airports"."city" LIKE "s%"
  </code></pre>
  
  <p>I tried it this way:</p>
  
  <pre><code>ny.destinations.where('city LIKE ?', '#{params[:query]}%')
  </code></pre>
  
  <p>But I always get an empty result. </p>
  
  <p><strong>How could I use active record to filter my resulting objetcs?</strong></p>
  
  <p><em>edit: Thats the best solution I found so far:</em></p>
  
  <p>I added the cityLike() method to the Airport model:</p>
  
  <p>app/models/airport.rb:</p>
  
  <pre><code>class Airport &lt; ActiveRecord::Base
    attr_accessible :city, :name
  
    has_many :connections
    has_many :destinations, :through =&gt; :connections
    has_many :inverse_connections, :class_name =&gt; "Connection", :foreign_key =&gt; "destination_id"
    has_many :inverse_destinations, :through =&gt; :inverse_connections, :source =&gt; :airport
  
    def self.cityLike(query)
      where("city LIKE ?", "%#{query}%")
    end
  end
  </code></pre>
  
  <p>app/model/connection.rb:</p>
  
  <pre><code>class Connection &lt; ActiveRecord::Base
    attr_accessible :destination_id, :airport_id
  
    belongs_to :airport
    belongs_to :destination, :class_name =&gt; "Airport"
  end
  </code></pre>
  
  <p>Now I can query the objects with the following statement:</p>
  
  <pre><code>Airport.find(1).destinations.cityLike("a")
  </code></pre>
  
  <p>Not sure if it's the best solution, but it produces the query I was looking for.</p>
  
  <p>Thanks a lot to all af you!</p>