Rails query, based on a scope from an unrelated model

Question

I want to find all of a user's convos where there is not a connect

I have a convos table, with a sender_id and recipient_id which are both references to a user id

# app/models/user.rb

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

Note the convo can belong to a user that is either sender_id OR recipient_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 has a requestor_id and requestee_id which are both references to a user id. Connect model

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

I want to find all of a user's convos where there is not a connect

I've tried something like:

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

---

Then I tried without referencing a user at all, and just tried to get all convos without a connect.

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)

---

What is the best way to get all the user's convos where a connect doesn't exist?

UPDATE

This works, and is what I'm looking for, but obviously this is trashy, and I'd like to understand how get this in 1 call.

@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

You can use LEFT JOIN to get the users rows where their match between id and convos.sender_id and convos.recipient_id is not NULL, but their match between connections.requester_id and connections.requestee_id is 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 implementation:

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 })

---

Considering a DB structure like this:

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)

With the following records:

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)

The following query will return only the second convo, because user with id 3 doesn't have any connect.

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)

The Rails query for that can be this:

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 })

Answer 2

class Convo < ApplicationRecord
  def self.no_connects(user_id = nil)
    q = joins('
      LEFT JOIN connects ON
        sender_id IN (connects.requestor_id, connects.requestee_id)
          OR
        recipient_id IN (connects.requestor_id, connects.requestee_id)
    ')
    q = q.where('connects.requestor_id IS NULL AND connects.requestee_id IS NULL')
    q = q.where("convos.sender_id = :user_id OR convos.recipient_id = :user_id", user_id: user_id) if user_id
    q
  end
end

To get all the convos without connects

Convo.no_connects

For single user

Convo.no_connects(current_user.id)

Answer 3

I'll first write the SQL query to do so. In your case you perhaps want

SELECT convos.*
FROM convos
WHERE (sender_id = :user_id
        AND NOT EXISTS (
         SELECT 1
         FROM connects
         WHERE (requestor_id = sender_id AND requestee_id = recipient_id) OR (requestor_id = recipient_id AND requestee_id = sender_id)
        ))
  OR        
    (recipient_id = :user_id
       AND NOT EXISTS (
         SELECT 1
         FROM connects
        WHERE (requestor_id = recipient_id AND requestee_id = sender_id) OR (requestor_id = sender_id AND requestee_id = recipient_id)
    ))

This can be then converted into AR query.

Answer 4

Answer with current setup

If you're looking for just current_user, you'll want to start with their convos, do a left join to connects, and select the rows where connects is NULL. With your table setup, we'll have to do this joins manually on the possible user_id combinations:

current_user.convos.joins("
  LEFT JOIN connects ON
    (connects.requestor_id = convos.sender_id AND connects.requestee_id = convos.recipient_id)
    OR
    (connects.requestor_id = convos.recipient_id AND connects.requestee_id = convos.sender_id)
  ").where(connects: {id: nil})

The left joins gives you any connects that are between the same two users as the convo, which is necessarily involving current_user since we started with current_user.convos. From there we filter down to only rows where the connects fields are NULL, getting us rows with a convo that does not have a matching connect.

---

Suggestion

That much raw SQL is a bit of code smell in a Rails app, and it's because of what we're trying to do here with the models set up as they are. I'd suggest refactoring the data models to make the queries easier. Two ideas come to mind:

1. Always create a symmetrical record for a connect and a convo, so you can look up by a single column instead of using all the ORs. That is, whenever you create a connect between user 1 and user 2, also create one between user 2 and user 1. More bookkeeping, since you'd have to destroy and edit them together as well. But it lets you . define simple associations without all the hoops.
2. Use a separate table to refer to unique user-pairs (order doesn't matter). To do this, create a UserPair model with user_1_id and user_2_id, where user_1_id is always set to the lower of the two user ids. That way, a convo can be more easily identified by a user_pair_id, a UserPair can has_many :convos and has_many: connects, and you can to a straight rails join between convos -> user_pairs -> connects.

The models in 2 would look something like

class UserPair < ApplicationRecord
  belongs_to :user_1, class_name: "User"
  belongs_to :user_2, class_name: "User"

  before_save :sort_users

  scope :between, -> (user_1_id,user_2_id) do
    # records are always saved in sorted id order, so sort before querying
    user_1_id, user_2_id = [user_1_id, user_2_id].sort
    where(user_1_id: user_1_id, user_2_id: user_2_id)
  end

  # always put lowest id first for easy lookup
  def sort_users
    if user_1.present? && user_2.present? && user_1.id > user_2.id
      self.user_1, self.user_2 = user_2, user_1
    end
  end
end

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

  before_validation :set_user_pair

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

  # since UserPair records are always user_id sorted, we can just use
  # that model's scope here without need to repeat it, using `merge`
  scope :between, -> (sender_id,recipient_id) do
    joins(:user_pair).merge(UserPair.between(sender_id, recipient_id))
  end

  def set_user_pair
    self.user_pair = UserPair.find_or_initialize_by(user_1: sender, user_2: recipient)
  end
end

Answer 5

So if I understand correctly, from the list of users a user has a conversation with, you want the list of users that they do not have a connection with.

In a simple way this could be something like:

users_conversed_with = user.convos.map{|c| [c.sender_id, c.recipient_id]}.flatten.uniq 
users_connected_with = user.connections.map{|c| c.requestor_id, c.requestee_id}.flatten.uniq

Both sets also contain the user.id, but we can ignore that, because we are interested in the difference: that would be the set of people we conversed with, without connection (and because user.id will be in both, unless one of them is empty, we do not have to separately remove user.id from those sets).

users_not_connected_with = users_conversed_with - users_connected_with 

This is not an optimal approach, because we do two queries, retrieve all the user-ids from the database, to then discard probably most of the retrieved data. We could improve this by creating a custom query, and let the database do the work for us, like so

 sql = <<-SQL
   (select distinct user_id from 

     (select sender_id as user_id from convos where sender_id=#{user.id} or recipient_id=#{user.id}
      union
      select recipient_id as user_id from convos where sender_id=#{user.id} or recipient_id=#{user.id}
     )
   )
   except
   (
   (select distinct user_id from          
     (select requestor_id as user_id from connections where requestor_id=#{user.id} or requestee_id=#{user.id}
      union
      select requestee_id as user_id from convos where requestor_id=#{user.id} or requestee_id=#{user.id}
     )
   ) 
 SQL  

 result = Convo.connection.execute(sql)
 users_ids_in_convo_without_connection = result.to_a.map(&:values).flatten 

But if performance is not an issue, your code has the advantage of being very readable and clearer in it's intention.