SQL query to Aurelius help

BIZ TMS Aurelius
1

Hi Wagner,


I've got a query that I need to run but I'm finding it difficult to understand how to create Criteria for it.  Would you have a moment to give me some pointers?  Here's the query:
select
    D.*
from DRIVERS D
join VEHICLES_PPD PPD on D.ID = PPD.DRIVER_ID and PPD.DISCRIMINATOR = 'DRIVER'
join VEHICLES V on PPD.VEHICLES_ID = V.ID
left join HAULIERS H on D.HAULIER_ID = H.ID
where V.VEHICLE_REG = :VehicleReg and
      D.ENABLED = 1 and
      (H.ID is null or (H.ENABLED = 1))
I have entities for each table and the vehicle class has a lazy loaded list of vehicles_PPD. At the moment the driver class only has an association to the Hauliers.  Vehicles_PDD is used to give a pick list of drivers that can use a vehicle.  I need the results to be in a list of TDriver and I want it to be a TCriteria so that the user can order the Aurelius dataset by added a TOrder when they click the grid header.

Here's what I have so far but how do I add in the vehicles_PPD  without changing the TDriver class? Should I use an SQL Projection? 
  Criteria :=
    FManager.Find<TDriver>
    .CreateAlias('Haulier', 'Haulier')
    .Where(
     TLinq.Eq('Enabled', 1) and
    (TLinq.Eq('Haulier.Enabled', 1) or TLinq.IsNull('Haulier'))
    );

  

2

I'm afraid you can't add Vehicles_PPD without changing TDriver class and adding an association to Vehicles_PPD there. As an alternative you could start your query in Vehicles_PPD class and just read the Vehicles_PDD.Driver property to get your driver?