Converting MySQL Query to the syntax for JQuery Datatable Editor

jdpassiveincome2

Greetings,

I'm new to Jquery Datatable Editor and I need to run the following query within Jquery Datatable Editor. Can someone please assist?

SELECT
t1.techid,
t1.techname,
t1.manager,
t1.dispatcher,
t1.cellphone,
t2.SC_SCTechID,
t2.BCARNA,
t2.SC_CallStatus,
t2.Serial_ID,
t2.Age,
t2.SC_CallTimestamp,
COALESCE(t3.num_of_dispatch, 0) AS num_of_dispatch,
COALESCE(t4.num_of_hold, 0) AS num_of_hold,
COALESCE(t5.num_of_credit, 0) AS num_of_credit,
COALESCE(t6.num_of_open, 0) AS num_of_open,
COALESCE(t7.num_of_total, 0) AS num_of_total
FROM serviceteam t1
LEFT JOIN ato_openservicecalls t2
ON t1.techid = t2.SC_SCTechID AND t2.SC_CallStatus = 'Dispatch'
LEFT JOIN
(
SELECT t1.techid, COUNT(t2.Serial_ID) AS num_of_dispatch
FROM serviceteam t1
LEFT JOIN ato_openservicecalls t2
ON t1.techid = t2.SC_SCTechID
WHERE t2.SC_CallStatus = 'Dispatch'
GROUP BY
t1.techid
)
t3 ON t1.techid = t3.techid
LEFT JOIN
(
SELECT t1.techid, COUNT(t2.Serial_ID) AS num_of_hold
FROM serviceteam t1
LEFT JOIN ato_openservicecalls t2
ON t1.techid = t2.SC_SCTechID
WHERE t2.SC_CallStatus = 'Hold for'
GROUP BY
t1.techid

)
t4 ON t1.techid = t4.techid
LEFT JOIN
(
SELECT t1.techid, COUNT(t2.Serial_ID) AS num_of_credit
FROM serviceteam t1
LEFT JOIN ato_openservicecalls t2
ON t1.techid = t2.SC_SCTechID
WHERE t2.SC_CallStatus = 'CREDIT'
GROUP BY
t1.techid

)
t5 ON t1.techid = t5.techid
LEFT JOIN
(
SELECT t1.techid, COUNT(t2.Serial_ID) AS num_of_open
FROM serviceteam t1
LEFT JOIN ato_openservicecalls t2
ON t1.techid = t2.SC_SCTechID
WHERE t2.SC_CallStatus = 'Open'
GROUP BY
t1.techid

)
t6 ON t1.techid = t6.techid
LEFT JOIN
(
SELECT t1.techid, COUNT(t2.Serial_ID) AS num_of_total
FROM serviceteam t1
LEFT JOIN ato_openservicecalls t2
ON t1.techid = t2.SC_SCTechID
GROUP BY
t1.techid

)
t7 ON t1.techid = t7.techid
");

Here's my attempt but I don't know how to do the counts from the above query....

Editor::inst( $db, 'serviceteam')
->fields(
Field::inst( 'serviceteam.techid' )->validator( 'Validate::notEmpty' ),
Field::inst( 'serviceteam.techname' )->validator( 'Validate::notEmpty' ),
Field::inst( 'serviceteam.manager' )->validator( 'Validate::notEmpty' ),
Field::inst( 'serviceteam.dispatcher' )->validator( 'Validate::notEmpty' ),
Field::inst( 'serviceteam.cellphone' )->validator( 'Validate::notEmpty' ),
Field::inst( 'serviceteam.serviceteamstatus' )->validator( 'Validate::notEmpty' ),
Field::inst('ato_openservicecalls.SC_SCTechID')->validator("Validate::notEmpty"),
Field::inst('ato_openservicecalls.SC_CallStatus')->validator("Validate::notEmpty"),
Field::inst('ato_openservicecalls.BCARNA')->validator("Validate::notEmpty"),
Field::inst('ato_openservicecalls.Age')->validator("Validate::notEmpty"),
Field::inst('ato_openservicecalls.SC_CallTimestamp')->validator("Validate::notEmpty"),
Field::inst('ato_openservicecalls.Serial_ID')->validator("Validate::notEmpty"),
Field::inst('ato_openservicecalls.SC_CallComment')->validator("Validate::notEmpty")
)
->leftJoin('ato_openservicecalls', 'ato_openservicecalls.SC_SCTechID', '=', 'serviceteam.techid AND (ato_openservicecalls.SC_CallStatus = "Dispatch")')
->process( $_POST )
->json();

allan

Editor's server-side libraries don't attempt to provide methods to access everything that can be done in SQL. Instead, what to do is create a VIEW for your complex SQL expression and then use the readTable method to tell the library the name of the VIEW it should read from. You'll need to specify the fields to read as regular fields (based on their returned names from the VIEW).

Allan

jdpassiveincome2

Do you have an example on how to use the readTable method?

allan

You coudl use something like:

Editor::inst( $db, 'demo' )
    ->fields(
        Field::inst( 'first_name' )->set(false),
        Field::inst( 'last_name' )->get(false),
        Field::inst( 'position' ),
        Field::inst( 'email' ),
        Field::inst( 'office' )
    )
    ->readTable( 'readView' )
    ->process( $_POST )
    ->json();

I've used the get and set methods just to show how you can cause fields to be read from or not, and set or not.

Allan