我有一个现有的查询,它返回100000多条记录。
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")
设备型号有一个范围:
scope :dhcp, -> {
joins("INNER JOIN dhcp_cidrs ON (devices.ipaddress <<= dhcp_cidrs.cidr)").where("devices.id NOT IN (?)", Device.reserved.select("devices.id"))
}
如何使用Device.dhcp作为子查询来计算一个名为"is_dhcp"=true的新列,如果cid在dhcp_ids中(否则,is_dhcp=false(
pulk((支持多种数据库命令。在许多DBMS中,可以使用IF-THEN-ELSE构造。在PostgresQL中,必须使用CASE-WHEN-THEN-ELSE-END构造。
首先,您可以使用model.scope创建CASE构造,如下所示:
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"
接下来,您可以在pulk((中使用此计算
.pluck("devices.hostname, devices.ipaddress, systems.fismaid, assets.is_expired, #{dhcp_calculation}")