CODEWITHSUNDEEP
ruby-on-railsrubyactiverecordassociations
Abhishek Bhatta
Abhishek Bhatta

Reputation: 69

How to pluck a column value in has one association?

I have a Vehicle model which has a has_one association with QrCode. I want to pluck a specific column of qr_code rather than selecting all the columns and mapping single value

I have tried the following code.

vehicle = Vehicle.first 
code = vehicle.qr_code.pluck(:value)

But this is not a valid query

Following code will have the desired value. 

code = vehicle.qr_code.value

But the query build by this code is

SELECT  "qr_codes".* FROM "qr_codes" WHERE "qr_codes"."codeable_id" = $1 AND "qr_codes"."codeable_type" = $2 LIMIT 1  [["codeable_id", 1], ["codeable_type", "Vehicle"]]

This is expensive as it selects all column values and there are few columns in qr_codes table that store huge data.

Following is the code implementation 

class Vehicle < ActiveRecord::Base
  has_one :qr_code, as: :codeable
end

class QrCode < ActiveRecord::Base
  belongs_to :codeable, polymorphic: true
end

not expected query:

SELECT  "qr_codes".* FROM "qr_codes" WHERE "qr_codes"."codeable_id" = $1 AND "qr_codes"."codeable_type" = $2 LIMIT 1  [["codeable_id", 1], ["codeable_type", "Vehicle"]]

expected query: 

SELECT  "qr_codes".value FROM "qr_codes" WHERE "qr_codes"."codeable_id" = $1 AND "qr_codes"."codeable_type" = $2 LIMIT 1  [["codeable_id", 1], ["codeable_type", "Vehicle"]]

Upvotes: 0

Views: 1441

Answers (4)

Dyaniyal Wilson
Dyaniyal Wilson

Reputation: 1060

This will select only value column in the query.

Vehicle.joins(:qr_code).select("qr_codes.value").pluck(:value)

For a specific vehicle:

Vehicle.where(id: 1).joins(:qr_code).select("qr_codes.value").pluck(:value)

Upvotes: 0

Parakh Garg
Parakh Garg

Reputation: 119

As per your expected query -

QrCode.where(codeable: Vehicle.first).pluck(:value).first

Upvotes: 0

Pavel Mikhailyuk
Pavel Mikhailyuk

Reputation: 2877

When you have Vehicle instance:

QrCode.
  where(codeable: vehicle).
  pluck(:value).
  first

When you have vehicle_id only:

Vehicle.
  left_joins(:qr_code).
  where(id: vehicle_id).
  pluck('qr_codes.value').
  first

Upvotes: 0

Vishal
Vishal

Reputation: 7361

You can use query like below for getting vehicle first record with its qr code's value name

Vehicle
      .left_outer_joins(:qr_code)
      .select("vehicles.*, 
              (SELECT qr_codes.value from qr_codes WHERE vehicles.id = qr_codes.codeable_id) as value_name")
      .limit(1)

Upvotes: 1

Related Questions