如何将字符串传递给 has_many :finder_sql 参数?

发布于 2024-07-09 10:11:03 字数 2721 浏览 6 评论 0原文

在我的应用程序中,用户 has_many 票。 不幸的是,门票表没有 user_id:它有 user_login (它是旧数据库)。 有一天我会改变这一点,但目前这一改变会产生太多影响。

那么如何通过 login 列建立“user has_many :tickets”关联呢?

我尝试了以下finder_sql,但它不起作用。

class User  < ActiveRecord::Base
  has_many :tickets,
      :finder_sql => 'select t.* from tickets t where t.user_login=#{login}'
  ...
end

我收到一个奇怪的错误:

ArgumentError: /var/lib/gems/1.8/gems/activesupport-2.0.2/lib/active_support/dependencies.rb:402:in `to_constant_name': Anonymous modules have no name to be referenced by
    from /var/lib/gems/1.8/gems/activerecord-2.0.2/lib/active_record/base.rb:2355:in `interpolate_sql'
    from /var/lib/gems/1.8/gems/activesupport-2.0.2/lib/active_support/dependencies.rb:214:in `qualified_name_for'
    from /var/lib/gems/1.8/gems/activesupport-2.0.2/lib/active_support/dependencies.rb:477:in `const_missing'
    from (eval):1:in `interpolate_sql'
    from /var/lib/gems/1.8/gems/activerecord-2.0.2/lib/active_record/associations/association_proxy.rb:95:in `send'
    from /var/lib/gems/1.8/gems/activerecord-2.0.2/lib/active_record/associations/association_proxy.rb:95:in `interpolate_sql'
    from /var/lib/gems/1.8/gems/activerecord-2.0.2/lib/active_record/associations/has_many_association.rb:143:in `construct_sql'
    from /var/lib/gems/1.8/gems/activerecord-2.0.2/lib/active_record/associations/has_many_association.rb:6:in `initialize'
    from /var/lib/gems/1.8/gems/activerecord-2.0.2/lib/active_record/associations.rb:1032:in `new'
    from /var/lib/gems/1.8/gems/activerecord-2.0.2/lib/active_record/associations.rb:1032:in `tickets'
    from (irb):1

我也尝试了这个 finder_sql (在登录名周围使用双引号):

:finder_sql => 'select t.* from tickets t where t.user_login="#{login}"'

但它以同样的方式失败(无论如何,如果它有效,它将很容易受到 sql 注入的攻击)。

在测试数据库中,我在门票表中添加了 user_id 列,并尝试了此 finder_sql:

:finder_sql => 'select t.* from tickets t where t.user_login=#{id}'

现在效果很好。 显然,我的问题与我尝试使用的用户列是字符串而不是 id 有关。

我在网上搜索了很长一段时间……但找不到线索。

我希望能够将任何参数传递给 finder_sql,并编写如下内容:

has_many :tickets_since_subscription,
:finder_sql => ['select t.* from tickets t where t.user_login=?'+
     ' and t.created_at>=?', '#{login}', '#{subscription_date}']

编辑:我无法使用 has_many 关联的 :foreign_key 参数,因为我的用户表确实有一个 id 主键列,在应用程序的其他地方使用。

Edit#2:显然我没有足够彻底地阅读文档:has_many 关联可以采用 :primary_key 参数,来指定哪一列是本地主键(默认 id)。 谢谢丹尼尔让我大开眼界! 我想它回答了我最初的问题:

has_many tickets, :primary_key="login", :foreign_key="user_login"

但我仍然很想知道如何使 has_many :tickets_since_subscription 关联发挥作用。

In my application, a user has_many tickets. Unfortunately, the tickets table does not have a user_id: it has a user_login (it is a legacy database). I am going to change that someday, but for now this change would have too many implications.

So how can I build a "user has_many :tickets" association through the login column?

I tried the following finder_sql, but it does not work.

class User  < ActiveRecord::Base
  has_many :tickets,
      :finder_sql => 'select t.* from tickets t where t.user_login=#{login}'
  ...
end

I get a weird error:

ArgumentError: /var/lib/gems/1.8/gems/activesupport-2.0.2/lib/active_support/dependencies.rb:402:in `to_constant_name': Anonymous modules have no name to be referenced by
    from /var/lib/gems/1.8/gems/activerecord-2.0.2/lib/active_record/base.rb:2355:in `interpolate_sql'
    from /var/lib/gems/1.8/gems/activesupport-2.0.2/lib/active_support/dependencies.rb:214:in `qualified_name_for'
    from /var/lib/gems/1.8/gems/activesupport-2.0.2/lib/active_support/dependencies.rb:477:in `const_missing'
    from (eval):1:in `interpolate_sql'
    from /var/lib/gems/1.8/gems/activerecord-2.0.2/lib/active_record/associations/association_proxy.rb:95:in `send'
    from /var/lib/gems/1.8/gems/activerecord-2.0.2/lib/active_record/associations/association_proxy.rb:95:in `interpolate_sql'
    from /var/lib/gems/1.8/gems/activerecord-2.0.2/lib/active_record/associations/has_many_association.rb:143:in `construct_sql'
    from /var/lib/gems/1.8/gems/activerecord-2.0.2/lib/active_record/associations/has_many_association.rb:6:in `initialize'
    from /var/lib/gems/1.8/gems/activerecord-2.0.2/lib/active_record/associations.rb:1032:in `new'
    from /var/lib/gems/1.8/gems/activerecord-2.0.2/lib/active_record/associations.rb:1032:in `tickets'
    from (irb):1

I also tried this finder_sql (with double quotes around the login):

:finder_sql => 'select t.* from tickets t where t.user_login="#{login}"'

But it fails the same way (and anyway, if it worked it would be vulnerable to sql injection).

In a test database, I added a user_id column in the tickets table, and tried this finder_sql:

:finder_sql => 'select t.* from tickets t where t.user_login=#{id}'

Now this works fine. So apparently, my problem has to do with the fact that the users column I am trying to use is a string, not an id.

I searched the net for quite some time... but could not find a clue.

I would love to be able to pass any parameter to the finder_sql, and write things like this:

has_many :tickets_since_subscription,
:finder_sql => ['select t.* from tickets t where t.user_login=?'+
     ' and t.created_at>=?', '#{login}', '#{subscription_date}']

Edit: I cannot use the :foreign_key parameter of the has_many association because my users table does have an id primary key column, used elsewhere in the application.

Edit#2: apparently I did not read the documentation thoroughly enough: the has_many association can take a :primary_key parameter, to specify which column is the local primary key (default id). Thank you Daniel for opening my eyes! I guess it answers my original question:

has_many tickets, :primary_key="login", :foreign_key="user_login"

But I would still love to know how I can make the has_many :tickets_since_subscription association work.

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(4

微凉 2024-07-16 10:11:03

我认为您想要 has_many:primary_key 选项。 它允许您指定当前表上的列,该列的值存储在另一个表上的 :foreign_key 列中。

has_many :tickets, :foreign_key => "user_login", :primary_key => "login"

我通过阅读 has_many 文档找到了这一点。

I think you want the :primary_key option to has_many. It allows you to specify the column on the current Table who's value is stored in the :foreign_key column on the other table.

has_many :tickets, :foreign_key => "user_login", :primary_key => "login"

I found this by reading the has_many docs.

茶色山野 2024-07-16 10:11:03

要拥有类似 has_many :tickets_since_subscription 的内容,您可以使用named_scopes:

在模型中添加:

named_scope :since_subscription, lambda { |subscription_date| { :conditions => ['created_at > ?', subscription_date] }

有了这个,您可以像这样找到您想要的内容:

user.tickets.since_subscription 3.days.ago

user.tickets.since_subscription user.subscription_date

(当然您需要用户模型中的 subscription_date 列)。

您可以找到更多示例 这里

如果您不想使用named_scopes,您可以通过以下方式找到您想要的内容:

user.tickets.all(:conditions => ['created_at > ?', subscription_date]) 

To have something like has_many :tickets_since_subscription you can use named_scopes:

In model add:

named_scope :since_subscription, lambda { |subscription_date| { :conditions => ['created_at > ?', subscription_date] }

With this, you can find what you want like this:

user.tickets.since_subscription 3.days.ago

or

user.tickets.since_subscription user.subscription_date

(of course you need subscription_date column in user model).

You can find more examples here.

If you don't want to use named_scopes you can find what you want with this:

user.tickets.all(:conditions => ['created_at > ?', subscription_date]) 
一生独一 2024-07-16 10:11:03

我认为您正在寻找 has_many 上的 :foreign_key 选项。 这应该允许您指定外键不是 user_id,而是 user_login,而无需调整查找器逻辑。

有关更多详细信息,请参阅 ActiveRecord has_many 文档。

I think you are looking for the :foreign_key option on has_many. That should allow you to specify that the foreign key is not user_id, but user_login, without adjusting the finder logic.

See the ActiveRecord has_many documentation for more details.

风铃鹿 2024-07-16 10:11:03

只是回答自己,以防没有更好的解决方案。 我找不到 has_many 关联的解决方案,所以我最终创建了一个简单的查找器方法。 一点都不好:它确实允许我调用 some_user.tickets,但它并没有给我带来 has_many 关联的所有好处(即清除、删除、<<、...协会本身的方法)。

def tickets
    return Ticket.find(:all, :conditions=>["user_login = ?", login])
end

我仍然希望有人能提出更好的解决方案。

Just answering to myself, in case there is no better solution. I could not find a solution with the has_many association, so I ended up creating a simple finder method. Not great at all: it does allow me to call some_user.tickets, but it does not give me all the benefits of the has_many associations (namely the clear, delete, <<,... methods on the association itself).

def tickets
    return Ticket.find(:all, :conditions=>["user_login = ?", login])
end

I am still hoping that someone will come up with a better solution.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文