Trend Metrics with leftjoin -error

cyberweb commented on May 22, 2022

Hello il i use un leftjoin in the sql query, i have 1 error AdminAutoMaker::table("table1")

           ->leftjoin("table2","table2.id","=","table1.id_table2")
           ->leftjoin("table3","table3.id","=","table1.id_table3")

I have this error query Error >> [You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '.date_day SELECT DATE_FORMAT(table2.date,'%Y-%m-%d') AS table2.date_day, SUM(table1.nbre) AS table1.nbre_sum FROM transactions_details ...

The problem is that the mysql use alias with the table with jointure table2.date_day in place of date_day.

Have you got a tips to avoid this error ? Thanks

Sebastian Morales commented on May 24, 2022

Pls post your full query builder code for us to check it for you. Tks,

cyberweb commented on May 24, 2022

use \koolreport\dashboard\metrics\Trend;
use \Maclass\AdminAutoMaker;
use \koolreport\dashboard\fields\Date;
use \koolreport\dashboard\fields\Currency;
use \koolreport\dashboard\fields\Number;
use \koolreport\processes\DateTimeFormat;
use \koolreport\dashboard\fields\DateTimePicker;
use koolreport\dashboard\fields\ID;

class ThisClass extends Trend
{
    protected function onInit()
    {
        $this->defaultRange("This Month");
        $this->title("MaPage");
    }
 
    public function getEventID()
    {
        return $this->dashboard()->getCustomerNumber();
    }

 protected function dataSource()
    {   
        // $range = $this->sibling("PaymentDateRange")->value();
        return AdminAutoMaker::rawSQL("
        SELECT SUM(table1.nbre) AS nbre_sum, table2.date 
        FROM table1                
        LEFT JOIN
            table2
        ON
            table2.id = table1.id_transaction
        LEFT JOIN
            table3
        ON
            table3.id = table1.id_event   
        WHERE
            table3.id_event = ".$this->dashboard()->getCustomerNumber()."   

    ");
    }
    

    {
        return [
            "This Week"=>$this::thisWeek(),
            "Last 7 Days"=>$this::last7days(),
            "Last 30 Days"=>$this::last30days(),
            "This Month"=>$this::thisMonth(),
            "This Quarter"=>$this::thisQuarter(),
            "This Year"=>$this::thisYear(),
            "Last Year"=>$this::lastYear(),
        ];   
    }
    protected function fields()
    {
        return [
    
         $this->group(Date::create("table2.date")),          
        
            $this->sum(
                Number::create("nbre_sum"))
        ];
    }}

KoolReport commented on May 25, 2022

Let try to do this to see how:

 protected function dataSource()
 {  
        return AdminAutoMaker::rawSQL("
        SELECT SUM(table1.nbre) AS nbre_sum, table2.date 
        FROM table1                
        LEFT JOIN
            table2
        ON
            table2.id = table1.id_transaction
        LEFT JOIN
            table3
        ON
            table3.id = table1.id_event   
        WHERE
            table3.id_event = ".$this->dashboard()->getCustomerNumber()."   

    ")->run(); // Add this
}

cyberweb commented on May 25, 2022

not work i received Trying to access array offset on value of type null I'm trying protected function dataSource()

{   
   return  AdminAutoMaker::table("table1")
  
->leftjoin("table2","table2.id","=","table1.id_transaction")
->leftjoin("table3","table3.id","=","table1.id_event")
->where("table3.id_event","=",$this->dashboard()->getCustomerNumber());

I receive error SELECT DATE_FORMAT(table2.date,'%Y-%m-%d') AS table2.date_day, SUM(nbre) AS nbre_sum FROM transactions_details

If i use date , i have a ambigious error

Sebastian Morales commented on May 26, 2022

Pls post your error's full message and stack trace.

cyberweb commented on May 26, 2022

Message: Query Error >> [You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '.date_day, SUM(nbre) AS nbre_sum FROM table1 LEFT JOIN table2' at line 1] >> SELECT DATE_FORMAT(table2.date,'%Y-%m-%d') AS table2.date_day, SUM(nbre) AS nbre_sum FROM table1 LEFT JOIN table2 ON table2.id = table1.transaction LEFT JOIN table3 ON table3.id = table1.id_event WHERE table3.id_event = '31' AND (table2.date >= '2022-05-01 00:00:00' AND table2.date <= '2022-05-31 23:59:59') GROUP BY DATE_FORMAT(table2.date,'%Y-%m-%d')
Line: 440
File: /var/www/clients/client1/web760/web/vendor/koolreport/core/src/datasources/PdoDataSource.php
Collapse
#0: /var/www/clients/client1/web760/web/vendor/koolreport/core/src/core/DataSource.php Line 108 : start(null)
#1: /var/www/clients/client1/web760/web/vendor/koolreport/core/src/core/Node.php Line 365 : requestDataSending(null)
#2: /var/www/clients/client1/web760/web/vendor/koolreport/dashboard/sources/PDOSource.php Line 139 : requestDataSending(null)
#3: /var/www/clients/client1/web760/web/vendor/koolreport/dashboard/data/SQLHandler.php Line 124 : run(null)
#4: /var/www/clients/client1/web760/web/vendor/koolreport/dashboard/metrics/Trend.php Line 133 : executes(null)
#5: /var/www/clients/client1/web760/web/vendor/koolreport/dashboard/Widget.php Line 223 : render(null)
#6: /var/www/clients/client1/web760/web/vendor/koolreport/dashboard/Widget.php Line 176 : view(null)
#7: /var/www/clients/client1/web760/web/vendor/koolreport/dashboard/TAction.php Line 37 : actionIndex(null)
#8: /var/www/clients/client1/web760/web/vendor/koolreport/dashboard/Widget.php Line 139 : action(null)
#9: /var/www/clients/client1/web760/web/vendor/koolreport/dashboard/Dashboard.php Line 259 : handle(null)
#10: /var/www/clients/client1/web760/web/vendor/koolreport/dashboard/pages/Main.php Line 137 : handle(null)
#11: /var/www/clients/client1/web760/web/vendor/koolreport/dashboard/Application.php Line 217 : handle(null)
#12: /var/www/clients/client1/web760/web/vendor/koolreport/dashboard/Application.php Line 149 : handle(null)
#13: /var/www/clients/client1/web760/web/index.php Line 14 : run(null)

Sebastian Morales commented on May 27, 2022

Pls post your dataSource() and fields() methods. Tks,

cyberweb commented on May 27, 2022

protected function dataSource()
 {  

   return  AdminAutoMaker::table("table1")
  
->leftjoin("table2","table2.id","=","table1.id_transaction")
->leftjoin("table3","table3.id","=","table1.id_event")
->where("table3.id_event","=",$this->dashboard()->getCustomerNumber())
}

protected function fields()
    {
        return [
    
         $this->group(Date::create("table2.date")),          
        
            $this->sum(
                Number::create("nbre"))
        ];
    }}

KoolReport commented on May 27, 2022

Let try this if it works:

protected function dataSource()
{  

   return  AdminAutoMaker::rawSQL("
        SELECT
            table2.date AS theDate,
            nbre
        FROM table1
        LEFT JOIN table2 ON table2.id = table1.id_transaction
        LEFT JOIN table3 ON table3.id = table1.id_event
        WHERE
            table3.id_event = ".$this->dashboard()->getCustomerNumber()
        );  
}

protected function fields()
{
    return [

        $this->group(Date::create("theDate")),
        $this->sum(Number::create("nbre"))
    ];
}

cyberweb commented on May 27, 2022

Yes it's work thanks a lot

KoolReport's Forum

Trend Metrics with leftjoin -error #2691

Build Your Excellent Data Report