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';