I have a Student model and that model has_many subscriptions. I need to return all students who have subscriptions. this object must contain only the student's name, email, phone and status, in addition to the subscription id and recurrence_code. I thought something more or less like this but I can't succeed in the query:

 students = Student.all.includes(:subscriptions)
 students.each do |student|
  student.select(:id, :name, :email, :phone, :status,  subscription: {
    methods: [:id, :recurrency_code]}  
 end

CodePudding user response：

This is a classic inner join scenario

class Student < ApplicationRecord
  has_many :subscriptions
end

class Subscription < ApplicationRecord
  belongs_to :student
end

I find it helpful to break these problems into steps:

"Only Students where a Subscription record is present" is a standard inner join:

Student.joins(:subscriptions).uniq

"object must contain only the student's name, email, phone and status"

Student.joins(:subscriptions).select(:name, :email, :phone, :status).uniq

"in addition to the subscription id and recurrence_code"

students = Student.joins(:subscriptions)
                  .select(
                    'students.name, students.email,'\
                    'students.phone, students.status, '\
                    'subscriptions.id as subscription_id,'\
                    'subscriptions.recurrence_code as subscription_recurrence_code'
                  )

A few notes:

1. Using select with joins

@vee's SO Answer here points out:

If the column in select is not one of the attributes of the model on which the select is called on then those columns are not displayed. All of these attributes are still contained in the objects within AR::Relation and are accessible as any other public instance attributes.

This means if you load an individual record (e.g. students.first), you will only see the Student attributes by default. But you can still access the Subscription attributes by the as name you set in the query. E.g.:

students.first.subscription_recurrence_code

2. Use .uniq to eliminate duplicates.

Because of the has_many relationship, the query Student.joins(:subscriptions) will return a record for each subscription, which means each student will appear in the result the same number of times as they have subscriptions. Calling .uniq (short for unique) will remove duplicates from the result.

CodePudding user response：

I'm agree with the Chiperific response, but I disagree to use the uniq method because it doesn't call the 'DISTINCT' in the SQL query.

Rails: uniq vs. distinct

For me it's better to use distinct. So the query could be as this:

Student.joins(:subscriptions).distinct.select(
  :name, :email, :phone, :status, 
  'subscriptions.id AS subscription_id',
  'subscriptions.recurrence_code'
)