SQL查询通过相关模型
问题描述:
我的问题是选择一个用户的朋友?SQL查询通过相关模型
红宝石如下:
class User < ActiveRecord::Base
has_many :relations
end
class Relation < ActiveRecord::Base
belongs_to :initiator, class_name: "User"
belongs_to :initiatee, class_name: "User"
end
class CreateRelations < ActiveRecord::Migration
def change
create_table :relations do |t|
t.references :initiator
t.references :initiatee
t.boolean :is_confirmed, default: false
t.integer :type
end
end
end
答
你会遇到麻烦,因为Rails的预计type
用于Single Table Inheritance。您还需要告诉Rails,relations
上的ID不是user_id
,这将是has_many
的默认值。既然你有两个关系方向,你就需要声明两者。
has_many :outgoing_relations, class_name: 'Relation', foreign_key: 'initiator_id'
has_many :incoming_relations, class_name: 'Relation', foreign_key: 'initiatee_id'
从那里,做最简单的事情就是写一个聚合了其他用户的方法:
def friends(params = {})
outgoing_relations.where(params).includes(:initiatee).map(&:initiatee) +
incoming_relations.where(params).includes(:initiator).map(&:initiator)
end
> User.first.friends(is_confirmed: true, kind: 0)
=> [#<User id: 2, created_at: "2015-08-28 15:11:12", updated_at: "2015-08-28 15:11:12">]
在直SQL,你可以很容易地UNION
一对夫妇查询拉您想要的其他用户ID,然后按照您喜欢的方式进行操作。
SELECT initiatee_id AS id
FROM relations
WHERE initiator_id = 2
AND kind = 0
AND is_confirmed
UNION
SELECT initiator_id AS id
FROM relations
WHERE initiatee_id = 2
AND kind = 0
AND is_confirmed
;
id
----
1
3
这是数据我上运行:
SELECT * FROM users;
id | created_at | updated_at
----+----------------------------+----------------------------
1 | 2015-08-28 15:11:10.631187 | 2015-08-28 15:11:10.631187
2 | 2015-08-28 15:11:12.911575 | 2015-08-28 15:11:12.911575
3 | 2015-08-28 15:14:27.762946 | 2015-08-28 15:14:27.762946
SELECT * FROM relations;
id | initiator_id | initiatee_id | is_confirmed | kind
----+--------------+--------------+--------------+------
1 | 1 | 2 | t | 0
2 | 3 | 2 | t | 0
你想要的SQL,而不是轨道的方法呢? –