Populating the table with RAWSQL query

Populating the table with RAWSQL query

Khalid TeliKhalid Teli Posts: 251Questions: 71Answers: 0
edited July 2020 in Free community support

I am using the raw sql query below to populate some columns in the datatble which is already created using Editor data-tables. I am not sure if I am doing it correctly. I believe that the** if ( ! isset( $_POST['action'] ) ) {** is not working as it should work.

How should the JSON response look like?

In my case I would like JSON to contain all the data 1) From RAW sql to populate few columns and 2) From Editor to populate renaming columns (else condition).

p.s i tried to remove the if condition so i can read all the data in one JSON and then handle them differently on client side but it is showing me** Error: Parse error**

**// if ( ! isset( $_POST['action'] ) ) {
   $rawquery = "SELECT start  AS time 
                FROM contract_bal";
   $data = $db->sql( $rawquery )->fetchAll();
   echo json_encode( array(
      'data' => $data
   ) );
 
// }else{
......editor stuff
}**    

and on the client side i included this:

**columns[
......
 
                     { data: "time" }
.....
]**

This question has an accepted answers - jump to answer

Answers

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    What is the response from the server? The details in this tech note will show you how to get that.

    Allan

  • Khalid TeliKhalid Teli Posts: 251Questions: 71Answers: 0
    edited July 2020

    Thank you @allan

    1) when i don't use the if condition , the output looks like below, it reads both from RAWSQL ans EDITOR and the error it shows id parse error on line 9 in bold

      {
            "data": [{
                "starty": "1234"
            }, {
                "starty": "1234"
            }, {
                "starty": "1234"
            }]
       ** } {**
            "data": [{
                    "DT_RowId": "row_100758",
                    "contract_bal": {
                        "contract_id": "100758",
                        "member_name": "Foodservice",
                        "opt_one": "1190",
                        "opt_two": "0",
                        "opt_three": "0",
                        "opt_four": "0",
                        "opt_five": "0",
                        "opt_six": "0",
                        "monthly_input": "5231",
                        "start_date": "2019-10-01",
                        "end_date": "2020-09-30",
                        "contractinfo_id_fk": "4",
                        "product_code_fk2": "D230"
                    },
                    "contract_info": {
                        "contract_prod": "Tomatoes"
                    },
                    "products": {
                        "product_name": " Chopped Tomatoes",
                        "supplier_id_fk": "1068"
                    },
                    "supplier": {
                        "supplier_name": "Martin"
                    }
                }
            }],
        "options": {
            "contract_bal.contractinfo_id_fk": [{
                "label": "Freeze_Chill_Fries",
                "value": "26"
            }, {
                "label": "Fries",
                "value": "25"
            } {
                "label": "Tomatoes",
                "value": "14"
            }],
            "contract_bal.product_code_fk2": [{
                "label": "  Olive Oil      ",
                "value": "9260"
            } {
                "label": " Seafood",
                "value": "1123"
                                            
            }]
        },
        "files": [],
        "searchPanes": {
            "options": []
        },
        "draw": 1,
        "recordsTotal": "177",
        "recordsFiltered": "177"
        }
    

    2) when i put the if condition **~~(if ( isset( $_POST['action'] ) ) {)~~** it just, i guess it takes both RAWSQL ans Editor as post action and error shows unknown field for "starty" and for **~~(if ( ! isset( $_POST['action'] ) ) {)~~** there is no response

    3) Then I tried just using the RAW SQL query on server-side and successfully populate the datatatable but the problem is that there is no searching facility. I have to disable the server-side from client side in order to search the datatable

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    EDITOR and the error it shows id parse error on line 9 in bold

    That looks like it is sending two different JSON objects back. That isn't valid JSON.

    Also the empty return from 2 isn't valid JSON either, hence the error you are seeing. So there are some issues with the server-side script. Can you show me what you have please? Are you using our PHP libraries?

    Allan

  • Khalid TeliKhalid Teli Posts: 251Questions: 71Answers: 0

    @allan Thank you.
    yes, I use PHP libraries.
    you are right, it returns two JSON objects which are concatenated and therefore resultant JSON is not a valid JSON.

    Please find my server-side script below

        <?php
    
        // DataTables PHP library
        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,
            DataTables\Database,
               DataTables\Database\Query,
               DataTables\Database\Result;
    
    
        if ( isset(! $_POST['action'] ) ) {
          $rawquery = "SELECT start as starty FROM contract_bal";
           $data = $db->sql( $rawquery )->fetchAll();
        echo json_encode( [
          'data' => $data
        ] );
        }
    
        else {
    
        // Build our Editor instance and process the data coming from _POST
        Editor::inst( $db, 'contract_bal', 'contract_id' )
            ->fields(
                // Field::inst( 'contract_id' )
          //           ->set( false ),
                Field::inst( 'contract_bal.contract_id' )
                    ->validator( Validate::notEmpty( ValidateOptions::inst()
                        ->message( 'A Contract ID is required' )    
                    ) ),
                Field::inst( 'contract_bal.member_name' )
                    ->validator( Validate::notEmpty( ValidateOptions::inst()
                        ->message( 'A member name is required' )    
                    ) ),
                Field::inst( 'contract_bal.opt_one' ),
                Field::inst( 'contract_bal.opt_two' ),
                Field::inst( 'contract_bal.opt_three' ),
                Field::inst( 'contract_bal.opt_four' ),
                Field::inst( 'contract_bal.opt_five' ),
                Field::inst( 'contract_bal.opt_six' ),
                Field::inst( 'contract_bal.monthly_input' ),
                Field::inst( 'contract_bal.start_date' )
                    ->validator( Validate::dateFormat( 'Y-m-d' ) )
                    ->getFormatter( Format::dateSqlToFormat( 'Y-m-d' ) )
                    ->setFormatter( Format::dateFormatToSql('Y-m-d' ) ),
                Field::inst( 'contract_bal.end_date' )
                    ->validator( Validate::dateFormat( 'Y-m-d' ) )
                    ->getFormatter( Format::dateSqlToFormat( 'Y-m-d' ) )
                    ->setFormatter( Format::dateFormatToSql('Y-m-d' ) ),
    
         Field::inst( 'contract_bal.contractinfo_id_fk' )
                    ->options( Options::inst()
                        ->table( 'contract_info' )
                        ->value( 'contract_id' )
                        ->label( 'contract_prod' )
                    )
                    ->validator( Validate::dbValues() ),
    
         Field::inst( 'contract_info.contract_prod' ),
    
    
    
         Field::inst( 'contract_bal.product_code_fk2' )
                    ->options( Options::inst()
                        ->table( 'products' )
                        ->value( 'product_code' )
                        ->label( 'product_name' )
                    )
                    ->validator( Validate::dbValues() ),
    
         Field::inst( 'products.product_name' ),
    
          Field::inst( 'products.supplier_id_fk' )
                    ->options( Options::inst()
                        ->table( 'supplier' )
                        ->value( 'supplier_id' )
                        ->label( 'supplier_name' )
                    )
                    ->validator( Validate::dbValues() ),
                     Field::inst( 'supplier.supplier_name' ),
    
         // Field::inst( 'start' )
    
    
    
            )
    
    
             ->leftJoin( 'contract_info', 'contract_info.contract_id', '=', 'contract_bal.contractinfo_id_fk' )
              ->leftJoin( 'products', 'products.product_code', '=', 'contract_bal.product_code_fk2' )
                ->leftJoin( 'supplier', 'supplier.supplier_id', '=', 'products.supplier_id_fk' )
    
    
            ->process( $_POST )
            ->json();
          }
    
  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    It is as if the if / else is executing both blocks! I think this line is wrong though:

    if ( isset(! $_POST['action'] ) ) {
    

    It should be:

    if ( ! isset($_POST['action'] ) ) {
    

    Allan

  • Khalid TeliKhalid Teli Posts: 251Questions: 71Answers: 0
    edited July 2020

    @allan
    Thank you. That was a silly mistake from my side. However, I changed this and nothing changed.

    It seems when it is not set ( if ( ! isset($_POST['action'] ) ) { ) it ready the data from raw sql query in if statement and doesn't go out of if loop to else statement.

    please look at the error below:

    **DataTables warning: table id=contracts - Requested unknown parameter 'contract_bal.contract_id' for row 0, column 0. For more information about this error, please see http://datatables.net/tn/4**

    Just in case you want to see how my 'columns' look from client side

                        "columns": [
    
    
                        { data: "contract_bal.contract_id" },
                        { data: "contract_bal.member_name"},
                        { data: "contract_bal.opt_one" },
                       { data: "contract_bal.opt_two" },
                                    { data: "contract_bal.opt_three" },
                                    { data: "contract_bal.opt_four" },
                                    { data: "contract_bal.opt_five" },
                                    { data: "contract_bal.opt_six" },
                                    { data: "contract_bal.monthly_input" },
                                    { data: "contract_bal.start_date" },
                                    { data: "contract_bal.end_date" },
                                    { data: "contract_info.contract_prod" },
                                    { data: "products.product_name" },
                                    { data: null,
                                     render: function (data, type, row)
                                        {
                                          //  var numFormat = $.fn.dataTable.render.number( ",", ".", 0 ).display;
                                            var open_balance = parseInt(data.contract_bal.opt_one) + parseInt(data.contract_bal.opt_two)+
                                            parseInt(data.contract_bal.opt_three) + parseInt(data.contract_bal.opt_four)+
                                            parseInt(data.contract_bal.opt_five) + parseInt(data.contract_bal.opt_six);
    
                                            return open_balance;
                                        }},
    
                                    { data: null,
                                     render: function (data, type, row)
                                        {
                                          //  var numFormat = $.fn.dataTable.render.number( ",", ".", 0 ).display;
                                            var remaining_balance = (parseInt(data.contract_bal.opt_one) + parseInt(data.contract_bal.opt_two)+
                                            parseInt(data.contract_bal.opt_three) + parseInt(data.contract_bal.opt_four)+
                                            parseInt(data.contract_bal.opt_five) + parseInt(data.contract_bal.opt_six)) - 
                                            parseInt(data.contract_bal.monthly_input);
    
                                            return remaining_balance;
                                        }},
    
    
                                    { data: null,
                                     render: function (data, type, row)
                                        {
                                          //  var numFormat = $.fn.dataTable.render.number( ",", ".", 0 ).display;
                                            var per_remaining_balance = ((((parseInt(data.contract_bal.opt_one) + parseInt(data.contract_bal.opt_two)+
                                            parseInt(data.contract_bal.opt_three) + parseInt(data.contract_bal.opt_four)+
                                            parseInt(data.contract_bal.opt_five) + parseInt(data.contract_bal.opt_six)) - 
                                            parseInt(data.contract_bal.monthly_input)) / 
    
                                            (parseInt(data.contract_bal.opt_one) + parseInt(data.contract_bal.opt_two)+
                                            parseInt(data.contract_bal.opt_three) + parseInt(data.contract_bal.opt_four)+
                                            parseInt(data.contract_bal.opt_five) + parseInt(data.contract_bal.opt_six))) * 100 )
    
    
                                            return Math.round(per_remaining_balance)+'%';
    
                                        }},
    
                                    { data: null,
                                     render: function (data, type, row)
    
                                         {
    
                                          var start_date = new Date(row.contract_bal.start_date);
                                          var end_date = new Date(row.contract_bal.end_date);
                                          var today = new Date() ;
    
                                          var sec_start = start_date.getTime();
                                          var sec_end = end_date.getTime();
                                          var sec_today = today.getTime();
    
    
                                          var total = sec_end - sec_start;
                                           var elapsed = sec_today - sec_start;
                                           var percent = Math.round(((total-elapsed)/total * 100)) + '%';
    
    
    
                                            return percent ;
    
    
    
                                        }},
                                    { data: null,
                                                render: function (data, type, row)
                                        {
                                          //  var numFormat = $.fn.dataTable.render.number( ",", ".", 0 ).display;
    
                                          //opening balance - remaining balance divided by no of months 
                                            var monthly_avg = 
    
                                            (parseInt(data.contract_bal.opt_one) + parseInt(data.contract_bal.opt_two)+
                                            parseInt(data.contract_bal.opt_three) + parseInt(data.contract_bal.opt_four)+
                                            parseInt(data.contract_bal.opt_five) + parseInt(data.contract_bal.opt_six) - 
    
    
                                           ( (parseInt(data.contract_bal.opt_one) + parseInt(data.contract_bal.opt_two)+
                                            parseInt(data.contract_bal.opt_three) + parseInt(data.contract_bal.opt_four)+
                                            parseInt(data.contract_bal.opt_five) + parseInt(data.contract_bal.opt_six) - 
                                            parseInt(data.contract_bal.monthly_input))))
    
    
    
    
                                            return Math.round((monthly_avg)/12);
    
                                        }},
    
                                        { data: "supplier.supplier_name" },
    
                                        { data: "starty" }
    
    
    
    
                                ],
    
  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    Can you show me the corrected PHP code? In the if statement above you are only selecting the start column and returning that. So yes, if you tried to tell the DataTable to display anything else, then it would error with the message you are seeing.

    Allan

  • Khalid TeliKhalid Teli Posts: 251Questions: 71Answers: 0
    edited August 2020

    @allan Thank you. I am posting my code below. What I am trying to do is , populate one of the columns in datatable with Raw sql query ('SELECT start as starty from contract_bal') and the rest of columns to be filled by Editor instance

    So, is there a way to populate one datatble with both raw sql and editor instance at same time? or in other words can we populate editor with rawsql?

        <?php
    
        // DataTables PHP library
        include( "../lib/DataTables.php" );
    
        // Alias Editor classes so they are easy to use
        use
            DataTables\Editor,
            DataTables\Editor\Field,
            DataTables\Editor\Format,
            DataTables\Editor\Mjoin,
            DataTables\Editor\Options,
            DataTables\Editor\Upload,
            DataTables\Editor\Validate,
            DataTables\Editor\ValidateOptions;
    
    
        if ( ! isset($_POST['action'] ) ) {
          $rawquery = "SELECT start as starty from contract_bal";
           $data = $db->sql( $rawquery )->fetchAll();
        echo json_encode( [
          'data' => $data
        ] );
        }
    
        else {
    
        // Build our Editor instance and process the data coming from _POST
        Editor::inst( $db, 'contract_bal', 'contract_id' )
            ->fields(
                // Field::inst( 'contract_id' )
          //           ->set( false ),
                Field::inst( 'contract_bal.contract_id' )
                    ->validator( Validate::notEmpty( ValidateOptions::inst()
                        ->message( 'A Contract ID is required' )    
                    ) ),
                Field::inst( 'contract_bal.member_name' )
                    ->validator( Validate::notEmpty( ValidateOptions::inst()
                        ->message( 'A member name is required' )    
                    ) ),
                Field::inst( 'contract_bal.opt_one' ),
                Field::inst( 'contract_bal.opt_two' ),
                Field::inst( 'contract_bal.opt_three' ),
                Field::inst( 'contract_bal.opt_four' ),
                Field::inst( 'contract_bal.opt_five' ),
                Field::inst( 'contract_bal.opt_six' ),
                Field::inst( 'contract_bal.monthly_input' ),
                Field::inst( 'contract_bal.start_date' )
                    ->validator( Validate::dateFormat( 'Y-m-d' ) )
                    ->getFormatter( Format::dateSqlToFormat( 'Y-m-d' ) )
                    ->setFormatter( Format::dateFormatToSql('Y-m-d' ) ),
                Field::inst( 'contract_bal.end_date' )
                    ->validator( Validate::dateFormat( 'Y-m-d' ) )
                    ->getFormatter( Format::dateSqlToFormat( 'Y-m-d' ) )
                    ->setFormatter( Format::dateFormatToSql('Y-m-d' ) ),
    
         Field::inst( 'contract_bal.contractinfo_id_fk' )
                    ->options( Options::inst()
                        ->table( 'contract_info' )
                        ->value( 'contract_id' )
                        ->label( 'contract_prod' )
                    )
                    ->validator( Validate::dbValues() ),
    
         Field::inst( 'contract_info.contract_prod' ),
    
    
    
         Field::inst( 'contract_bal.product_code_fk2' )
                    ->options( Options::inst()
                        ->table( 'products' )
                        ->value( 'product_code' )
                        ->label( 'product_name' )
                    )
                    ->validator( Validate::dbValues() ),
    
         Field::inst( 'products.product_name' ),
    
          Field::inst( 'products.supplier_id_fk' )
                    ->options( Options::inst()
                        ->table( 'supplier' )
                        ->value( 'supplier_id' )
                        ->label( 'supplier_name' )
                    )
                    ->validator( Validate::dbValues() ),
                     Field::inst( 'supplier.supplier_name' )
    
    
    
            )
    
    
             ->leftJoin( 'contract_info', 'contract_info.contract_id', '=', 'contract_bal.contractinfo_id_fk' )
              ->leftJoin( 'products', 'products.product_code', '=', 'contract_bal.product_code_fk2' )
                ->leftJoin( 'supplier', 'supplier.supplier_id', '=', 'products.supplier_id_fk' )
    
            ->process( $_POST )
            ->json();
          }
    
  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    What I am trying to do is , populate one of the columns in datatable with Raw sql query ('SELECT start as starty from contract_bal') and the rest of columns to be filled by Editor instance

    Right - but what the code is doing above is just returning starty in a data array. It can’t merge the data from the two different parts of the if statement! Only one half of it runs after all.

    So, is there a way to populate one datatble with both raw sql and editor instance at same time? or in other words can we populate editor with rawsql?

    You can certainly populate with raw SQL, but you can’t do both raw SQL and the Editor instance at the same time.

    I don’t actually understand what the goal is here. Why not just include Field::inst( 'contract_bal.start' ), in your field list?

    Allan

  • Khalid TeliKhalid Teli Posts: 251Questions: 71Answers: 0
    edited August 2020

    @allan Thank you for the clarification. Much appreciated!
    1) This was for the test purpose , just to try if we can populate with both RAW SQL and EDITOR instance. My final goal was to perform some calculations with RAW SQL and then pass them to Editor instance which I can read from client side.

    a) However, is there a way to include SQL inside field instance inside here Field::inst( 'contract_bal.start' )
    **
    **b) or is it possible to use php code inside the field instance like we used to do in Datatbles using ssp class to perform some operations , for example , in the code below i am reading 'testing' column and perform some operations using php ****

    ! array(
    ! 'db' => 'testing',
    ! 'dt' => 13,
    ! 'formatter' => function( $d, $row ) {
    !
    !
    ! $splitstr = str_split($d,10);
    !
    ! $split_one = $splitstr[0];
    ! $split_two = $splitstr[1];
    !
    ! $start_date = strtotime(str_replace('/', '-', $split_one));
    ! $end_date = strtotime(str_replace('/', '-', $split_two));
    ! $today = time() ;
    !
    ! $total = $end_date - $start_date;
    ! $elapsed = $today - $start_date;
    ! $percent = round(($total-$elapsed)/$total * 100);
    !
    ! return sprintf($percent).'%';
    !
    ! }

    2) I successfully managed to populate my DataTable just with RAWSQL . However, the problem I face here is that when turning theServerside:'true' the searching doesn't work and only works when serverside is set to false .
    Below is my server-side script I used

        <?php
    
        // DataTables PHP library
        include( "../lib/DataTables.php" );
    
        // Alias Editor classes so they are easy to use
        use
            DataTables\Editor,
            DataTables\Editor\Field,
            DataTables\Editor\Format,
            DataTables\Editor\Mjoin,
            DataTables\Editor\Options,
            DataTables\Editor\Upload,
            DataTables\Editor\Validate,
            DataTables\Editor\ValidateOptions,
            DataTables\Database,
                DataTables\Database\Query,
               DataTables\Database\Result;
    
    
          $rawquery = "SELECT contract_info.contract_prod, products.product_name, supplier.supplier_name, contract_bal.contract_id, contract_bal.start_date, contract_bal.end_date, contract_bal.member_name, 
    
    
        SUM((contract_bal.opt_one + contract_bal.opt_two + contract_bal.opt_three + contract_bal.opt_four + contract_bal.opt_five + contract_bal.opt_six)) as total_opn_bal,
    
        sum(((contract_bal.opt_one + contract_bal.opt_two + contract_bal.opt_three + contract_bal.opt_four + contract_bal.opt_five + contract_bal.opt_six) - monthly_input)) as total_rem_bal,
    
        sum(((contract_bal.opt_one + contract_bal.opt_two + contract_bal.opt_three + contract_bal.opt_four + contract_bal.opt_five + contract_bal.opt_six) - monthly_input)) / SUM((contract_bal.opt_one + contract_bal.opt_two + contract_bal.opt_three + contract_bal.opt_four + contract_bal.opt_five + contract_bal.opt_six))*100 as total_rem_bal_per,
    
        SUM((contract_bal.opt_one + contract_bal.opt_two + contract_bal.opt_three + contract_bal.opt_four + contract_bal.opt_five + contract_bal.opt_six)) - 
    
        sum(((contract_bal.opt_one + contract_bal.opt_two + contract_bal.opt_three + contract_bal.opt_four + contract_bal.opt_five + contract_bal.opt_six) - monthly_input)) as monthly_avg
    
    
        FROM contract_bal
    
        LEFT JOIN products
        ON   contract_bal.product_code_fk2 = products.product_code
    
        LEFT JOIN contract_info
        ON   contract_info.contract_id = contract_bal.contractinfo_id_fk 
    
        LEFT JOIN Supplier
        ON products.supplier_id_fk  = supplier.supplier_id group by product_name";
    
        $data = $db->sql( $rawquery )->fetchAll();
    
    
         echo json_encode( [
           "data" => $data
         ] );
    
  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin
    Answer ✓

    You can use an SQL function in the Editor Field information .e.g.:

    Field::inst(‘SUM(...)’, ‘mySum’)
      ->set(false)
    

    See the documentation for that here.

    Allan

  • Khalid TeliKhalid Teli Posts: 251Questions: 71Answers: 0

    @allan Thank you very much. You are just amazing!!!

  • Khalid TeliKhalid Teli Posts: 251Questions: 71Answers: 0
    edited August 2020

    Hi @allan

    Is there any reason, why this code works fine but the next one doesnt work at all and return null.
    As you can see from the code (which doesnt work) I add one more column value after / (division)/(contract_bal.opt_one + contract_bal.opt_two + contract_bal.opt_five + contract_bal.opt_three ) when this is restricted to three values it works , anything more than that it returns null

    This works fine:

     Field::inst('((contract_bal.opt_one + contract_bal.opt_two + contract_bal.opt_three + contract_bal.opt_four + contract_bal.opt_five + contract_bal.opt_six) - (contract_bal.monthly_input)) / (contract_bal.opt_one + contract_bal.opt_two + contract_bal.opt_five   ) * 100', 'per_rem_bal1' )
                  ->set(false)
    

    This doesnt work :

     Field::inst('((contract_bal.opt_one + contract_bal.opt_two + contract_bal.opt_three + contract_bal.opt_four + contract_bal.opt_five + contract_bal.opt_six) - (contract_bal.monthly_input)) / (contract_bal.opt_one + contract_bal.opt_two + contract_bal.opt_five + contract_bal.opt_three  ) * 100', 'per_rem_bal1' )
                  ->set(false)
    
  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    I don't know why that would happen off the top of my head I'm afraid.

    Just before the ->process( $_POST ) line, add ->debug(true) and then reload the page. The JSON response will now include the SQL that has been generated by the libraries. What does that look like?

    Allan

  • tefdattefdat Posts: 42Questions: 7Answers: 3

    @allan

    You can certainly populate with raw SQL, but you can’t do both raw SQL and the > > Editor instance at the same time.

    As mentioned in this entry, I want to ask If there is really now way to use RAW SQL queries but continuing using the Editor instance?
    for my case I must populate the search pattern and build the sql query as like here:

    SELECT * FROM s2l.brs Where Match(fts_obverse, fts_reverse) Against ("+word1* +word2 +word3* +word*" in boolean mode)
    
  • Khalid TeliKhalid Teli Posts: 251Questions: 71Answers: 0
    edited August 2020

    @allan

    This code inside the Editor field works fine

    Field::inst('((contract_bal.opt_one + contract_bal.opt_two + contract_bal.opt_three + contract_bal.opt_four + contract_bal.opt_five + contract_bal.opt_six) - (contract_bal.monthly_input)) / (contract_bal.opt_one + contract_bal.opt_two + contract_bal.opt_three) * 100', 'per_rem_bal1' )
                  ->set(false)
    

    Here is the relevant SQL code from debug

    {
            "query": "SELECT  `contract_bal`.`contract_id` as 'contract_bal.contract_id', `contract_bal`.`member_name` as 'contract_bal.member_name', `contract_bal`.`opt_one` as 'contract_bal.opt_one', `contract_bal`.`opt_two` as 'contract_bal.opt_two', `contract_bal`.`opt_three` as 'contract_bal.opt_three', `contract_bal`.`opt_four` as 'contract_bal.opt_four', `contract_bal`.`opt_five` as 'contract_bal.opt_five', `contract_bal`.`opt_six` as 'contract_bal.opt_six', `contract_bal`.`monthly_input` as 'contract_bal.monthly_input', `contract_bal`.`start_date` as 'contract_bal.start_date', `contract_bal`.`end_date` as 'contract_bal.end_date', `contract_bal`.`contractinfo_id_fk` as 'contract_bal.contractinfo_id_fk', `contract_info`.`contract_prod` as 'contract_info.contract_prod', `contract_bal`.`product_code_fk2` as 'contract_bal.product_code_fk2', `products`.`product_name` as 'products.product_name', `products`.`supplier_id_fk` as 'products.supplier_id_fk', `supplier`.`supplier_name` as 'supplier.supplier_name', (contract_bal.opt_one + contract_bal.opt_two + contract_bal.opt_three + contract_bal.opt_four + contract_bal.opt_five + contract_bal.opt_six) as '(contract_bal.opt_one + contract_bal.opt_two + contract_bal.opt_three + contract_bal.opt_four + contract_bal.opt_five + contract_bal.opt_six)', (contract_bal.opt_one + contract_bal.opt_two + contract_bal.opt_three + contract_bal.opt_four + contract_bal.opt_five + contract_bal.opt_six) - (contract_bal.monthly_input) as '(contract_bal.opt_one + contract_bal.opt_two + contract_bal.opt_three + contract_bal.opt_four + contract_bal.opt_five + contract_bal.opt_six) - (contract_bal.monthly_input)', ((contract_bal.opt_one + contract_bal.opt_two + contract_bal.opt_three + contract_bal.opt_four + contract_bal.opt_five + contract_bal.opt_six) - (contract_bal.monthly_input)) \/ (contract_bal.opt_one + contract_bal.opt_two + contract_bal.opt_three) * 100 as '((contract_bal.opt_one + contract_bal.opt_two + contract_bal.opt_three + contract_bal.opt_four + contract_bal.opt_five + contract_bal.opt_six) - (contract_bal.monthly_input)) \/ (contract_bal.opt_one + contract_bal.opt_two + contract_bal.opt_three) * 100' FROM  `contract_bal` LEFT JOIN `contract_info` ON `contract_info`.`contract_id` = `contract_bal`.`contractinfo_id_fk`  LEFT JOIN `products` ON `products`.`product_code` = `contract_bal`.`product_code_fk2`  LEFT JOIN `supplier` ON `supplier`.`supplier_id` = `products`.`supplier_id_fk` WHERE `contract_bal`.`member_name` like :where_0  ORDER BY `contract_bal`.`contract_id`  asc ",
            "bindings": [{
                "name": ":where_0",
                "value": "%xyz Foodservice%",
                "type": null
            }]
        }
    

    For this code inside the Editor field, it doesn't work

    Field::inst('((contract_bal.opt_one + contract_bal.opt_two + contract_bal.opt_three + contract_bal.opt_four + contract_bal.opt_five + contract_bal.opt_six) - (contract_bal.monthly_input)) / (contract_bal.opt_one + contract_bal.opt_two + contract_bal.opt_three + contract_bal.opt_four + contract_bal.opt_five + contract_bal.opt_six) * 100', 'per_rem_bal1' )
                  ->set(false)
    

    Here is the relevant SQL code from debug

     {
            "query": "SELECT  `contract_bal`.`contract_id` as 'contract_bal.contract_id', `contract_bal`.`member_name` as 'contract_bal.member_name', `contract_bal`.`opt_one` as 'contract_bal.opt_one', `contract_bal`.`opt_two` as 'contract_bal.opt_two', `contract_bal`.`opt_three` as 'contract_bal.opt_three', `contract_bal`.`opt_four` as 'contract_bal.opt_four', `contract_bal`.`opt_five` as 'contract_bal.opt_five', `contract_bal`.`opt_six` as 'contract_bal.opt_six', `contract_bal`.`monthly_input` as 'contract_bal.monthly_input', `contract_bal`.`start_date` as 'contract_bal.start_date', `contract_bal`.`end_date` as 'contract_bal.end_date', `contract_bal`.`contractinfo_id_fk` as 'contract_bal.contractinfo_id_fk', `contract_info`.`contract_prod` as 'contract_info.contract_prod', `contract_bal`.`product_code_fk2` as 'contract_bal.product_code_fk2', `products`.`product_name` as 'products.product_name', `products`.`supplier_id_fk` as 'products.supplier_id_fk', `supplier`.`supplier_name` as 'supplier.supplier_name', (contract_bal.opt_one + contract_bal.opt_two + contract_bal.opt_three + contract_bal.opt_four + contract_bal.opt_five + contract_bal.opt_six) as '(contract_bal.opt_one + contract_bal.opt_two + contract_bal.opt_three + contract_bal.opt_four + contract_bal.opt_five + contract_bal.opt_six)', (contract_bal.opt_one + contract_bal.opt_two + contract_bal.opt_three + contract_bal.opt_four + contract_bal.opt_five + contract_bal.opt_six) - (contract_bal.monthly_input) as '(contract_bal.opt_one + contract_bal.opt_two + contract_bal.opt_three + contract_bal.opt_four + contract_bal.opt_five + contract_bal.opt_six) - (contract_bal.monthly_input)', ((contract_bal.opt_one + contract_bal.opt_two + contract_bal.opt_three + contract_bal.opt_four + contract_bal.opt_five + contract_bal.opt_six) - (contract_bal.monthly_input)) \/ (contract_bal.opt_one + contract_bal.opt_two + contract_bal.opt_three + contract_bal.opt_four + contract_bal.opt_five + contract_bal.opt_six) * 100 as '((contract_bal.opt_one + contract_bal.opt_two + contract_bal.opt_three + contract_bal.opt_four + contract_bal.opt_five + contract_bal.opt_six) - (contract_bal.monthly_input)) \/ (contract_bal.opt_one + contract_bal.opt_two + contract_bal.opt_three + contract_bal.opt_four + contract_bal.opt_five + contract_bal.opt_six) * 100' FROM  `contract_bal` LEFT JOIN `contract_info` ON `contract_info`.`contract_id` = `contract_bal`.`contractinfo_id_fk`  LEFT JOIN `products` ON `products`.`product_code` = `contract_bal`.`product_code_fk2`  LEFT JOIN `supplier` ON `supplier`.`supplier_id` = `products`.`supplier_id_fk` WHERE `contract_bal`.`member_name` like :where_0  ORDER BY `contract_bal`.`contract_id`  asc ",
            "bindings": [{
                "name": ":where_0",
                "value": "%xyz Foodservice%",
                "type": null
            }]
        }
    

    May be this is creating problem but I am not sure (v like structure ):

    (contract_bal.monthly_input))** \/ **(contract_bal.opt_one + contract

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    @tefdat - Could you post your question in a new thread, so we can track a single issue at a time please?

    @Khalid Teli I suspect that it is the / dividing operation that is causing the issue - that is the one thing that stands out as different to me. And it isn't giving you an SQL error? If you replace / with * obviously the result would be nonsense, but does it then work?

    Another option here, instead of doing the calculation server-side, use a client-side renderer to do it. You've got all the data from the other fields - it is just a case of writing the calculation in Javascript rather than SQ I think.

    Allan

  • Khalid TeliKhalid Teli Posts: 251Questions: 71Answers: 0

    @allan
    Thank you.
    No, it is not giving me any SQL errors.
    I tried to replace it with* and the problem is same .

    This works fine:

     Field::inst('((contract_bal.opt_one + contract_bal.opt_two + contract_bal.opt_three + contract_bal.opt_four + contract_bal.opt_five + contract_bal.opt_six) - (contract_bal.monthly_input)) * (contract_bal.opt_one + contract_bal.opt_two + contract_bal.opt_three ) * 100', 'per_rem_bal1' )
                  ->set(false)
    

    But this doesn't :

     Field::inst('((contract_bal.opt_one + contract_bal.opt_two + contract_bal.opt_three + contract_bal.opt_four + contract_bal.opt_five + contract_bal.opt_six) - (contract_bal.monthly_input)) * (contract_bal.opt_one + contract_bal.opt_two + contract_bal.opt_three+ contract_bal.opt_four + contract_bal.opt_five + contract_bal.opt_six ) * 100', 'per_rem_bal1' )
                  ->set(false)
    

    Only after adding those extra columns seems causing problem .

    On the other hand , I have used the Client-side rendering and it works perfectly fine. The only issue is I am expecting to use large amount of data in future , that is why I was using server side .

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    You can still use client-side rendering with server-side processing. Although it wouldn't allow for sort and filtering...

    If you run the query directly in phpMyAdmin or whatever database client you prefer, does it work there? I honestly don't know why that wouldn't be working based solely on the length of the string. Perhaps try removing parameters until you find out the length it will accept?

    Allan

  • Khalid TeliKhalid Teli Posts: 251Questions: 71Answers: 0

    @allan
    Thank you. I ran the query on phpMyAdmnin and works fine .

    I have decided to render it on client-side and as I mentioned earlier it works fine.
    I will come back to it later and if I find what the problem is, I will post it here.

  • Khalid TeliKhalid Teli Posts: 251Questions: 71Answers: 0

    @allan
    just for the information, I have tried different combinations, anything greater 11 seems not to be working and anything less or equal to 11 works fine .

    For emample, this works fine :

              Field::inst ( '(contract_balcopy.m1 + contract_balcopy.m2 + contract_balcopy.m3 + contract_balcopy.m4 + contract_balcopy.m5 + contract_balcopy.m6 + contract_balcopy.m7 + contract_balcopy.m8 + contract_balcopy.m9 + contract_balcopy.m10 + contract_balcopy.m11)', 'agg_input' )
                           ->set(false),
    

    and this doesnt work:

        Field::inst ( '(contract_balcopy.m1 + contract_balcopy.m2 + contract_balcopy.m3 + contract_balcopy.m4 + contract_balcopy.m5 + contract_balcopy.m6 + contract_balcopy.m7 + contract_balcopy.m8 + contract_balcopy.m9 + contract_balcopy.m10 + contract_balcopy.m11 + contract_balcopy.m12)', 'agg_input' )
                       ->set(false),
    
  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    That is very very odd! I'll try to reproduce it locally and will post back if I find anything. Good to hear you've got it working client-side in the meantime.

    Allan

This discussion has been closed.