我有一个 Bug 类,其中包含分析师和提交者,他们在用户表中。
class Bug < ApplicationRecord
belongs_to :analyst, class_name: 'User'
belongs_to :committer, class_name: 'User'
...
end
我有一个搜索,可以包括搜索分析师、提交者或两者兼而有之。
如何知道活动记录将用于表的表别名?
如果我像这样搜索分析师,我得到的 SQL,
analyst = 'abenge'
Bug.joins(:analyst).where(users: {username: analyst}).to_sql
=> "SELECT `bugs`.* FROM `bugs`
INNER JOIN `users` ON `users`.`id` = `bugs`.`analyst_id`
WHERE `users`.`username` = 'abenge'"
同样,如果我搜索提交者,我得到的 SQL,
analyst = 'amcdonnell'
Bug.joins(:committer).where(users: {username: committer}).to_sql
=> "SELECT `bugs`.* FROM `bugs`
INNER JOIN `users` ON `users`.`id` = `bugs`.`committer_id`
WHERE `users`.`username` = 'amcdonnell'"
但是,如果我同时搜索两者,则 where 调用不能同时使用users
。 表被别名,我需要使用别名。
Bug.joins(:analyst, :committer)
.where(users: {username: committer})
.where(committers_bugs: {username: committer}).to_sql
=> "SELECT `bugs`.* FROM `bugs`
INNER JOIN `users` ON `users`.`id` = `bugs`.`analyst_id`
INNER JOIN `users` `committers_bugs` ON `committers_bugs`.`id` = `bugs`.`committer_id`
WHERE `users`.`username` = 'abenge'
AND `committers_bugs`.`cvs_username` = 'amcdonnell'"
所以我的问题是,"我怎么知道在我的 where 子句中使用哪个键作为提交者的用户名,在一种情况下它是users
的,而在另一种情况下它是committers_bugs
的?
我希望代码是这样的:
relation = Bug
analyst = params['analyst']
if analyst.present?
relation = relation.joins(:analyst).where(user: {username: analyst})
end
committer = params['committer']
if committer.present?
relation = relation.joins(:committer).where(committers_bugs: {username: committer})
end
问题是,如果他们同时搜索两者,上述方法有效。 但是,如果他们搜索提交者而不搜索分析师,则活动记录使用users
作为连接到committer
的表名。
如果每个"Bug"都与两个用户相关,则 Bug 表需要通过两个不同的外键进行关联:
迁移
class CreateBug < ActiveRecord::Migration
def change
create_table :bugs do |t|
# ...
t.references :analyst_id, index: true, foreign_key: {to_table: :users}
t.references :committer_id, index: true, foreign_key: {to_table: :users}
end
end
end
型
class Bug < ActiveRecord
belongs_to :analyst, class_name: 'User', foreign_key: 'analyst_id'
belongs_to :committer, class_name: 'User', foreign_key: 'committer_id'
end
提交者和分析师都只是来自同一个表的 SQL 别名。如果您在 WHERE 语句中使用它们中的任何一个,结果将始终相同。如果要将条件限制为特定的 JOIN,则必须将该条件添加到 ON 语句中。
我喜欢用来执行此操作的一个选项是向关系添加 lambda:
class Bug < ActiveRecord
belongs_to :analyst, -> { where(username: 'Haruki Murakami') }, class_name: 'User'
end
这返回
SELECT "bugs".*
FROM "bugs"
INNER JOIN "users"
ON "bugs"."user_id" = "users"."id" AND "users"."username" = $1
LIMIT $2
这样做的问题是,只要我有搜索,你就不能将参数传递给这个 lambda,但你仍然可以做一些不太漂亮但功能更强大的事情,例如:
Bug.joins("LEFT JOIN users ON bugs.user_id = user.id AND users.username = ?", params['committer'])
。在您的示例中,必须实现为:
relation = Bug
analyst = params['analyst']
if analyst.present?
relation = relation.joins("LEFT JOIN users A ON bugs.analyst_id = A.id AND A.username = ?", analyst)
end
committer = params['committer']
if committer.present?
relation = relation.joins("LEFT JOIN users C ON bugs.committer_id = C.id AND C.username= ?", committer)
end
也许您想将此代码移动到您的模型中,例如:
class Bug < ActiveRecord
def self.who_analyses(name)
Bug.joins("LEFT JOIN users A ON bugs.analyst_id = A.id AND A.username = ?", name)
end
def self.who_have_commited(name)
Bug.joins("LEFT JOIN users C ON bugs.committer_id = C.id AND C.username= ?", name)
end
end
analyst = params['analyst']
committer = params['committer']
if analyst.present?
relation = relation.who_analyses(analyst)
end
if committer.present?
relation = relation.who_have_commited(committer)
end