Substring or equivalent in editor?

Substring or equivalent in editor?

davidjmorindavidjmorin Posts: 101Questions: 31Answers: 0

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();

This question has an accepted answers - jump to answer

Answers

  • davidjmorindavidjmorin Posts: 101Questions: 31Answers: 0
    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
    
  • davidjmorindavidjmorin Posts: 101Questions: 31Answers: 0

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

  • LapointeLapointe Posts: 430Questions: 81Answers: 4
    edited December 2020

    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

  • davidjmorindavidjmorin Posts: 101Questions: 31Answers: 0

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

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin
    Answer ✓
  • davidjmorindavidjmorin Posts: 101Questions: 31Answers: 0

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

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

    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

This discussion has been closed.