ruby-on-rails - Rails 查询，基于来自不相关模型的范围

Question

我想找到一个用户的所有 convos哪里没有 connect

我有一个 convos表，带有 sender_id和 recipient_id它们都是对用户 ID 的引用

# app/models/user.rb

has_many :convos, ->(user) {
  unscope(:where).where("sender_id = :id OR recipient_id = :id", id: user.id)
}

请注意，convo 可以属于 sender_id 或 receiver_id 的用户。

# app/models/convo.rb

class Convo < ApplicationRecord
  belongs_to :sender, :foreign_key => :sender_id, class_name: 'User'
  belongs_to :recipient, :foreign_key => :recipient_id, class_name: 'User'

  has_many :msgs, dependent: :destroy

  validates_uniqueness_of :sender_id, :scope => :recipient_id

  scope :involving, -> (user) do
    where("convos.sender_id =? OR convos.recipient_id =?",user.id,user.id)
  end

  scope :between, -> (sender_id,recipient_id) do
    where("(convos.sender_id = ? AND convos.recipient_id =?) OR (convos.sender_id = ? AND convos.recipient_id =?)", sender_id,recipient_id, recipient_id, sender_id)
  end
end

Connect table 上有一个 requestor_id和 requestee_id它们都是对用户 ID 的引用。
连接模型

class Connect < ApplicationRecord
  belongs_to :requestor, :foreign_key => :requestor_id, class_name: 'User'
  belongs_to :requestee, :foreign_key => :requestee_id, class_name: 'User'


  scope :between, -> (requestor_id,requestee_id) do
    where("(connects.requestor_id = ? AND connects.requestee_id =?) OR (connects.requestor_id = ? AND connects.requestee_id =?)", requestor_id,requestee_id, requestee_id, requestor_id)
  end
end

我想找到一个用户的所有 convos哪里没有 connect
我试过这样的事情:

user = User.first
user.convos.where.not(Connect.between(self.requestor_id, self.requestee_id).length > 0 )

# NoMethodError (undefined method `requestor_id' for main:Object)

user.convos.where.not(Connect.between(convo.requestor_id, convo.requestee_id).length > 0 )

# undefined local variable or method `convo' for main:Object

然后我尝试完全不引用用户，只是尝试在没有连接的情况下获取所有 convos。

Convo.where("Connect.between(? ,?) < ?)", :sender_id, :recipient_id, 1)

# ActiveRecord::StatementInvalid (SQLite3::SQLException: near "between": syntax error: SELECT "convos".* FROM "convos" WHERE (Connect.between('sender_id' ,'recipient_id') < 1)))

Convo.where("Connect.between(? ,?) < ?)", self.sender_id, self.recipient_id, 1)

# NoMethodError (undefined method `sender_id' for main:Object)

在不存在连接的情况下获取所有用户的对话的最佳方法是什么？

更新

这有效，并且是我正在寻找的，但显​​然这是无用的，我想了解如何在 1 个电话中获得此信息。

@og_connections = []
current_user.convos.each do |convo|
   if Connect.between(convo.sender_id, convo.recipient_id).length === 0
     @og_connections.push(current_user.id === convo.sender_id ? convo.recipient_id : convo.sender_id)
   end
end
@connections = User.select(:id, :first_name, :slug).where(id: @og_connections, status: 'Active')

Answer 1

您可以使用 LEFT JOIN获取在 id 之间匹配的用户行和 convos.sender_id和 convos.recipient_id不是 NULL ，但它们之间的匹配connections.requester_id和 connections.requestee_id是 NULL :

SELECT *
FROM users
LEFT JOIN connects
ON users.id IN (connects.requester_id, connects.requestee_id)
LEFT JOIN convos
ON users.id IN (convos.sender_id, convos.recipient_id)
WHERE connects.requester_id IS NULL AND
      connects.requestee_id IS NULL AND
      convos.sender_id      IS NOT NULL AND
      convos.recipient_id   IS NOT NULL

AR实现:

User.joins('LEFT JOIN connects ON users.id IN (connects.requester_id, connects.requestee_id)
            LEFT JOIN convos   ON users.id IN (convos.sender_id, convos.recipient_id)')
    .where(connects: { requester_id: nil, requestee_id: nil })
    .where.not(convos: { sender_id: nil, recipient_id: nil })

考虑这样的数据库结构:

db=# \d+ users
                                                              Table "public.users"
   Column   |              Type              | Collation | Nullable |              Default              | Storage  | Stats target | Description
------------+--------------------------------+-----------+----------+-----------------------------------+----------+--------------+-------------
 id         | bigint                         |           | not null | nextval('users_id_seq'::regclass) | plain    |              |
 name       | character varying              |           |          |                                   | extended |              |
 created_at | timestamp(6) without time zone |           | not null |                                   | plain    |              |
 updated_at | timestamp(6) without time zone |           | not null |                                   | plain    |              |
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)

db=# \d+ convos
                                                              Table "public.convos"
    Column    |              Type              | Collation | Nullable |              Default               | Storage | Stats target | Description
--------------+--------------------------------+-----------+----------+------------------------------------+---------+--------------+-------------
 id           | bigint                         |           | not null | nextval('convos_id_seq'::regclass) | plain   |              |
 sender_id    | integer                        |           |          |                                    | plain   |              |
 recipient_id | integer                        |           |          |                                    | plain   |              |
 created_at   | timestamp(6) without time zone |           | not null |                                    | plain   |              |
 updated_at   | timestamp(6) without time zone |           | not null |                                    | plain   |              |
Indexes:
    "convos_pkey" PRIMARY KEY, btree (id)

db=# \d+ connects
                                                              Table "public.connects"
    Column    |              Type              | Collation | Nullable |               Default                | Storage | Stats target | Description
--------------+--------------------------------+-----------+----------+--------------------------------------+---------+--------------+-------------
 id           | bigint                         |           | not null | nextval('connects_id_seq'::regclass) | plain   |              |
 requestor_id | integer                        |           |          |                                      | plain   |              |
 requestee_id | integer                        |           |          |                                      | plain   |              |
 created_at   | timestamp(6) without time zone |           | not null |                                      | plain   |              |
 updated_at   | timestamp(6) without time zone |           | not null |                                      | plain   |              |
Indexes:
    "connects_pkey" PRIMARY KEY, btree (id)

有以下记录:

db=# select * from users;
 id | name |         created_at         |         updated_at
----+------+----------------------------+----------------------------
  1 | seb  | 2019-11-27 09:59:53.762911 | 2019-11-27 09:59:53.762911
  2 | sab  | 2019-11-27 09:59:55.455096 | 2019-11-27 09:59:55.455096
  3 | foo  | 2019-11-27 10:07:19.760675 | 2019-11-27 10:07:19.760675
  4 | bar  | 2019-11-27 10:07:36.18696  | 2019-11-27 10:07:36.18696
  5 | meh  | 2019-11-27 10:07:38.465841 | 2019-11-27 10:07:38.465841
(5 rows)

db=# select * from convos;
 id | sender_id | recipient_id |         created_at         |         updated_at
----+-----------+--------------+----------------------------+----------------------------
  1 |         1 |            2 | 2019-11-27 10:09:36.742426 | 2019-11-27 10:09:36.742426
  2 |         1 |            3 | 2019-11-27 10:09:40.555118 | 2019-11-27 10:09:40.555118
(2 rows)

db=# select * from connects;
 id | requestor_id | requestee_id |         created_at         |         updated_at
----+--------------+--------------+----------------------------+----------------------------
  1 |            1 |            2 | 2019-11-27 10:07:07.76146  | 2019-11-27 10:07:07.76146
  2 |            2 |            1 | 2019-11-27 10:07:11.380084 | 2019-11-27 10:07:11.380084
  3 |            1 |            4 | 2019-11-27 10:07:47.892944 | 2019-11-27 10:07:47.892944
  4 |            5 |            1 | 2019-11-27 10:07:51.406224 | 2019-11-27 10:07:51.406224
(4 rows)

以下查询将仅返回第二个 convo，因为 id 为 3 的用户没有任何连接。

SELECT convos.*
FROM convos
LEFT JOIN users
ON users.id IN (convos.sender_id, convos.recipient_id)
LEFT JOIN connects
ON users.id IN (connects.requestor_id, connects.requestee_id)
WHERE connects.requestor_id IS NULL AND connects.requestee_id IS NULL

 id | sender_id | recipient_id |         created_at         |         updated_at         | id | name |         created_at         |         updated_at         | id | requestor_id | requestee_id | created_at | updated_at
----+-----------+--------------+----------------------------+----------------------------+----+------+----------------------------+----------------------------+----+--------------+--------------+------------+------------
  2 |         1 |            3 | 2019-11-27 10:09:40.555118 | 2019-11-27 10:09:40.555118 |  3 | foo  | 2019-11-27 10:07:19.760675 | 2019-11-27 10:07:19.760675 |    |              |              |            |
(1 row)

Rails 查询可以是这样的:

Convo
  .joins('LEFT JOIN users ON users.id IN (convos.sender_id, convos.recipient_id)
          LEFT JOIN connects ON users.id IN (connects.requestor_id, connects.requestee_id)')
  .where(connects: { requestor_id: nil, requestee_id: nil })