OPTIMIZE QUERY ON RAILS - POSTGRESQL-CodePudding

I have 3 scopes: 1 - lists the promotions in progress. 2 - sort out of stock last 3 - list the promotions that are not in progress.

Is it possible to combine everything in a single scope? It would be: List the promotions in progress, in order of stock from highest to lowest, and then the promotions that are not in progress.

I tried, but I couldn't.

scope :in_progress, -> { start_and_end_dates.in_weeks.between_hours }
    
scope :without_stock_last, lambda {
  select('promotions.*, (CASE WHEN offers.current_inventory > 0 THEN 1 ELSE 0 END) AS "is_available"')
   .order('is_available DESC')
   .group('promotions.id, offers.current_inventory, offers.created_at')
}
    
scope :not_progress, lambda {
  promotions_in_progress = Promotion.in_progress.pluck(:id).join(",")
  Promotion.with_estabilishment.select("promotions.*, (CASE WHEN promotions.id NOT IN (#{promotions_in_progress}) THEN 0 END) AS is_unavailable")
  .order('is_unavailable DESC')
}

CodePudding user response：

if I understand what you're trying to do is to get in_progress showing first in the result and then you have the not in progress coming next. You can just as the scope :find_all concat the result of the results of the other two.

I supposed that (correct me if I'm wrong) that you have One to Many relationship between Promotion and Offer. So what I do in the without_stock_last scope is to join the offers and set a condition where current_inventory greater than 2.

For the not_in_progress you can just do a where.not to do an exclusion of result from the in_progress scope

scope :in_progress, -> { start_and_end_dates.in_weeks.between_hours }
scope :without_stock_last, -> { joins(:offfers).where(offers: {current_inventory: 2..})  }
scope :not_in_progress, -> { where.not(id: in_progress) }
scope :find_all, -> { without_stock_last   not_in_progress }

Please provide a more clear description of the model you have and what optimization you want to make.

thank