Marketplace - Search by rate & tags - Model - Crystal

product_query.cr

class ProductQuery < Granite::Base
  connection pg

  column id : Int64, primary: true
  column user_id : Int64
  column title : String?
  column description : String?
  column tags : Array(Int64) | Nil
  column amount : Float64?
  column attachment : String?

  select_statement <<-SQL
  SELECT products.id,
  products.user_id,
  products.title,
  products.description,
  ARRAY_AGG(taggables.tag_id) AS tags,
  MIN(rates.amount) AS amount,
  (ARRAY_AGG(attachments.picture))[1] AS attachment
  FROM products
  LEFT OUTER JOIN taggables ON taggables.model_id = products.id AND taggables.model_type = 'Product'
  LEFT OUTER JOIN rates ON rates.model_id = products.id AND rates.model_type = 'Product'
  LEFT OUTER JOIN attachments ON attachments.id = products.attachment_id
  SQL
end