Does using Ruby Enumerable methods on ActiveRecord relations trigger multiple SQL queries?

I would load all records in only one query, no matter if primary or secondary. And then separate and assign them to different variables:

RUBYCopied
primary_user, *secondary_user = User
  .where(organization_id: 1234).order('secondary ASC NULLS FIRST')

This query returns all user sorted by the secondary column, with the user with secondary == false or nil records first, then followed by the users with secondary == true.

Because there is only on primary user, this user is now at the top of the list. The primary_user, *secondary_user = assignment, assigns the first record to the primary_user variable and all others to the secondary_users variable.

This should work with PostgreSQL and might need to be slightly changed when you are using a different DB, because they might treat sorting by boolean with nil record differently. And it only works when there is only one primary, with multiple primary a different approach would be to be taken.

Your current approach has some inefficiencies:

1. User.where(organization_id: 1234) creates an ActiveRecord::Relation
2. Using find and select on that relation will load all records into memory first and then filter them in Ruby, which is less efficient
3. Whether you use .to_a or not, the current approach loads all users and then filters them in Ruby memory

Here's a more efficient solution that will execute a single optimized SQL query for each type of user:

RUBYCopied
primary_user = User.find_by(organization_id: organization_id, secondary: false)

secondary_users = User.where(organization_id: organization_id, secondary: true)

For the primary user:

• Uses find_by which adds a LIMIT 1 to the query
• Directly queries for secondary: false
• Returns nil if no primary user is found

For secondary users:

• Uses where to get all secondary users
• Keeps the query lazy (doesn't execute until needed)

I suggest using this approach because

• Each query is optimized at the database level
• Minimal memory usage

There is a much better way to approach the problem.

If an organization can only have one primary user just add a foreign key column pointing to that user instead.

RUBYCopied
class AddPrimaryUserToOrganizations < ActiveRecord::Migration[8.0]
  def change
    add_reference :organizations, :primary_user, 
      null: false, 
      foreign_key: { to_table: :users }
  end
end

Boolean columns in general are a code smell. Think in terms of relations between your tables instead of flags.

I'll leave fixing the existing data to a separate question.

RUBYCopied
class Organization < ApplicationRecord
  has_many :users
  belongs_to :primary_user, 
    class_name: 'User',
    inverse_of: :organizations_as_primary_user
end

RUBYCopied
class User < ApplicationRecord
  belongs_to :organization
  has_many :organizations_as_primary_user,
    foreign_key: :primary_user_id,
    class_name: 'Organization',
    inverse_of: :primary_user
end 

The most straight forward way to get the data is to just do two database queries:

RUBYCopied
organzation = Organzation.eager_load(:primary_user)
                         .find(1234)
primary   = org.primary_user
secondary = org.users.where.not(id: primary.id)

If you just want to get "secondary users" in one database query you can do it by adding a WHERE NOT organization.primary_user_id = users.id clause:

RUBYCopied
o, u = Organization.arel_table, User.arel_table
User.joins(:organization)
    .where(organization_id: 1234)
    .where.not(o[:primary_user].eq(u[:id]))

You could also filter the primary user out of the collection in Rubyland:

RUBYCopied
organzation = Organzation.eager_load(:primary_user, :users)
                         .find(1234)
primary   = org.primary_user
secondary = org.users.reject { |user| user.id == primary.id }