I have to get primary users and secondary users query I have written as below:
users = User.where(anization_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 anization will have only one primary user.
or it can be use it as below?
# making it as an array.
users = User.where(anization_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(anization_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 anization will have only one primary user.
or it can be use it as below?
# making it as an array.
users = User.where(anization_id: 1234).to_a
primary_user = users.find{ |user| !user.secondary }
secondary_users = users.select(&:secondary)
return primary_user, secondary_users
Share
Improve this question
edited Jan 30 at 13:02
Amol Mohite
asked Jan 30 at 6:14
Amol MohiteAmol Mohite
6423 silver badges12 bronze badges
3
|
3 Answers
Reset to default 2I 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(anization_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 anization 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 :anizations, :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: :anizations_as_primary_user
end
class User < ApplicationRecord
belongs_to :anization
has_many :anizations_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:
anzation = Organzation.eager_load(:primary_user)
.find(1234)
primary = .primary_user
secondary = .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 anization.primary_user_id = users.id
clause:
o, u = Organization.arel_table, User.arel_table
User.joins(:anization)
.where(anization_id: 1234)
.where.not(o[:primary_user].eq(u[:id]))
You could also filter the primary user out of the collection in Rubyland:
anzation = Organzation.eager_load(:primary_user, :users)
.find(1234)
primary = .primary_user
secondary = .users.reject { |user| user.id == primary.id }
Your current approach has some inefficiencies:
- User.where(anization_id: 1234) creates an ActiveRecord::Relation
- Using find and select on that relation will load all records into memory first and then filter them in Ruby, which is less efficient
- 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(anization_id: anization_id, secondary: false)
secondary_users = User.where(anization_id: anization_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
secondary
user a primary user? Is there only one primary? Why are secondary users defined bysecondary_manager
and on by thesecondary
flag that defines the primary whenfalse
? Why do you store themobile_no
of the primary and theparent_id
of the secondaries and returns those together, but not the users itself? – spickermann Commented Jan 30 at 7:09