KR with CI4: how to handle null value of parameter in where clause

AhmedHaroon commented on Jan 12, 2023

in oracle, we handle null input parameter for query like this:

select empno, ename from emp where ename = nvl(:name, ename);

means if parameter name is null then return all rows regardless of specific ename.

how i can handle it here? see my code below:

( also please guide, is it correct way to have parametarized report )

CustomerSummary.php

<?php
namespace App\Reports;

// require_once "../../../load.koolreport.php";

use \koolreport\KoolReport;
use \koolreport\processes\Sort;

class CustomerSummary extends \koolreport\KoolReport
{
    use \koolreport\codeigniter\Friendship;
    use \koolreport\inputs\Bindable;
    use \koolreport\inputs\POSTBinding;

    protected function defaultParamValues()
    {
        return array(
            "dateRange"=>array(
                "2022-12-01",
                "2022-12-10",
            ),
            "customers"=>null,
        );
    }

    protected function bindParamsToInputs()
    {
        return array(
            "dateRange"=>"dateRange",
            "customers"=>"customers",
        );
    }

    function setup()
    {
        $this->src("default")
        ->query("SELECT DATE(order_date) AS order_date, user_id, customer_name, rides,rider_charges, order_total FROM customer_summary WHERE
                    user_id = nvl(:customers, user_id)
                    AND
                    order_date >= :start
                    AND
                    order_date <= :end")
        ->params(array(
            ":start"=>$this->params["dateRange"][0],
            ":end"=>$this->params["dateRange"][1],
            ":customers"=>$this->params["customers"]
        ))
        ->pipe(Sort::process([
            "order_date"=>"desc"
        ]))
        ->pipe($this->dataStore("result"));
        $this->src("default")->query("
            SELECT DISTINCT 
                user_id,
                customer_name
            FROM
                customer_summary
            ORDER BY customer_name
        ")
        ->pipe($this->dataStore("customers"));
    }
}

please help.

regards

Sebastian Morales commented on Jan 12, 2023

First you check if the parameter value is null or not. If it's null don't include its where clause in your sql. If it's not null include its where clause. Here's an example:

if ($this->params["customers"] != null) {
    $parameterClause = "user_id = nvl(:customers, user_id)";
} else {
    $parameterClause = "1=1";
}
$sql = "... WHERE 1=1 AND $parameterClause ...";

AhmedHaroon commented on Jan 13, 2023

thank you for this tip.

as mentioned earlier about my very basic knowledge, please can you update my code above as my sql contains date range too in where clause and i am confused here WHERE 1=1 AND $parameterClause

i will be much grateful.

regards

Sebastian Morales commented on Jan 16, 2023

Pls try something like this:

    $sqlParams = array(
            ":start"=>$this->params["dateRange"][0],
            ":end"=>$this->params["dateRange"][1]
        );
    if ($this->params["customers"] != null) {
        $userClause = "user_id = nvl(:customers, user_id)";
        $sqlParams[":customers"] = $this->params["customers"];
    } else {
        $userClause = "1=1";
    }
    $sql = "SELECT DATE(order_date) AS order_date, user_id, customer_name, rides,rider_charges, order_total FROM customer_summary WHERE
                    $userClause
                    AND
                    order_date >= :start
                    AND
                    order_date <= :end"; 

        $this->src("default")
        ->query($sql)
        ->params($sqlParams)
        ->pipe(Sort::process([
            "order_date"=>"desc"
        ]))
        ->pipe($this->dataStore("result"));

AhmedHaroon commented on Jan 17, 2023

@Sebastian Morles thanks again sir for your kind help.

i was trying to resolve and yesterday have modified as below and it works, please check and advise:

->query("SELECT DATE(order_date) AS order_date, user_id, customer_name, rides,rider_charges, order_total FROM customer_summary WHERE
                    user_id = ".($this->params["customers"] == '' ? 'user_id' : ':customers')."
                    AND
                    order_date >= :start
                    AND
                    order_date <= :end")
        ->params(array(
            ":start"=>$this->params["dateRange"][0],
            ":end"=>$this->params["dateRange"][1],
            ":customers"=>$this->params["customers"]
        ))
        ->pipe(Sort::process([
            "order_date"=>"desc"
        ]))
        ->pipe($this->dataStore("result"));

regards

Sebastian Morales commented on Jan 17, 2023

That's also a good approach. As long as it works and you find it intuitive and easier for later maintenance I think there's no problem applying it at all.

KoolReport's Forum

KR with CI4: how to handle null value of parameter in where clause #2938

Build Your Excellent Data Report