I've an STI-based model known as Buyable, with two models Basket and Item. The characteristics of interest for Buyable are:

  • shop_week_id
  • location_id
  • parent_id

There is a parent-child relationship between Basket and Item. parent_id is definitely nil for basket, but a product can fit in with a gift basket by referencing the initial basket id. So basket has_many products, as well as an item goes_to some basket.

I want a technique around the basket model that:

Returns the case with false if you will find every other baskets within the table with both same quantity of and kinds of products. Products are regarded as exactly the same type once they share exactly the same shop_week_id and placement_id.

For ex:

Given a gift basket (uid = 7) with 2 products:

item #1

  • id = 3
  • shop_week_id = 13
  • location_id = 103
  • parent_id = 7

item #2

  • id = 4
  • shop_week_id = 13
  • location_id = 204
  • parent_id = 7

Return true if you will find every other baskets within the table which contain exactly 2 products, with one item getting a store7 daysidentification = 13 and placementidentification = 103 and also the other getting a store7 daysidentification = 13 and placementidentification = 204. Otherwise return false.

How does one approach this issue? This is obvious, however i am searching for a really efficient solution.

The next SQL appears to have the desired effect

large_query = "

  Choose Is available (

    Choose 1

    FROM buyables b2

      JOIN buyables b2

        ON b2.shop_week_id = b2.shop_week_id

        AND b2.location_id = b2.location_id

    WHERE

      b2.parent_id != %1$d

      AND b2.parent_id = %1$d

      AND b2.type = 'Item'

      AND b2.type = 'Item'

    GROUP BY b2.parent_id

    Getting COUNT(*) = ( Choose COUNT(*) FROM buyables WHERE parent_id = %1$d And kind = 'Item' )

  )

"

With ActiveRecord, you will get this result using choose_value:

class Basket < Buyable

  def has_duplicate

    !!connection.choose_value( large_query % id )

  finish

finish

I'm not so sure about performance however

If you wish to get this to as efficient as you possibly can, you should look at developing a hash that encodes basket contents like a single string or blob, give a new column that contains the hash (which will have to be up-to-date each time the basket contents change, either through the application or utilizing a trigger), and compare hash values to find out possible equality. Then you will need to do further evaluations (as referred to above) so as

What in the event you use for any hash though? Knowing the baskets is going to be limited in dimensions, and also the ids under consideration are bounded integers, you need to have the ability to hash to some string that's enough by itself to check for equality. For instance, you can base64 scribe each shop_week and placement, concatenate having a separator not in base64 (like ""), after which concatenate using the other basket products. Build a catalog around the new hash key, and evaluations is going to be fast.