QueryBuilder adding single quotes, breaking query

Tyler Neal commented on Apr 13, 2018

I'm using QueryBuilder for MySQL and it's adding single quotes to every field name in the query, which ends up breaking the query. Here's an example.

QueryBuilder Output:

SELECT `mco.name` AS `mco_name` FROM `order_line_item` JOIN `mco` ON `order_line_item.mco_id` = `mco.id`  JOIN `providers` ON `order_line_item.provider_id` = `providers.id` LIMIT 10

Error:

Unknown column 'mco.name' in 'field list'

Corrected Query:

SELECT mco.name AS mco_name FROM order_line_item JOIN mco ON order_line_item.mco_id = mco.id  JOIN providers ON order_line_item.provider_id = providers.id LIMIT 10

Is this a known issue? Something I can correct on my own?

KoolReport commented on Apr 14, 2018

Yes, it is known bug. We have released new version QueryBuilder 1.3.0. Could you please download. It will solve the issue.

Tyler Neal commented on Apr 16, 2018

Hello,

Getting this error now which is preventing data from loading, my code appears to be okay. Still getting the single quotes as well it appears.

[16-Apr-2018 04:39:37 Europe/Berlin] PHP Fatal error:  Uncaught Exception: Query Error >> [FUNCTION me_db.m does not exist] >> SELECT m(`c`) AS `mco_name` FROM `order_line_item` JOIN `mco` ON `order_line_item`.`mco_id` = `mco`.`id`  JOIN `providers` ON `order_line_item`.`provider_id` = `providers`.`id` WHERE `order_line_item`.`order_id` = '=6833' LIMIT 10 in /Applications/MAMP/htdocs/new-mexico-portal/vendor/koolphp/koolreport/koolreport/datasources/PdoDataSource.php:137

Tyler Neal commented on Apr 16, 2018

Just also noticed that it's adding an extra = in the where clause, see below.

WHERE `order_line_item`.`order_id` = '=6833'

Here's my code:

$this->src('ApprovedDevices')->query(MYSQL::type(
            DB::table('order_line_item')
                ->join('mco', 'order_line_item.mco_id', '=', 'mco.id')
                ->join('providers', 'order_line_item.provider_id', '=', 'providers.id')
                ->select("mco.name")->alias('mco_name')
                ->where('order_line_item.order_id', '='. $this->params["orderID"])
                ->limit(10)
        ));

KoolReport commented on Apr 16, 2018

We confirm issue, please allow us few hours to fix this.

KoolReport commented on Apr 16, 2018

Okay, we have released 1.4.0, could you please download. About the issue '=6833', it is bug in your code, it should be:

->where('order_line_item.order_id', '=', $this->params["orderID"])

Notice that we use the , instead of your . after the '='

Tyler Neal commented on Apr 16, 2018

Thanks for the update, seeing new issues this time, still no data being pulled in unfortunately.

On the top of the page, this text is being output, not sure if it is supposed to be it's being displayed on the page itself:

array(2) { [0]=> array(3) { [0]=> string(4) "JOIN" [1]=> string(3) "mco" [2]=> object(koolreport\querybuilder\Query)#10 (14) { ["type"]=> string(6) "select" ["tables"]=> array(0) { } ["columns"]=> array(0) { } ["conditions"]=> array(1) { [0]=> array(3) { [0]=> string(22) "order_line_item.mco_id" [1]=> string(1) "=" [2]=> string(17) "[{colName}]mco.id" } } ["orders"]=> array(0) { } ["groups"]=> array(0) { } ["having"]=> NULL ["limit"]=> NULL ["offset"]=> NULL ["joins"]=> array(0) { } ["distinct"]=> bool(false) ["unions"]=> array(0) { } ["values"]=> array(0) { } ["lock"]=> NULL } } [1]=> array(3) { [0]=> string(4) "JOIN" [1]=> string(9) "providers" [2]=> object(koolreport\querybuilder\Query)#11 (14) { ["type"]=> string(6) "select" ["tables"]=> array(0) { } ["columns"]=> array(0) { } ["conditions"]=> array(1) { [0]=> array(3) { [0]=> string(27) "order_line_item.provider_id" [1]=> string(1) "=" [2]=> string(23) "[{colName}]providers.id" } } ["orders"]=> array(0) { } ["groups"]=> array(0) { } ["having"]=> NULL ["limit"]=> NULL ["offset"]=> NULL ["joins"]=> array(0) { } ["distinct"]=> bool(false) ["unions"]=> array(0) { } ["values"]=> array(0) { } ["lock"]=> NULL } } }

As mentioned above, I'm not seeing any data coming from the table, I do notice this new log being made into the php error log:

[16-Apr-2018 08:41:15 Europe/Berlin] PHP Notice:  Undefined index: columns in /Applications/MAMP/htdocs/new-mexico-portal/vendor/koolphp/koolreport/koolreport/widgets/koolphp/Table.php on line 109

Thank you for all the help so far.

KoolReport commented on Apr 16, 2018

Please help me to do 1 thing, please go to line 166 of the file \koolreport\packages\querybuilder\SQL.php and remove the var_dump($joins);.

I need to help you solve this issue before receiving your thank.

Tyler Neal commented on Apr 16, 2018

That successfully removed the text being put on the page, but no data is loading in yet, in my case I should see 1 row of data coming in. Is this related to the error below?:

[16-Apr-2018 08:41:15 Europe/Berlin] PHP Notice:  Undefined index: columns in /Applications/MAMP/htdocs/new-mexico-portal/vendor/koolphp/koolreport/koolreport/widgets/koolphp/Table.php on line 109

KoolReport commented on Apr 16, 2018

if you check the output of SQL Statement generated by querybuilder, is query generated correctly? If you test the query in phpmyadmin for example. Does it work?

Tyler Neal commented on Apr 16, 2018

Where can I see the output query? I checked the previous query I had gotten with single quotes, removed them and ran it and it returned results.

KoolReport commented on Apr 16, 2018

Well, you simple do this:

echo MYSQL::type(
            DB::table('order_line_item')
                ->join('mco', 'order_line_item.mco_id', '=', 'mco.id')
                ->join('providers', 'order_line_item.provider_id', '=', 'providers.id')
                ->select("mco.name")->alias('mco_name')
                ->where('order_line_item.order_id', '=', 6833)
                ->limit(10)
        );

you will get the sql statement generated from the QueryBuilder, check if there is any error. you may paste it to the phpmyadmin to see if it pull data.

Also if possible, please send me your report files (class file and view file) to support@koolreport.com, I would like to see if anything suspected. It is the first time we saw the error undefined index for "columns".

KoolReport commented on Apr 16, 2018

Furthermore, if you do not like query to be added with quote for table name and column name as we do, please do this:

Go to the file \querybuilder\MySQL.php and put following content:

<?php

namespace koolreport\querybuilder;

class MySQL extends SQL
{
    protected $indentifierCover=array("","");//For table name and column name
}

KoolReport commented on Apr 16, 2018

Oh I see, I have seen you files and it should be like this:

<?php
/**
 * Created by PhpStorm.
 * User: advil0
 * Date: 4/12/18
 * Time: 8:03 PM
 */

require_once __DIR__ . '/../vendor/autoload.php';
require_once __DIR__ . '/../config.php';

//use \koolreport\processes\Limit;
use \koolreport\querybuilder\DB;
use \koolreport\querybuilder\MySQL;

class ApprovedDevices extends \koolreport\KoolReport
{
    public function settings()
    {
        return array(
            "dataSources"=>array(
                "ApprovedDevices"=>array(
                    "connectionString"=>"mysql:host=" . DB_HOST . ";dbname=" . DB_NAME,
                    "username"=>DB_USER,
                    "password"=>DB_PASS,
                    "charset"=>"utf8"
                )
            )
        );
    }

    public function setup()
    {
        $this->src('ApprovedDevices')->query(MYSQL::type(
        DB::table('order_line_item')
            ->join('mco', 'order_line_item.mco_id', '=', 'mco.id')
            ->join('providers', 'order_line_item.provider_id', '=', 'providers.id')
            ->select("mco.name")->alias('mco_name')
            ->where('order_line_item.order_id', '=', $this->params["orderID"])
            ->limit(10)
        ))
        ->pipe($this->dataStore("ApprovedDevices")); // <- This is important
    }
}

You are lack of piping result to dataStore at the end.

Tyler Neal commented on Apr 16, 2018

Always something simple. Thanks again! I've tipped you via the forum.

KoolReport commented on Apr 16, 2018

Thank you so so much for your generosity. If you have any problem, please let me know.

KoolReport's Forum

QueryBuilder adding single quotes, breaking query #246

Build Your Excellent Data Report