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 Answers 2

Reset to default
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
  • 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\$ Commented Aug 24, 2017 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\$

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.