Select simple value in ActiveRecord

Question

I want to show a text summary for a model in a Rails application.

Currently I'm doing it like this:

class ServiceOrder < ApplicationRecord
  has_many :items, class_name: 'ServiceOrderItem', 
                   dependent: :destroy,
                   inverse_of: :service_order

  def link_text
    items.left_outer_joins(:product)
      .select("string_agg(coalesce(products.description, service_order_items.description), '; ') as description")
      .group("service_order_items.service_order_id")
      .map(&:description)
      .first
  end
end

class ServiceOrderItem < ApplicationRecord
  belongs_to :service_order, inverse_of: :items
  belongs_to :product, optional: true
end

class Product < ApplicationRecord
end

What bothers me is that I'm trying to select a single value, and not a model.

This query does return a "fake" model and extract the value I want, but it's kind of hacky:

1. Add proper relations I need
   • items.left_outer_joins(:product)
2. Add the select value I want
   • .select("string_agg(coalesce(products.description, service_order_items.description), '; ') as description")
3. Add group by clause
   • .group("service_order_items.service_order_id")
4. Execute the query and extract the description of the "fake" model returned
   • .map(&:description)
5. I know this query only returns a single result, but it builds an array with all results, so I extract the single result out of the array
   • .first

The query I want is this:

select string_agg(coalesce(products.description, service_order_items.description), '; ')
  from service_order_items
  left outer join products on service_order_items.product_id = products.id
  where service_order_items.service_order_id = :id
  group by service_order_items.service_order_id;

And this is the query I'm generating, the problem is that the result is enclosed in a model object, then I transform it into an array and then I extract the value I want.

So, how do I tell active record to select a single raw value and not a list of models?

By the way, adding .first before .map doesn't work because it includes an order by in the executed SQL that I can't have (order by service_order_items.id).

The schema:

create_table "products", force: :cascade do |t|
  t.integer  "organization_id"
  t.string   "code"
  t.string   "description"
  t.string   "brand"
  t.string   "unit_of_measure"
  t.datetime "created_at",      null: false
  t.datetime "updated_at",      null: false
  t.decimal  "selling_price"
  t.index ["organization_id"], name: "index_products_on_organization_id", using: :btree
end

create_table "service_order_items", force: :cascade do |t|
  t.integer  "service_order_id"
  t.decimal  "quantity"
  t.string   "description"
  t.integer  "product_id"
  t.decimal  "unit_price"
  t.datetime "created_at",       null: false
  t.datetime "updated_at",       null: false
  t.index ["product_id"], name: "index_service_order_items_on_product_id", using: :btree
  t.index ["service_order_id"], name: "index_service_order_items_on_service_order_id", using: :btree
end

create_table "service_orders", force: :cascade do |t|
  t.integer  "organization_id"
  t.text     "description"
  t.integer  "state_id"
  t.datetime "created_at",      null: false
  t.datetime "updated_at",      null: false
  t.integer  "customer_id"
  t.integer  "sequential_id"
  t.date     "start_date"
  t.date     "end_date"
  t.index ["customer_id"], name: "index_service_orders_on_customer_id", using: :btree
  t.index ["organization_id"], name: "index_service_orders_on_organization_id", using: :btree
  t.index ["state_id"], name: "index_service_orders_on_state_id", using: :btree
end

Answer 1

New answer

The need to use the description on service_order_items if there isn't a product makes this a little tricky. If you want to keep your custom SQL, it should be possible to use pluck with the same text as your select (minus the as description part):

def link_text
  items.left_outer_joins(:product)
    .group("service_order_items.service_order_id")
    .pluck("string_agg(coalesce(products.description, service_order_items.description), '; ')")
    .first
end

You also mentioned that you couldn't use first before map because it introduced an undesired order; you could try using take instead of first to avoid that, in which case you wouldn't need pluck.

Note that in either case you're introducing some dependencies on the table names, which could cause problems in more complex queries that require table aliases. If you want to go for less custom SQL, the most direct way I can think of is to add the following method (probably with a name that better fits your application) to ServiceOrderItem:

def description_for_link_text
  product.try(:description) || description
end

Then in ServiceOrder:

def link_text
  items.includes(:product).map(&:description_for_link_text).join('; ')
end

The includes(:product) should avoid the N+1 issue where you do one query to get the items and then another query for each product. If you have a page that's displaying this text for multiple service orders, you have to deal with another level of this; often you have to declare a whole bunch of tables in includes even if they're declared in the link_text method.

service_orders = ServiceOrder.some_query_or_scope.includes(items: :product)
service_orders.each { |so| puts so.link_text }

If you do this, I don't think you actually have to have the includes in link_text itself, but if you removed it from there and you called link_text in any other situation, you'd get the N+1 issue again.

Original answer

I'm a bit confused by how your schema fits together: do service_orders and items have a one-to-many relationship, or a many-to-many relationship? How does products relate to items? And I don't have quite enough reputation to comment to ask.

In general, you can use pluck to get an array of values with just the attributes you want. I don't know off the top of my head if it works on virtual attributes, but you may be able to define has_many :through relationships so that you don't need to define string_agg(products.description, '; ') as description to join the strings together. That is, if your ServiceOrder model is able to have a products association like:

has_many :items
has_many :products, through: :items

then you could then just define link_text as products.pluck(:description).join("; "). You may need to play around with your has_many :through definition in order to get it to work right with your schema. Also, doing it this way does mean you have to watch out for potential N+1 query issues; see the Rails guide section on eager loading for how to address that.