我有一个STI为基础的模型，称为可买，有两个型号篮和项目。这里可购买关注的属性是：

I have an STI-based model called Buyable, with two models Basket and Item. The attributes of concern here for Buyable are:

shop_week_id LOCATION_ID PARENT_ID

有篮和项目之间的父子关系。 PARENT_ID始终是零的篮子里，但一个项目可以通过引用独特的一揽子ID属于一个篮子里。因此，篮子的has_many项目，一个项目belongs_to的一个篮子里。

There's a parent-child relationship between Basket and Item. parent_id is always nil for basket, but an item can belong to a basket by referencing the unique basket id. So basket has_many items, and an item belongs_to a basket.

我需要在篮下模型的方法是：

I need a method on the basket model that:

如果有在表中与两个相同的数目的和类型的物品的任何其他筐返回假真。项目被认为是同一类型时，他们共享相同shop_week_id和LOCATION_ID

Returns true of false if there are any other baskets in the table with both the same number of and types of items. Items are considered to be the same type when they share the same shop_week_id and location_id.

有关EX：

由于一篮子（UID = 7）有2项：

Given a basket (uid = 7) with 2 items:

项目＃1

在ID = 3 shop_week_id = 13 LOCATION_ID = 103 PARENT_ID = 7

项目＃2

n = 4 shop_week_id = 13 LOCATION_ID = 204 PARENT_ID = 7

返回真，如果有表中的任何其他篮子正好包含2项，与具有shop_week_id = 13和LOCATION_ID = 103，另一项具有shop_week_id = 13和LOCATION_ID = 204，否则返回false。

Return true if there are any other baskets in the table that contain exactly 2 items, with one item having a shop_week_id = 13 and location_id = 103 and the other having a shop_week_id = 13 and location_id = 204. Otherwise return false.

您将如何处理这个问题？这个不用说，但我要寻找一个非常有效的解决方案。

How would you approach this problem? This goes without saying, but I am looking for a very efficient solution.

推荐答案

要澄清我的查询，以及可买表的表列的有点模糊的描述，而PARENT_ID在篮子中的问题。的Shop_Week_ID是考虑筐要比较...不从1周比较一篮子到2周〜3 #ID列显示为在表中一个连续ID周，但不实际的ID的项目进行比较...该LOCATION_ID似乎是常见的物品。在该方案中，假定一个购物车，LOCATION_ID = 103 =计算机，LOCATION_ID = 204 =电视（只是对数据我跨pretation）。如果这是不正确，可能需要轻微的调整，除了原来的海报示出，以显示正确的相关性说...的数据的一打的条目的列表。

To clarify my query, and somewhat vague description of the table columns of the "buyable" table, The "Parent_ID" is the basket in question. The "Shop_Week_ID" is the consideration for baskets to be compared... don't compare a basket from week 1 to week 2 to week 3. The #ID column appears to be a sequential ID in the table, but not the actual ID of the item to be compared... The Location_ID appears to be the common "Item". In the scenario, assuming a shopping cart, Location_ID = 103 = "Computer", Location_ID = 204 = "Television" (just for my interpretation of the data). If this is incorrect, minor adjustments may be needed, in addition to the original poster showing a list of say... a dozen entries of the data to show proper correlation.

所以，现在，让我的查询。我正在做一个STRAIGHT_JOIN所以在加入我所列出的顺序。

So, now, on to my query.. I'm doing a STRAIGHT_JOIN so it joins in the order I've listed.

第一个查询别名MainBasket是专门用于查询多少项目是在篮子中问题一次，所以它不需要被重新接合/对每个可能的篮子，以匹配再次查询。没有ON条款，因为这将是一个单一的记录，因此没有笛卡尔的影响，因为我想这个COUNT（*）值适用于每一个记录的最终结果。

The first query for alias "MainBasket" is exclusively used to query how many items are in the basket in question ONCE, so it doesn't need to be re-joined/queried again for each possible basket to match. There is no "ON" clause as this will be a single record, and thus no Cartesian impact, as I want this COUNT(*) value applied to EVERY record in the final result.

下一个查询是要找到一个DISTINCT对方篮下，其中至少有一个LOCATION_ID（项目）的同一周中的父问题的范围...这可能会导致具有1个，相同或比其他条目篮子篮。但是，如果有100个篮子，但只有18至少有1个是1个项目在原有的篮子匹配的条目，你只是显著降低筐的数量做最后的比较反对（SameWeekSimilar别名结果）。

The NEXT Query is to find a DISTINCT OTHER Basket where at LEAST ONE "Location_ID" (Item) within the same week as the parent in question... This could result in other baskets having 1, same or more entries than the basket. But if there are 100 baskets, but only 18 have at least 1 entry that matches 1 item in the original basket, you've just significantly cut down the number of baskets to do final compare against (SameWeekSimilar alias result).

最后一个加入到可买表了，但基于联接的SameWeekSimilar，但只有在每个其他篮子有一场势均力敌的比赛......没有具体项目，只是篮筐。用于获取SameWeekSimilar查询已经$ P $对 - 合格同一周，并从有问题的原篮的至少一个匹配的物品，但特别不包括原始篮，因此它不会与其自身进行比较。

Finally is a Join to the buyable table again, but based on a join for the SameWeekSimilar, but only on per "other" basket that had a close match... No specific items, just by the basket. The query used to get the SameWeekSimilar already pre-qualified the same week, and at least one matching item from the original basket in question, but specifically excluding the original basket so it doesn't compare to itself.

通过做一组基础上，SameWeekSimilar.NextBasket外层面上，我们可以得到这个篮子实际的项目数。因为一个简单的笛卡尔联接到MainBasket，我们只要抓住原有的计数。

By doing a group at the outer level based on the SameWeekSimilar.NextBasket, we can get the count of actual items for that basket. Since a simple Cartesian join to the MainBasket, we just grab the original count.

最后，HAVING子句。由于这是COUNT（*）后应用，我们知道很多项目是如何在其他篮子，又有多少在主要菜篮子。所以，在HAVING子句只包括那些计数是相同的。

Finally, the HAVING clause. Since this is applied AFTER the "COUNT(*)", we know how many items were in the "Other" baskets, and how many in the "Main" basket. So, the HAVING clause is only including those where the counts were the same.

如果你想测试，以确保我所描述的，运行这个对你的表，但不包括HAVING子句。你会看到这都是可能的...然后重新添加HAVING子句，看看哪些是匹配相同的数...

If you want to test to ensure what I'm describing, run this against your table but DO NOT include the HAVING clause. You'll see which were all the POSSIBLE... Then re-add the HAVING clause and see which ones DO match same count...

select STRAIGHT_JOIN
      SameWeekSimilar.NextBasket,
      count(*) NextBasketCount,
      MainBasket.OrigCount
   from 
      ( select count(*) OrigCount
           from Buyable B1
           where B1.Parent_ID = 7 ) MainBasket

      JOIN

      ( select DISTINCT
              B2.Parent_ID as NextBasket
           from
              Buyable B1
                 JOIN Buyable B2
                    ON B1.Parent_ID != B2.Parent_ID
                   AND B1.Shop_Week_ID = B2.Shop_Week_ID
                   AND B1.Location_ID = B2.Location_ID
           where
              B1.Parent_ID = 7 ) SameWeekSimilar

       Join Buyable B1
          on SameWeekSimilar.NextBasket = B1.Parent_ID

    group by
       SameWeekSimilar.NextBasket

    having
       MainBasket.OrigCount = NextBasketCount