Bug While generating query using QueryBuilder

Nitin Srivastava commented on Dec 20, 2018

Hello,

We are using koolreport_pro_3.1.0. We are trying to generate the report by using KoolReports QueryBuilder. But we stuck on some points:

1) We are genrating columnChart report by passing data and stuck while generating query.

My actual query is: SELECT DAY(date_added) as 'Day',SUM(total) as 'Total' FROM orders WHERE YEAR(date_added) = '2016' GROUP BY DAY(date_added)

Now I want to generate it by using QueryBuilder so I am passing data in this manner:

Code is like:

$this->src('pick_pack_plus_new')->query(MySQL::type(
                    DB::table($this->params['table_name'])
                        ->select('DAY(date_added)')->alias('Day')
                        ->select('SUM(total)')->alias('Total')
                        ->whereYear('date_added','=' ,'2016')
                        ->groupBy('DAY(date_added)')
                ))

In the above code, we used the whereYear function to add the condition on the basis of year and also showing error in the selected fields. But it is throwing an error of undefined column as the query is built as:

SELECT `DAY(date_added)` AS `Day`, `SUM(total)` AS `Total` FROM `orders` WHERE `YEAR(date_added)` = '2016' GROUP BY `Day(date_added)`

The above query is wrong as the quotes should be on __date_added __column but it is taking quotes to the whole parameter. While KoolReports docs mentioned this function.

Reference URL: https://www.koolreport.com/docs/querybuilder/overview/#where-clauses-simple-where-clauses

Kindly suggest how we can proceed or fix this issue.

2) Apart from that, we want to generate comboChart. In your documentation/example you have mentioned the hardcoded data. But I want to generate this report using QueryBuilder. Kindly suggest how we can make this data dynamic to generate this report. We want below data by using Query builder. Kindly suggest us.

$category_amount = array(
        array("category"=>"Books","sale"=>32000,"cost"=>20000,"profit"=>12000),
        array("category"=>"Accessories","sale"=>43000,"cost"=>36000,"profit"=>7000),
        array("category"=>"Phones","sale"=>54000,"cost"=>39000,"profit"=>15000),
        array("category"=>"Movies","sale"=>23000,"cost"=>18000,"profit"=>5000),
        array("category"=>"Others","sale"=>12000,"cost"=>6000,"profit"=>6000),
    );

KoolReport commented on Dec 21, 2018

Please open the \koolreport\querybuilder\MySQL.php and replace content with this

<?php

namespace koolreport\querybuilder;

class MySQL extends SQL
{
    protected $indentifierCover=array("","");
}

Let me know if it works.

KoolReport commented on Dec 21, 2018

The hardcoded data is used because we want to demo how the charts look and it is much burden if we use database because users need to install database before seeing example. There are many examples that we use charts with database. The fastest way you can create chart from database is to enter directly query like below:

ColumnChart::create(array(
    "dataSource"=>$this->src("your_mysql_source")
                       ->query("select category, sale, cost, profit from tblSales")
));

KoolReport's Forum

Bug While generating query using QueryBuilder #568

Build Your Excellent Data Report