How to build ActiveRecord join query with specific columns

Question

I have two models:

class Friend < ApplicationRecord
  belongs_to :user
end

class User < ApplicationRecord
  has_many :friends
end

The schema of the models is as follows:

create_table "friends" do |t|
  t.integer  "user_id"
  t.integer  "friends_max"
  t.integer  "friends_count",                          null: false
  t.datetime "created_at",                             null: false
  t.datetime "updated_at",                             null: false
end

create_table "users" do |t|
  t.string   "name"
  t.string   "email"
  t.datetime "created_at"
  t.datetime "updated_at"
end

Assuming I have 3 user records that are the following:

[id: 1, name: "John", email: "john@gmail.com", created_at: "2017-03-29 02:33:41", updated_at: "2017-03-29 02:33:11"]
[id: 2, name: "Rob", email: "rob@gmail.com", created_at: "2017-03-21 02:33:41", updated_at: "2017-03-29 02:33:33"]
[id: 3, name: "Tommy", email: "tommy@gmail.com", created_at: "2017-02-29 02:33:41", updated_at: "2017-03-29 02:33:32"]

And 6 friends records:

[id: 52, user_id: 1, friends_max: 5, friends_count: 2, created_at: "2017-05-01 21:10:37", updated_at: "2017-05-01 21:10:37"]
[id: 53, user_id: 1, friends_max: 5, friends_count: 2, created_at: "2017-05-01 21:10:37", updated_at: "2017-05-01 21:10:37"]
[id: 54, user_id: 2, friends_max: 5, friends_count: 3, created_at: "2017-05-01 21:10:37", updated_at: "2017-05-01 21:10:37"]
[id: 55, user_id: 2, friends_max: 2, friends_count: 2, created_at: "2017-05-01 21:10:37", updated_at: "2017-05-01 21:10:37"]
[id: 56, user_id: 2, friends_max: 4, friends_count: 2, created_at: "2017-05-01 21:10:37", updated_at: "2017-05-01 21:10:37"]
[id: 57, user_id: 3, friends_max: 1, friends_count: 0, created_at: "2017-05-01 21:10:37", updated_at: "2017-05-01 21:10:37"]

What I'm wanting to do is find all the friends records in the past 30 days, along with the associated email from the user_id column and store it in a hash. In the end there should be 6 different hashes built up (from the six friends records).

I believe I wrote something that accomplishes this, but it's not too pretty:

hold = []

User.includes(:friends).where(friends: {created_at: 30.days.ago..Time.now}).each do |i|
  i.friends.each do |o|
    hold << {
      friends_count: o.friends_count,
      friends_max: o.friends_max,
      email: i.email
    }
  end
end

puts hold

An example result for one friends record (id: 52) should be:

{:friends_count=>2, :friends_max=>5, :email=>"john@gmail.com"}

But of course will all 6 of the friends records returned. Does anyone know of a cleaner way to do this? Thanks.

Answer 1

First, your method may not be doing quite what you think it is doing. You are running a query that returns an ActiveRecord relation pointing to all Users that have a friend that was created in the last 30 days. Next you are iterating over those Users and, for each User, you are iterating over all of the User's friends--not just the Friends that were created in the last 30 days.

I don't think you want to start your query by searching Users. You are really searching for Friends, and incidentally you want to include the associated User email.

I would start by making a couple of scopes to use within your query. These will be available to other methods in your code and may be called on any existing scope of Friends:

class Friend < ApplicationRecord
  belongs_to :user
  scope :recently_created, -> { where(created_at: 30.days.ago..Time.now) }
  scope :with_user_email, -> { joins(:user).select('friends.*, users.email') }
end

Now you can clean up your method using these scopes and Ruby's built-in Array methods. While we're at it, you should do away with those single-letter names and use meaningful names:

Friend.recently_created.with_user_email.map do |friend|
  friend.attributes.slice('friends_max', 'friends_count', 'email')
end

Answer 2

You can use the following code

User.joins(:friends)
    .where("friends.created_at":(30.days.ago..Time.now))
    .select("users.email, friends.friends_max, friends.friends_count")
    .collect{|x| x.attributes.except('id') }