DataTables / Editor-NET

.NET Framework and .NET Core server-side libraries for Editor
Other
15 stars 12 forks source link

MJoin with inner LeftJoin, does not put LeftJoin at end. #15

Open VictorioBerra opened 4 months ago

VictorioBerra commented 4 months ago

RE: https://datatables.net/forums/discussion/78882/editor-net-mjoin-leftjoin#latest

I think this is a legit bug.

// ...
  .MJoin(new MJoin("InventoryServerWarranty")
      .Set(false)
      .Link("InventoryServer.Id", "InventoryServerInventoryServerWarrantyLine.InventoryServerId")
      .Link("InventoryServerWarranty.Id", "InventoryServerInventoryServerWarrantyLine.InventoryServerWarrantyId")
      .Order("InventoryServerWarranty.CertificateNumber")
      .Model<Warranty>()
      .LeftJoin("InventoryServerWarrantyProvider", "InventoryServerWarrantyProvider.Id", "=", "InventoryServerWarranty.InventoryServerWarrantyProviderId")
  )
// ...

The Editor generated SQL looks like so:

SELECT
    DISTINCT [InventoryServer].[Id] as 'dteditor_pkey',
    [InventoryServerWarranty].[CertificateNumber] as 'CertificateNumber',
    [InventoryServerWarranty].[RegistrationID] as 'RegistrationID',
    [InventoryServerWarranty].[Notes] as 'Notes',
    [InventoryServerWarranty].[DeliveryDate] as 'DeliveryDate',
    [InventoryServerWarranty].[ExpirationDate] as 'ExpirationDate',
    [InventoryServerWarranty].[InventoryServerWarrantyProviderId] as 'InventoryServerWarrantyProviderId',
    [InventoryServerWarranty].[RetiredIncidentNumber] as 'RetiredIncidentNumber',
    [InventoryServerWarranty].[RetiredOn] as 'RetiredOn'
FROM
    [InventoryServer]
    LEFT JOIN [InventoryServerWarrantyProvider] ON [InventoryServerWarrantyProvider].[Id] = [InventoryServerWarranty].[InventoryServerWarrantyProviderId]
    JOIN [InventoryServerInventoryServerWarrantyLine] ON [InventoryServer].[Id] = [InventoryServerInventoryServerWarrantyLine].[InventoryServerId]
    JOIN [InventoryServerWarranty] ON [InventoryServerWarranty].[Id] = [InventoryServerInventoryServerWarrantyLine].[InventoryServerWarrantyId]
WHERE
    [InventoryServer].[Id] IN (2)
ORDER BY
    [InventoryServerWarranty].[CertificateNumber]

Note how that left join is FIRST. That will not work. Only by pulling that down to the be the final join, does the code work.

AllanJard commented 4 months ago

Thanks for flagging this up. I'll take a look into it - agreed that the order needs to be changed.