Calculate a new column in rails activerecord query

Question

I have an existing query that returns 100,000+ records.

dev_results = records.joins("INNER JOIN devices ON (devices.id = snapshots.type_id)")
  .joins("INNER JOIN assets ON (devices.asset_id = assets.id)")
  .joins("LEFT JOIN systems ON (assets.system_id = systems.id)")
  .pluck("devices.hostname, devices.ipaddress, systems.fismaid, assets.is_expired")

The Device model has a scope:

  scope :dhcp, -> {
joins("INNER JOIN dhcp_cidrs ON (devices.ipaddress <<= dhcp_cidrs.cidr)").where("devices.id NOT IN (?)", Device.reserved.select("devices.id"))

}

How do I use Device.dhcp as a subquery to calculate a new column called "is_dhcp" = true if devices.id is in dhcp_ids (otherwise, is_dhcp = false)

Answer 1

pluck() supports a wide variety of database commands. In many DBMS, you could use the IF-THEN-ELSE construct. In PostgresQL, you must use the CASE-WHEN-THEN-ELSE-END construct.

First, you can use the model.scope to create the CASE construct as follows:

dhcp_device_ids = Device.dhcp.select('devices.id').to_sql
dhcp_calculation = "CASE WHEN (devices.id IN (#{dhcp_device_ids})) THEN TRUE ELSE FALSE END"

Next, you can use this calculation in pluck()

.pluck("devices.hostname, devices.ipaddress, systems.fismaid, assets.is_expired, #{dhcp_calculation}")