Need to convert an LEFT JOIN to JPQL

immagine following problem: somebody wants to buy a basket of fruit containing green fruits from Africa and South America but no red fruits from Africa.

for that we think about following classes:

    class Basket
    {
      int id;
      Collection<Fruit> fruits; 
    }

    class Fruit
    {
      int id;
      int basketId;
      String origin
      Color color;
    }

the mapping will be MxM. It is intended that Fruit has no Basket object, but if necessary i could implement it.

for Native SQL i would use:

    SELECT * 
    FROM 
      Basket b 
    JOIN 
    (
      SELECT DISTINCT basketId 
      FROM Fruit 
      WHERE color='green' AND (origin='Africa' OR origin='South America')
    ) f1 ON (b.id=f1.basketId) 
    LEFT JOIN 
    (
      SELECT DISTINCT basketId 
      FROM Fruit 
      WHERE color='red' AND (origin='Africa')
    ) f2 ON (b.id=f2.basketId) 
    WHERE f2.basketId IS NULL 

What will be the Query in JPQL?

i tried already following:

    SELECT b 
    FROM Basket b 
    WHERE 
      b.id IN (
        SELECT f1.basketId FROM Fruit f1 
        WHERE f1.c开发者_JAVA百科olor='green' AND (f1.origin='Africa' OR f1.origin='South America')
      ) AND
      b.id NOT IN (
        SELECT f2.basketId FROM Fruit f2 
        WHERE f2.color='red' AND (origin='Africa'))

but this query took 12000ms instead of 50 ms. (This is just a simple example. The real Tables have around 750000 "fruits" and 10000 "baskets" with much more fields each.)

Thanks in advance

Gerald

---

You could try

select b from Basket b where
    exists (select f.id from Fruit f where f.basket = b and f.color = 'green')
    and not exists (select f.id from Fruit f where f.basket = b and f.color = 'red')

But I'm not sure it would be faster. It depends on the execution plan, which depends on the database indexes. There should be an index on Fruit.basketId and on Fruit.color.

---

would this work?

select b from Basket b join b.fruits f where f.color = 'green' and not f.color = 'red';

The join is probably optional so it could also be

select b from Basket b where b.fruits.color = 'green' and not b.fruits.color = 'red';