Union query

dynasoft

Hi

I'm trying to build some editor server code from the following sql query:

SELECT A.ServiceName As Res1, 'Global list' As Res2, '0' As Res3, A.ID As Res4 FROM ServiceLists AS A WHERE A.CustomerIndex = 0
UNION ALL SELECT A.ServiceName As Res1, C.CustomerNumber As Res2, C.CustID As Res3, A.ID As Res4 FROM ServiceLists AS A
INNER JOIN Customers AS C ON A.CustomerIndex = C.CustID WHERE C.Dealer = 1

How do I build the server code? Here's what I have in the where/leftjoin parts of the server code:

                editor.Where(q =>
                   q.Where("ServiceLists.CustomerIndex", 0).OrWhere("ServiceLists.CustomerIndex", lngCustIdx)
                );
                editor.Where("Customers.Dealer", lngDistIdx);
                editor.LeftJoin("DistributorCommissions", "ServiceLists.id", "=", "DistributorCommissions.ByItemPOSResidualServiceID");
                editor.LeftJoin("Customers", "ServiceLists.CustomerIndex", "=", "Customers.CustID");

How do I include the Union and build the condition where the records that need showing are where CustomerIndex = 0 and where ServiceLists shows records where dealer ID 1 has customers present in ServiceLists.

Thanks.

dynasoft

The code I included actually works fine. Thanks.

rf1234

If your conditions are really static (CustomerIndex = 0 and dealer ID = 1) then it is really easy: Just put your query into an SQL view and query the view with Editor.

Editor can't do UNION, INNER JOIN etc. In an options instance you can't even use a LEFT JOIN as far as I remember.

If you need something more complex you can either use views or other work arounds like your own proprietary queries inside a getFormatter etc. Views are the best option in my opinion.

CREATE VIEW your_view AS
SELECT A.ServiceName As Res1, 'Global list' As Res2, '0' As Res3, A.ID As Res4 
FROM ServiceLists AS A 
WHERE A.CustomerIndex = 0
       UNION ALL 
SELECT A.ServiceName As Res1, C.CustomerNumber As Res2, C.CustID As Res3, A.ID As Res4 
FROM ServiceLists AS A
INNER JOIN Customers AS C ON A.CustomerIndex = C.CustID 
WHERE C.Dealer = 1

allan

You can do a left Join with the Editor libraries, but not UNIONS or a RIGHT JOIN. For that, as you say a VIEW would need to be used.

Allan

rf1234

@allan, LEFT JOIN yes, but not in an options instance, right? Or has that changed?
https://editor.datatables.net/manual/php/joins

dynasoft

Thank you.