Re: [eclipselink-users] Number of problems with query hint extensions

[Michael Simons <michael.simons@xxxxxxxxxxx>]

Hello Tom,

I've tried the possibility mentioned by Andrei, but that seems to slow because of the
unnecessary outer-joins. So it only works for QueryHints.FETCH.
So the Native EclipseLink API definitely is an option for us. Can You please tell where to get
information about it.

Kind Regards, Michael


Tom Ware schrieb:
> Hi Michael,
> 
>   The problem here is that each of your hints is being treated as
> individual.
> 
> i.e.
> x.locatedVehicles causes one fetch and x.locatedVehicles.tours causes a
> completely independent fetch and so on. There is probably something we
> could do here to enhance the framework to allow expressions like
> x.locatedVehicles and x.locatedVehicles.tours to refer to the same
> thing.  (maybe some kind of aliasing strategy like JPQL uses for its
> non-fetch joins)  Please feel free to enter an enhancement.
> 
>   To workaround, the only thing I can think of is to use Native
> EclipseLink API.  Let me know if that is an option for you and I can
> help out.
> 
>  As for batching, that works a little differently, you should not see
> any SQL related to the batch on your original query, but when you
> traverse the relationship that is batched, you will get all the objects
> on the other side of the relationship as a batch.
> 
> -Tom
> 
> Michael Simons wrote:
>> Hello once more,
>>
>> when I expand the query to also get the stops within a tour like this:
>>  query = em.createQuery("SELECT DISTINCT x FROM Depot x WHERE
>> x.scenario = :currentScenario");
>>  query.setHint (QueryHints.FETCH, "x.locatedVehicles");
>>  query.setHint (QueryHints.FETCH, "x.locatedVehicles.tours");
>>  query.setHint (QueryHints.FETCH, "x.locatedVehicles.tours.positions");
>>
>> things get even worse, as you can see by the SQL statement below. As
>> you might guess that
>> statement runs almost for ever.
>> That's because the statement is much too complicated, e.g. the vehicle
>> is joined three times!
>> But that's not necessary at all, it's bloated.
>> Why is this done?
>>
>>
>> SELECT DISTINCT t1.depot_id, t1.bot_pump_rate, t1.available, ...
>> FROM tour_position t6
>> LEFT OUTER JOIN order_stop t7 ON (t7.tour_position_id =
>> t6.tour_position_id)
>> LEFT OUTER JOIN load_stop t8 ON (t8.tour_position_id =
>> t6.tour_position_id), tour t5, vehicle
>> t4, tour t3, vehicle t2, depot t1, vehicle t0
>> WHERE ((t1.scenario_id = ?)
>>  AND ((((((t0.depot_id = t1.depot_id) AND (t2.depot_id = t1.depot_id))
>>  AND (t3.vehicle_id = t2.vehicle_id)) AND (t4.depot_id = t1.depot_id))
>>  AND (t5.vehicle_id = t4.vehicle_id)) AND (t6.tour_id = t5.tour_id)))
>>
>> kind Regards, Michael
>>
>> Michael Simons schrieb:
>>> Hello,
>>>
>>> Given the follwing JPQL Query:
>>> "SELECT DISTINCT d FROM Depot d WHERE d.scenario = :currentScenario"
>>>
>>> with the following classes:
>>> @Entity @Table(name="depot") class Depot {
>>>   ...
>>>   List<Vehicle> locatedVehicles;
>>> }
>>>
>>> @Entity @Table(name="vehicle") class Vehicle {
>>>   ...
>>>   List<Tour> tours;
>>> }
>>>
>>> @Entity @Table(name="tour") class Tour {
>>>   ...
>>> }
>>>
>>> We want EL to query not only the depots within a given scenario but
>>> also all vehicles located at
>>> these depots, and all tours driven by theses vehicles (trucks).
>>> So, we set the following hints:
>>>         query.setHint (QueryHints.LEFT_FETCH, "x.locatedVehicles");
>>>         query.setHint (QueryHints.LEFT_FETCH,
>>> "x.locatedVehicles.tours");
>>>
>>> The following SQL-statement is created by EL:
>>> SELECT DISTINCT t1.depot_id, t1..., t0.vehicle_id, t0...,
>>> t2.vehicle_id, t2..., t3.tour_id, t3...
>>> FROM depot t1
>>> LEFT OUTER JOIN vehicle t0 ON (t0.depot_id = t1.depot_id)
>>> LEFT OUTER JOIN vehicle t2 ON (t2.depot_id = t1.depot_id)
>>> LEFT OUTER JOIN tour t3 ON (t3.vehicle_id = t2.vehicle_id)
>>> WHERE (t1.scenario_id = ?)
>>>
>>> Why is vehicle joined twice?
>>>
>>> On the other hand, when specifying
>>>         query.setHint (QueryHints.BATCH, "x.locatedVehicles");
>>>         query.setHint (QueryHints.BATCH, "x.locatedVehicles.tours");
>>>
>>> neither vehciles nor tours are selected at all.
>>>
>>> Is there anything wrong with these hints?
>>> Is there a detailed documentation on how to use QueryHints.BATCH /
>>> .FETCH / .LEFT_FETCH ?
>>>
>>> Kind Regards, Michael
>>> _______________________________________________
>>> eclipselink-users mailing list
>>> eclipselink-users@xxxxxxxxxxx
>>> https://dev.eclipse.org/mailman/listinfo/eclipselink-users
>>>
>>
>>
>> _______________________________________________
>> eclipselink-users mailing list
>> eclipselink-users@xxxxxxxxxxx
>> https://dev.eclipse.org/mailman/listinfo/eclipselink-users
> _______________________________________________
> eclipselink-users mailing list
> eclipselink-users@xxxxxxxxxxx
> https://dev.eclipse.org/mailman/listinfo/eclipselink-users
>