関連テーブルを含めたSELECTクエリの書き方

例えば、次のようなデータ構造があるとします。

Javaのソースは次のような感じ。

@Entity
public class Shop {

    //主キー
    @Id
    private long id;

    //取扱商品
    @ManyToMany(fetch = FetchType.LAZY)
    @JoinTable(name = "HandleProduct", 
        joinColumns = @JoinColumn(name = "shop_id"), 
        inverseJoinColumns = @JoinColumn(name = "product_id"))
    private Set<Product> handleProducts;

    ...
}


@Entity
public class Product {

    //主キー
    @Id
    private long id;


    ...
}

また、初期状態は次の通りとします。

• 店舗1(Shop.id=1)は商品1(Product.id=1)、商品2(Product.id=2)、商品3(Product.id=3)を取り扱っている。
• 店舗2(Shop.id=2)は商品3、商品4(Product.id=4)を取り扱っている。

要するに、オブジェクト図でいうと次のような状態です。

このとき、特定の商品を取り扱う店舗を検索したい場合にJPQLでどう書けば良いか、を整理してみました。

関連テーブルの検索パターン

さて、『特定の商品を取り扱う店舗』と言ってもパターンが3つあります。

1. 検索条件として指定した商品をどれか1つでも取り扱っている。: INTERSECTIONケース
2. 少なくとも、検索条件として指定した商品を全部取り扱っている。: INCLUSIONケース
3. 検索条件として指定した商品を、過不足なく正確にその組み合わせで取り扱っている。: EQUIVALENCEケース

INTERSECTIONケース

これは一番簡単なパターンです。

少なくとも商品1または商品4を取り扱う店舗を検索するJPQLは次の通り。

SELECT OBJECT(e)

FROM 
    Shop AS e, 
    IN(e.handleProducts) product

WHERE
    (product.id IN (1, 4))

このJPQLを実行すると、店舗1と店舗2がヒットします。

INCLUSIONケース

少なくとも商品1および商品3を取り扱う店舗を検索するJPQLは次の通り。

SELECT OBJECT(e)

FROM 
    Shop AS e, 
    IN(e.handleProducts) product

WHERE
    (SELECT COUNT(DISTINCT product_e) FROM Product AS product_e
    WHERE 
        (product_e.id IN (1, 3))
        AND
        (product_e.id = product.id)
    ) = 2

うーむ、いきなり面倒になりました。もっといい書き方があるような気が。

まあとりあえず、このJPQLを実行すると、店舗1のみがヒットします。

EQUIVALENCEケース

一番面倒なパターンです。

正確に商品1、商品2、商品3だけを取り扱う店舗を検索するJPQLは次の通り。

SELECT OBJECT(e)

FROM 
    Shop AS e, 
    IN(e.handleProducts) product

WHERE
    (SELECT COUNT(DISTINCT product_e) FROM Product AS product_e
    WHERE 
        (product_e.id IN (1, 2, 3))
        AND
        (product_e.id = product.id)
    ) = 3

    AND

    SIZE(e.handleProducts) = 3

このJPQLを実行すると、店舗1のみがヒットします。

EQUIVALENCEケースで実行されたSQL

EclipseLinkにより自動生成されたSQLはこんな感じです。

もし手書きでこんなSQLを書いていたら、小一時間説教されるレベルです。
自動生成だからまあいいけども。

SELECT   Count(DISTINCT (t0.id))
FROM     shop t0,
         handleproduct t2,
         product t1
WHERE    ((((((SELECT Count(DISTINCT (t3.id))
               FROM   handleproduct t7,
                      product t6,
                      shop t5,
                      product t4,
                      product t3,
                      handleproduct t8
               WHERE  ((((t3.id IN (1, 2, 3))
                         AND (t3.id = t4.id))
                        AND (t6.id = t4.id))
                       AND (((t7.shop_id = t5.id)
                             AND (t4.id = t7.product_id))
                            AND ((t8.shop_id = t0.id)
                                 AND (t6.id = t8.product_id))))) = 3)
             AND ((SELECT Count(* )
                   FROM   product t9,
                          handleproduct t10
                   WHERE  ((t10.shop_id = t0.id)
                           AND (t9.id = t10.product_id))) = 3))
          AND ((t2.shop_id = t0.id)
               AND (t1.id = t2.product_id)))