Question: ActiveRecord Complex Query

Question

ActiveRecord Complex Query

Answers 0
Added at 2016-12-28 18:12
Tags
Question

I need some advice on how I should be going about some business logic in our app. Currently I have a loop for returning all of a user’s sales opportunities.. It involves going through current_user‘s inventory_parts (uniq! by part number and condition) for each part number, I’m looping through all auctions in the auction table and making sure that in each auction:

  1. The auction was not created by the current_user
  2. The current_user has not already placed a bid in this auction
  3. The inventory_part‘s condition (new/used) includes one of the conditions that the auction is asking for

This is a loop that is way too inefficient, especially when we’re talking about users who have tens or hundreds of thousands of inventory_parts. There are a couple options I’m considering

  • A. Making a new table that stores all opportunities, with a company_id column that links the opportunities. These opportunities would be created in the background by a worker after a new auction has been made
  • B. Adding an opportunities column to companies that stores the id‘s of the auctions in a serialized array, and like option A using a worker after an auction is made to do all the work.
  • C. What I’ve been trying to figure out, is leaving my logic as is and figuring out how to load it in the background with a dataTable class

This is what my inefficient loop currently looks like

  def self.get_sales_opportunities(user)
      parts = user.inventory_parts
      parts.uniq! { |part| [part[:part_num], part[:condition]] }

      sales_opportunities = []
      parts.each do |part|
        #stick auction in sales opportunities
        #if the auction is not the user's, already contains a user bid,
        #or if the auction isn't asking for the part in-question's condition
        Auction.where(part_num: part.part_num, active: true).each do |auction|
          user_created_auction = (auction.company == user)
          user_has_placed_bids = (auction.bids & user.bids).present?
          part_matches = auction.condition.include?(part.condition)
          all_conditions = true if auction.condition[0].blank?

          sales_opportunities << auction unless user_created_auction || user_has_placed_bids || !part_matches
          sales_opportunities << auction if all_conditions && auction.company != user && !user_has_placed_bids
        end
      end
      sales_opportunities.uniq
    end
Answers to

ActiveRecord Complex Query

Source Show
◀ Wstecz