导轨：查​​询和过滤器N：M采用活动记录相关的对象 [英] rails: query and filter n:m related objects using active record

问题描述

我想使用机场之间的有效记录查询可能的连接。 我描述我已经在另外一个问题在这里创建的模型：

I'm trying to use active-record query possible connections between airports. I described the models I created already in another question here:

N：红宝石米自连接在轨道上活跃记录

基本上，我现在能做的就是：

Basically, what I can do now is that:

ny = Airport.create({"city" => "New York"})
la = Airport.create({"city" => "Los Angeles"})

ny.destinations << la
la.destinations << ny

我遇到了一个问题，询问我在寻找数据，这是在SQL很简单，但我没有运气与活动记录呢。

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.

ny = Airport.where('city = ?', 'New York')
ny.destinations

返回正确的对象，但所有的人。

returns the correct objects, but all of them.

在SQL查询看起来像：

The SQL query looks like that:

 SELECT "airports".* FROM "airports" INNER JOIN "connections" ON "airports"."id" = "connections"."destination_id" WHERE "connections"."airport_id" = 3

我想通过启动以S，例如城市来过滤这些结果，所以SQL查询看起来是这样的：

I'd like to filter those results by cities starting with "s" for example, so an SQL query could look like that:

SELECT "airports".* FROM "airports" INNER JOIN "connections" ON "airports"."id" = "connections"."destination_id" WHERE "connections"."airport_id" = 3 AND airports"."city" LIKE "s%"

我尝试了这种方式：

I tried it this way:

ny.destinations.where('city LIKE ?', '#{params[:query]}%')

不过，我总是得到一个空的结果。

But I always get an empty result.

我怎么能使用活动记录进行筛选我造成objetcs？

编辑：多数民众赞成我发现迄今最好的解决办法：的

我添加了cityLike（）方法来机场模型：

I added the cityLike() method to the Airport model:

应用程序/模型/ airport.rb：

app/models/airport.rb:

class Airport < ActiveRecord::Base
  attr_accessible :city, :name

  has_many :connections
  has_many :destinations, :through => :connections
  has_many :inverse_connections, :class_name => "Connection", :foreign_key => "destination_id"
  has_many :inverse_destinations, :through => :inverse_connections, :source => :airport

  def self.cityLike(query)
    where("city LIKE ?", "%#{query}%")
  end
end

应用程序/模型/ connection.rb：

app/model/connection.rb:

class Connection < ActiveRecord::Base
  attr_accessible :destination_id, :airport_id

  belongs_to :airport
  belongs_to :destination, :class_name => "Airport"
end

现在我可以查询的对象，用下面的语句：

Now I can query the objects with the following statement:

Airport.find(1).destinations.cityLike("a")

不知道这是最好的解决办法，但它产生的我一直在寻找的查询。

Not sure if it's the best solution, but it produces the query I was looking for.

非常感谢所有的自动对焦您！

Thanks a lot to all af you!

推荐答案

这code：

ny.destinations.where('city LIKE ?', '#{params[:query]}%')

是这样工作的 - 首先，你有一个对象NY repesenting的纽约市。当你说.destinations你现在跟着你在你的模型中定义的关系来检索所有，你可以得到来自纽约的目的地。但是，如果我正确地想像你的数据库模式，这些目标实际上并没有一个叫做城市领域;相反，他们有一个目标ID，它关系目标到一个特定的机场，它是有一个与之相关的城市机场。

works like this--first you have an object ny repesenting the city of New York. When you say ".destinations" you have now followed a relation you defined in your model to retrieve all the destinations that you can get to from New York. However, if I'm imagining your database schema correctly, these destinations don't actually have a field called "city"; instead, they have a destination_id, which ties the destination to a particular airport, and it's the airport that has a city associated with it.

所以，当您查询目标表的城市是怎样的？，没有找到任何匹配的记录。

So when you query the destination table for 'city LIKE ?', it doesn't find any matching records.

相反，尝试

ny.destinations.joins(:airports).where('city LIKE ?', '#{params[:query]}%')

这篇关于导轨：查​​询和过滤器N：M采用活动记录相关的对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！