0
\$\begingroup\$

I'm trying to figure out the best way to set up an ActiveRecord query.


Models

There are three relevant models involved:

class Client < ActiveRecord::Base
  has_many :bookmarks, dependent: :destroy
  has_many :saved_coaches, through: :bookmarks, source: :bookmarkable, source_type: "Coach"
end

class Bookmark < ActiveRecord::Base
  belongs_to :client
  belongs_to :bookmarkable, polymorphic: true

  scope :type, -> (type) { where(bookmarkable_type: type.to_s.capitalize) }
end

class Coach < ActiveRecord::Base
  has_many :bookmarks, as: :bookmarkable, dependent: :destroy
  belongs_to :user
end

The problem

I need a performant query to access to the coaches a client has bookmarked, the coach's user model, and the id of relevant bookmark.

I can think of two approaches:

@client = current_user.client

# option 1
@coach_bookmarks = @client.bookmarks.type(:coach).includes(bookmarkable: :user)

# option 2
@coaches = Coach.includes(:bookmarks, :user)
  .where(bookmarks: { bookmarkable_type: "Coach", client: @client })

Option 1 seems clearer, but produces a collection of bookmarks, when most of the data I need is on the associated coach. Option 2 gives me a collection of coaches, but the query is a little less clear, and the SQL output is huge. (I've included that below.)

Is one of these solutions better than the other? Is there a third, better solution (maybe through @client.saved_coaches)?


SQL output

Option 1

Bookmark Load (1.3ms)  SELECT "bookmarks".* FROM "bookmarks" WHERE "bookmarks"."client_id" = $1 AND "bookmarks"."bookmarkable_type" = $2  [["client_id", 3], ["bookmarkable_type", "Coach"]]
Coach Load (2.3ms)  SELECT "coaches".* FROM "coaches" WHERE "coaches"."id" IN (1)
User Load (1.0ms)  SELECT "users".* FROM "users" WHERE "users"."id" IN (3)

Option 2

SQL (6.3ms)  SELECT "coaches"."id" AS t0_r0, "coaches"."user_id" AS t0_r1,
"coaches"."questionnaire_submitted" AS t0_r2, "coaches"."validated" AS t0_r3,
"coaches"."verified" AS t0_r4, "coaches"."membership_type" AS t0_r5,
"coaches"."new_coach" AS t0_r6, "coaches"."created_at" AS t0_r7,
"coaches"."updated_at" AS t0_r8, "coaches"."title" AS t0_r9,
"coaches"."image_url" AS t0_r10, "coaches"."experience_years" AS t0_r11,
"coaches"."specialties" AS t0_r12, "coaches"."session_types" AS t0_r13,
"coaches"."session_type_other" AS t0_r14, "coaches"."meeting_types" AS t0_r15,
"coaches"."meeting_type_other" AS t0_r16, "coaches"."cancellation_policy" AS
t0_r17, "coaches"."bio" AS t0_r18, "coaches"."price_options" AS t0_r19,
"coaches"."quote" AS t0_r20, "coaches"."current_screen" AS t0_r21,
"coaches"."address_raw" AS t0_r22, "coaches"."business_phone" AS t0_r23,
"coaches"."address_city" AS t0_r24, "coaches"."address_state" AS t0_r25,
"coaches"."address_area" AS t0_r26, "coaches"."latitude" AS t0_r27,
"coaches"."longitude" AS t0_r28, "coaches"."categories" AS t0_r29,
"coaches"."keywords" AS t0_r30, "coaches"."ace_up_handles" AS t0_r31,
"coaches"."stripe_account_id" AS t0_r32, "coaches"."bank_account_info" AS
t0_r33, "coaches"."offer_free_consult" AS t0_r34,
"coaches"."free_consult_length" AS t0_r35, "coaches"."sessions_start_every" AS
t0_r36, "coaches"."max_daily_sessions" AS t0_r37,
"coaches"."minimum_scheduling_notice" AS t0_r38,
"coaches"."buffer_after_session" AS t0_r39, "coaches"."num_reviews" AS t0_r40,
"coaches"."onboard_professional_background" AS t0_r41,
"coaches"."onboard_certifications" AS t0_r42, "coaches"."onboard_challenges"
AS t0_r43, "coaches"."onboard_promoting" AS t0_r44,
"coaches"."onboard_referral_source" AS t0_r45, "coaches"."onboard_interests"
AS t0_r46, "coaches"."onboard_submitted" AS t0_r47, "coaches"."coach_url" AS
t0_r48, "coaches"."timezone" AS t0_r49, "coaches"."average_review" AS t0_r50,
"coaches"."coach_order" AS t0_r51, "coaches"."onboard_business" AS t0_r52,
"coaches"."onboard_services" AS t0_r53, "coaches"."videos" AS t0_r54,
"coaches"."card_highlights" AS t0_r55, "coaches"."edit_requested" AS t0_r56,
"coaches"."specialties_edit" AS t0_r57, "coaches"."session_types_edit" AS
t0_r58, "coaches"."meeting_types_edit" AS t0_r59,
"coaches"."cancellation_policy_edit" AS t0_r60, "coaches"."bio_edit" AS
t0_r61, "coaches"."quote_edit" AS t0_r62, "coaches"."keywords_career" AS
t0_r63, "coaches"."keywords_life" AS t0_r64, "coaches"."keywords_leadership"
AS t0_r65, "coaches"."keywords_business" AS t0_r66,
"coaches"."keywords_communication" AS t0_r67, "coaches"."keywords_executive"
AS t0_r68, "coaches"."qualifications" AS t0_r69, "coaches"."education" AS
t0_r70, "coaches"."target_clients" AS t0_r71, "coaches"."highlights" AS
t0_r72, "coaches"."client_review_link" AS t0_r73, "coaches"."consent" AS
t0_r74, "coaches"."qualifications_edit" AS t0_r75, "coaches"."education_edit"
AS t0_r76, "coaches"."target_clients_edit" AS t0_r77,
"coaches"."highlights_edit" AS t0_r78, "coaches"."text_notifications" AS
t0_r79, "coaches"."email_notifications" AS t0_r80, "coaches"."companies" AS
t0_r81, "coaches"."address_raw_edit" AS t0_r82, "coaches"."longitude_edit" AS
t0_r83, "coaches"."latitude_edit" AS t0_r84, "bookmarks"."id" AS t1_r0,
"bookmarks"."client_id" AS t1_r1, "bookmarks"."bookmarkable_id" AS t1_r2,
"bookmarks"."bookmarkable_type" AS t1_r3, "users"."id" AS t2_r0,
"users"."email" AS t2_r1, "users"."encrypted_password" AS t2_r2,
"users"."reset_password_token" AS t2_r3, "users"."reset_password_sent_at" AS
t2_r4, "users"."remember_created_at" AS t2_r5, "users"."sign_in_count" AS
t2_r6, "users"."current_sign_in_at" AS t2_r7, "users"."last_sign_in_at" AS
t2_r8, "users"."current_sign_in_ip" AS t2_r9, "users"."last_sign_in_ip" AS
t2_r10, "users"."confirmation_token" AS t2_r11, "users"."confirmed_at" AS
t2_r12, "users"."confirmation_sent_at" AS t2_r13, "users"."unconfirmed_email"
AS t2_r14, "users"."failed_attempts" AS t2_r15, "users"."unlock_token" AS
t2_r16, "users"."locked_at" AS t2_r17, "users"."encrypted_otp_secret" AS
t2_r18, "users"."encrypted_otp_secret_iv" AS t2_r19,
"users"."encrypted_otp_secret_salt" AS t2_r20, "users"."consumed_timestep" AS
t2_r21, "users"."otp_required_for_login" AS t2_r22, "users"."personal_phone"
AS t2_r23, "users"."name" AS t2_r24, "users"."created_at" AS t2_r25,
"users"."updated_at" AS t2_r26, "users"."google_token" AS t2_r27,
"users"."google_refresh_token" AS t2_r28, "users"."last_calendar_refresh" AS
t2_r29, "users"."google_expires_at" AS t2_r30,
"users"."google_calendar_refresh_tokens" AS t2_r31, "users"."acquisition" AS
t2_r32, "users"."icalendar_urls" AS t2_r33, "users"."admin" AS t2_r34,
"users"."banned" AS t2_r35, "users"."email_subscriber" AS t2_r36,
"users"."intermediate_reminder_sent" AS t2_r37, "users"."final_reminder_sent"
AS t2_r38 FROM "coaches" LEFT OUTER JOIN "bookmarks" ON
"bookmarks"."bookmarkable_id" = "coaches"."id" AND
"bookmarks"."bookmarkable_type" = $1 LEFT OUTER JOIN "users" ON "users"."id" =
"coaches"."user_id" WHERE "bookmarks"."bookmarkable_type" = $2 AND
"bookmarks"."client_id" = 3  [["bookmarkable_type", "Coach"],
["bookmarkable_type", "Coach"]]
\$\endgroup\$
2
\$\begingroup\$

if you don't need the bookmarks instances then I would be inclined to go for something like:

@coaches = Coach
             .joins(:bookmarks).includes(:user)
             .where(bookmarks: { bookmarkable_type: "Coach", client: @client })

though I would wrap that in a scope. You could probably also get away with excluding the bookmarkable_type: "Coach" condition since the join will enforce that.

\$\endgroup\$
  • 1
    \$\begingroup\$ Unfortunately, I need their IDs. I didn't realize I could drop the "bookmarkable_type," though, and wrapping it into a scope was a good idea. I created a Bookmarkable concern to hold the has_many :bookmarks association and a new bookmarked_by scope. \$\endgroup\$ – Phillip Longman Aug 24 '17 at 19:49
0
\$\begingroup\$
@coaches = Coach
             .includes(:user)
             .joins(:bookmarks)
             .where(bookmarks: { bookmarkable_type: "Coach", client: @client })
             .select("coaches.*, bookmarks.id AS bookmark_id")

After this query you can do the following:

@coaches.first.id # returns coach id
@coaches.first.bookmark_id # returns the relevant bookmark_id
@coaches.first.user.name # returns the user's name of that coach.

This approach has one advantage over including the collection of bookmarks that it won't load the whole objects of bookmarks into memory when you need only bookmark_id.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.