ruby - better way to use rails activerecord query - Stack Overflow

I have to get primary users and secondary users query I have written as below:

users = User.where(organization_id: 1234)
primary_user = users.find{ |user| !user.secondary }
secondary_users = users.select(&:secondary)
return primary_user, secondary_users

will this above query fires only single query on database or it will call 2 seperate queries for find and select.

Here I have secondary flag which is boolean and will be false in case of primary user. And an organization will have only one primary user.

or it can be use it as below?

# making it as an array. 
users = User.where(organization_id: 1234).to_a
primary_user = users.find{ |user| !user.secondary }
secondary_users = users.select(&:secondary)
return primary_user, secondary_users

I have to get primary users and secondary users query I have written as below:

users = User.where(organization_id: 1234)
primary_user = users.find{ |user| !user.secondary }
secondary_users = users.select(&:secondary)
return primary_user, secondary_users

will this above query fires only single query on database or it will call 2 seperate queries for find and select.

Here I have secondary flag which is boolean and will be false in case of primary user. And an organization will have only one primary user.

or it can be use it as below?

# making it as an array. 
users = User.where(organization_id: 1234).to_a
primary_user = users.find{ |user| !user.secondary }
secondary_users = users.select(&:secondary)
return primary_user, secondary_users

• ruby-on-rails
• ruby
• optimization

Share Follow edited Jan 30 at 13:02 Amol Mohite asked Jan 30 at 6:14 Amol MohiteAmol Mohite 64233 silver badges1212 bronze badges 3

• Would you mind adding more context? What defines a primary user, is every non secondary user a primary user? Is there only one primary? Why are secondary users defined by secondary_manager and on by the secondary flag that defines the primary when false? Why do you store the mobile_no of the primary and the parent_id of the secondaries and returns those together, but not the users itself? – spickermann Commented Jan 30 at 7:09
• Updated details in the questions @spickermann – Amol Mohite Commented Jan 30 at 13:03
• You can check how many queries are run in your logs. – Schwern Commented Jan 30 at 22:20

Add a comment  | 

3 Answers 3

Sorted by: Reset to default 2

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

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.

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.

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.

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

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:

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:

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:

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

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:

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