Substring or equivalent in editor?

davidjmorin

I want to trim the results from 2 tables and match them. I am currently using this in a PDO script and works fine. I am trying to convert all my tables to Datatables Editor but am running into this issue.

->leftJoin( 'FIND_IN_SET(SUBSTRING(disc_rq_activations.Related_SN,1, 13), SUBSTRING(disc_Activations.Device_ID, 1,13))' )

The above throws no errors but fails to load results.

here is my regular php script

FROM disc_rq_activations RQ
LEFT JOIN
   disc_Activations VZ
   ON FIND_IN_SET(SUBSTRING(RQ.Related_SN,1, 13), SUBSTRING(VZ.Device_ID, 1,13))
   #ON IF(length(Related_SN) = 15,FIND_IN_SET(LEFT(Related_SN, length(Related_SN) - 1), VZ.Device_ID),Related_SN)
WHERE
   VZ.Device_ID IS NULL
   AND RQ.District = "'.$region.'"
   AND STR_TO_DATE(RQ.Sold_On, "%b %e, %Y %l:%i %p")  BETWEEN "'.$mindate.'" AND "'.$maxdate.'"
   AND RQ.Product_Name != "ISPU Activation"
   AND RQ.Product_Name != "ISPU New Act"
   GROUP BY RQ.Tracking_, RQ.Related_SN

The above works just fine.

How can I implement this in datatables?

Full datatables script

use PHPMailer\PHPMailer\PHPMailer;



require '../../../vendor/autoload.php';
include( "lib/DataTables.php" );

use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate,
    DataTables\Editor\ValidateOptions;

    Editor::inst( $db, 'disc_rq_activations' )
        ->fields(
                Field::inst( 'disc_rq_activations.ID' ),
                Field::inst( 'disc_rq_activations.Invoice_' ),
                Field::inst( 'disc_rq_activations.Tracking_' ),
                Field::inst( 'disc_rq_activations.Qty' ),
                Field::inst( 'disc_rq_activations.Product_SKU' ),
                Field::inst( 'disc_rq_activations.Product_Name' ),
                Field::inst( 'disc_rq_activations.Unit_Rebate' ),
                Field::inst( 'disc_rq_activations.Related_Product' ),
                Field::inst( 'disc_rq_activations.Related_SKU' ),
                Field::inst( 'disc_rq_activations.Related_SN' ),
                Field::inst( 'disc_rq_activations.Related_Cost' ),
                Field::inst( 'disc_rq_activations.Related_Price' ),
                Field::inst( 'disc_rq_activations.Rate_Plan' ),
                Field::inst( 'disc_rq_activations.Customer' ),
                Field::inst( 'disc_rq_activations.Sales_Person' ),
                Field::inst( 'disc_rq_activations.Sales_Person_ID' ),
                Field::inst( 'disc_rq_activations.Sold_On' ),
                Field::inst( 'disc_rq_activations.Invoiced_At' ),
                Field::inst( 'disc_rq_activations.Original_Invoice' ),
                Field::inst( 'disc_rq_activations.Original_Sales_Date' ),
                Field::inst( 'disc_rq_activations.Contract_' ),
                Field::inst( 'disc_rq_activations.SOC_Code' ),
                Field::inst( 'disc_rq_activations.SOC_Code_2' ),
                Field::inst( 'disc_rq_activations.Extra_Field' ),
                Field::inst( 'disc_rq_activations.Port_Number' ),
                Field::inst( 'disc_rq_activations.Region' ),
                Field::inst( 'disc_rq_activations.District' ),
                Field::inst( 'disc_rq_activations.Vendor_Account_Name' ),
                Field::inst( 'daily_notes.note' ),
                Field::inst( 'daily_notes.note_id' ),



        )

// Need to convert this as it is showing all as null because one column is 18 digits and the other is 16 digits for the most part.  Some IDs are 13 digits. 
    ->leftJoin( 'disc_Activations', 'disc_rq_activations.Related_SN', '=', 'disc_Activations.Device_ID' )
    ->leftJoin( 'daily_notes', 'daily_notes.note_id', '=', 'disc_rq_activations.ID' )
    ->where('disc_Activations.Device_ID', null)


    ->debug( true )
        ->process( $_POST )
        ->json();

davidjmorin

DataTables warning: table id=profit - An SQL error occurred: SQLSTATE[42000]: Syntax error or access violation: 1630 FUNCTION comm_reconcile.SUBSTRING does not exist. Check the 'Function Name Parsing and Resolution' section in the Reference Manual

davidjmorin

Just a bump. Really need to get this figured out and I am lost here.

Lapointe

Hi

In fact leftjoin do not allow a function.
The way is to create a database view and refer it as a table in datatable join description

davidjmorin

@Lapointe I guess im not sure on how to do that. Do you have an example?

allan

There is a VIEW example with Editor available here.

Allan

davidjmorin

@allan thank you for the example. I am still lost on how to use this in my example.

colin

You create the view in the database, which looks like a standard table to Editor. You then configure Editor to use that as you would any other table. Hope that helps. If not, please can you be more specific on the problem,

Colin