Rails query, based on a scope from an unrelated model

rails select from multiple tables
rails scope
rails joins
rails find by attribute
activerecord select only certain columns
rails left outer join
activerecord::relation
rails 6 activerecord query

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

Since activerecord-4.2.8 scopes are being applied to unrelated , class Team < ActiveRecord::Base has_many :users def default scope of a model) certainly implies that Team.joins(:users) should be affected� Every time that you call on a scope, you query the database. When you iterate over these queries, you create what is called the n+1 query — a query that eventually slows your application to a crawl.


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

Active Record Query Interface — Ruby on Rails Guides, How to perform various calculations on Active Record models. If your default scope contains an order method, first will return the first record according to this� My favourite part of Rails is clearly ActiveRecord’s scopes. Their expressiveness and their reusability is simply great. Say you have a users table, with a profile association. If you need to


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.

Delegating to Query Objects through ActiveRecord scopes, The following ActiveRecord model has a scope returning popular featured videos . Okay, it's class Video < ActiveRecord::Base scope :featured_and_popular, Note that this is a bit simplified, I've left out the irrelevant parts:. Reusable query abstractions with class methods. Well, all the queries written so far are pretty long. When they get complicated or are intended to be reused through our code it is convenient to give them names. In older versions of rails, you should use the ActiveRecord::Base#scope class method to create such reusable queries. You are able to


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.

find (ActiveRecord::Base), default_scope Icon_protected_sm; Importance_0_sm Use Model.find(:first, * args) or its shortcut Model.first(*args). to the other tables in your :conditions or : options parameters, even if the test or sort is irrelevant. Check ActiveRecord:: Base.method_missing for documentation on the family of “magic” find methods� default_scope automatically creates the basis of all queries. Practically, default_scope is often not recommended in Rails. Implicit Behavior Change. Based on my experience, the biggest problem of the default_scope is applied implicitly. If the writer of the default_scope is different from the model user, the behavior must look weird. Model users will see a query they do not write unexpectedly.


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)

Rails Database Best Practices, class Project < ActiveRecord::Base The scope does not return an ActiveRecord Relation, therefore it is not chainable nor can it ActiveRecord has to parse the query results and create AR model objects for each one, and� The above calls to scope define class methods Shirt.red and Shirt.dry_clean_only. Shirt.red, in effect, represents the query Shirt.where(color: 'red'). You should always pass a callable object to the scopes defined with scope. This ensures that the scope is re-evaluated each time it is called.


Includes vs Joins in Rails: When and where?, Now with any e-commerce platform there is bound to be a lot of database relations and models relying on each other for, you guessed it: data,� Active Record Query InterfaceThis guide covers different ways to retrieve data from the database using Active Record.After reading this guide, you will know: How to find records using a variety of methods and conditions. How to specify the order, retrieved attributes, grouping, and other properties of the found records. How to use eager loading to reduce the number of database queries needed


Understanding Rails Eager-Loading - DEV, Tagged with ruby, rails, activerecord, sql. This post assumes that you understand model associations, are comfortable with JOINs, and are� Steps to reproduce When you define a scope like this class User belongs_to :team has_and_belongs_to_many :interests def self.a_scope # Unrelated query that will raise ActiveRecord::StatementInvalid: ids = Team.joins(:users).ids # PROBLEM


The SQL Alternative To Counter Caches | by Eric Anderson, The counter cache feature has been in Rails since the early days. This adds weight to your models even when operating on sections of the app unrelated to Depending on the nature of your app, counter caches may be write heavy. To do this we define a scope on our model which adds a sub-query� “north” is the scope that we want it to query (or search) We can manually input the SQL query in our SQLiteStudio. The return result as shown in the Figure 4.3.3 will match the result in Figure 4.4.1, providing that you have the same database. If you are unable to query, please ensure that your syntax are right.