KoolReport's Forum

Official Support Area, Q&As, Discussions, Suggestions and Bug reports.
Forum's Guidelines

Multiple Tables with Union All and Multi Select2 #1487

Closed David Vanacore opened this topic on on Jun 13, 2020 - 18 comments
David Vanacore commented on Jun 13, 2020

I have multiple tables that have similar data that I'm trying to use a Multi Select filter with. When I put the params in both WHERE statements, I'm getting a "SQLSTATE[HY093]: Invalid parameter number" error. If I take out the second query, everything works fine. I don't want to make it a subquery, because the tables are very large (3M+) and the query results are also very big (100k+ rows) and the performance would suffer. I know it has to do with the params, but I'm still trying to understand how they work, so any help would be appreciated.

For security reasons, I've simplified the code and changed the fields. Here's the view.php

<?php
    use \koolreport\datagrid\DataTables;
    use \koolreport\inputs\Select2;
?>
<html>
    <div style="margin:30px;">
    <head>
    <title>Example</title>
    </head>
    <body>
        <h1>Example</h1>
        <form method="get">
        <div class="row"> 
            <div class="col-sm-1">
            From Quarter:
                <?php
                Select2::create(array(
                    "name"=>"startQtrPicker",
                    "dataStore"=>$this->dataStore("yearQtr"),
                    "attributes"=>array(
                        "class"=>"form-control"),
                    ));
                ?>
            </div>   
            <div class="col-sm-1">
            To Quarter:
                <?php
                Select2::create(array(
                    "name"=>"endQtrPicker",
                    "dataStore"=>$this->dataStore("yearQtr"),
                    "attributes"=>array(
                        "class"=>"form-control"),
                    ));
                ?>
            </div>
            <div class="col-sm-2">
            Name:
                <?php
                Select2::create(array(
                    "name"=>"namePicker",
                    "multiple"=>true,
                    "dataStore"=>$this->dataStore("name"),
                    "dataBind"=>array(
                        "text"=>"Title",
                        "value"=>"__kp_Name_ID"),
                    "attributes"=>array(
                        "class"=>"form-control"),
                    ));
                ?>
            </div>
            <div class="col-sm-2">
            Location:
                <?php
                Select2::create(array(
                    "name"=>"location",
                    "multiple"=>true,
                    "dataStore"=>$this->dataStore("location"),
                    "dataBind"=>array(
                        "text"=>"Location",
                        "value"=>"__kp_Location_ID"),
                    "attributes"=>array(
                        "class"=>"form-control"),
                    ));
                ?>
            </div>
          </div>         
            <div class="form-group" style="margin-top:22px;">
            <button class="btn btn-md btn-primary">Search</button>
            </div>
        </form>
        </div>
        </div>
        <?php
        DataTables::create(array(
            'name' => 'Example',
            "cssClass"=>array(
                "table"=>"table table-striped table-bordered"),
            'dataSource' => function() {
                return $this->src('mysql2')
                ->query('
                #TABLE1           
                SELECT 
                StatementYearQuarter AS "Statement_YrQtr",
                Names.Title AS "Standard_Name",
                Locations.Name AS "Location",

                FROM (TABLE1
                LEFT JOIN Names ON Names.__kp_Name_ID=TABLE1._kf_Name_ID
                LEFT JOIN Locations ON Locations.__kp_Location_ID=TABLE1._kf_Location_ID
                )

                WHERE 1=1
                '.(($this->params["startQtrPicker"]!=array())?"and TABLE1.StatementYearQuarter BETWEEN :startQtrPicker AND :endQtrPicker":"").'
                '.(($this->params["namePicker"]!=array())?"and TABLE1._kf_Name_ID in (:namePicker)":"").'
                '.(($this->params["locationPicker"]!=array())?"and TABLE1._kf_Location_ID in (:locationPicker)":"").'

                UNION ALL

                #TABLE2
                SELECT
                StatementYearQuarter AS "Statement_YrQtr",
                Names.Title AS "Standard_Name",
                Locations.Name AS "Location",

                FROM (TABLE2
                LEFT JOIN Names ON Names.__kp_Name_ID=TABLE2._kf_Name_ID
                LEFT JOIN Locations ON Locations.__kp_Location_ID=TABLE2._kf_Location_ID
                )

                WHERE 1=1
                '.(($this->params["startQtrPicker"]!=array())?"and TABLE2.StatementYearQuarter BETWEEN :startQtrPicker AND :endQtrPicker":"").'
                '.(($this->params["namePicker"]!=array())?"and TABLE2._kf_Name_ID in (:namePicker)":"").'
                '.(($this->params["locationPicker"]!=array())?"and TABLE2._kf_Location_ID in (:locationPicker)":"").'
                
                ')

                ->params(array(
                    ":startQtrPicker"=>$this->params["startQtrPicker"],
                    ":endQtrPicker"=>$this->params["endQtrPicker"],
                    ":namePicker"=>$this->params["namePicker"],
                    ":locationPicker"=>$this->params["locationPicker"],
                ));
            },
            "options" => array(
                "searching" => true,
                "paging" => true,
                "colReorder" => true,
                "order"=>array(
                    array(0,"asc")),
                "pageLength" => 25, 
                ),
            "serverSide"=> true,
        ));
        ?>
    </body>
</html>
KoolReport commented on Jun 15, 2020

The issue is that if your query does not have a parameter to bind ":someParameter" but you have it in the params() method, it will cause error in PHP PDO.

David Vanacore commented on Jun 15, 2020

Thank you. Is there an example that you can point me to, where I can change the structure so that I can use user inputs without having to use the params() in the query?

KoolReport commented on Jun 15, 2020

Please have a look at this example Multiple Data Filters.

David Vanacore commented on Jun 15, 2020

Thank you. I've used that example to help me get this far. Unfortunately, that example has the query in the .php file and not on the client side (view.php), which I need because of the server side processing.

Everything works fine until I add the Union All command in the query with the second set of params.

KoolReport commented on Jun 16, 2020

It is the same thing, you prepare the parameters like we do in the setup() function. you check the availability of the params before adding to a $params variable. You can prepare those parameter just before DataTables::create() function. Then you do like this:

DataTables::create([
    "dataSources"=>function() use ($params) {
        return $this->src("...")->query("...")->params($params);
    }
]);
David Vanacore commented on Jun 19, 2020

Okay, I prepared the parameters like you did in the setup(). Here the code for that.

protected function setup()

{
    $query_params = array();
    if($this->params["startQtrPicker"]!=array())
    {
        $query_params[":startQtrPicker"] = $this->params["startQtrPicker"];
    }
    if($this->params["endQtrPicker"]!=array())
    {
        $query_params[":endQtrPicker"] = $this->params["endQtrPicker"];
    }
    if($this->params["namePicker"]!=array())
    {
        $query_params[":namePicker"] = $this->params["namePicker"];
    }
    if($this->params["locationPicker"]!=array())
    {
        $query_params[":locationPicker"] = $this->params["locationPicker"];
    }

}

Then I added the use() in my view.php

    DataTables::create(array(
        'name' => 'Example',
        "cssClass"=>array(
            "table"=>"table table-striped table-bordered"),
        'dataSource' => function() use ($query_params) {
            return $this->src('mysql2')
            ->query('....')
            ->params($query_params);

I think I'm missing the ->pipe in the .php file, but I'm not sure how exactly I need to implement it. Do I need to add one after each variable?

David Winterburn commented on Jun 19, 2020

Hi,

If you use multiple params in your query please separate them into different names even if they have the same value. For example, if you use multiple ":startQtrPicker" in your query it won't work. In your case change the params to "":startQtrPicker1" and "":startQtrPicker2". Then in the params() method set it like this: 

                ->params(array(
                    ":startQtrPicker1"=>$this->params["startQtrPicker"],
                    ":startQtrPicker2"=>$this->params["startQtrPicker"],

This is because PDO doesn't allow multiple same name params. In case you reuse the same params many times, maybe using sql variable is a good solution as well:

$sqlQuery = "SET @startDate = :startDate ...";

Please try either of this and let us know if it works for you. Thanks!

David Vanacore commented on Jun 19, 2020

Hi David,

Thank you for the help.

If I were to use the SQL variable, where would that statement go? In the protected function of the .php file or in the query of the view.php file or some place else in the view.php file?

David Winterburn commented on Jun 22, 2020

Hi,

Which database are you using PDO with?

David Vanacore commented on Jun 22, 2020

I'm using mysql.

David Winterburn commented on Jun 23, 2020

Hi,

So regarding your question of using a single variable to replace multiple parameters of the same value, while it's not possible to include both variable declaration and assignment together with a query, the good news is that a mysql pdo connection tends to keep a variable's value across one session. Thus, we could try a trick that separates variable assignment and sql query like this:

$this->src("pdoMysql")
->query("SET @startDate = :startDate")
->params([":startDate" => $startDate]);

$this->src("pdoMysql")
->query("Select * from MyTable where startDate >= @startDate and endDate >= @startDate")
->pipe(...)

Hope this works for you. Let us know if there's any issue. Thanks!

David Vanacore commented on Jun 24, 2020

Thank you for this info. I was able to get this to work in the php file, but not in the view.php, so that I can use server side processing. Is this feature possible to use in the view.php file?

Is there another way that I'd be able to set a variable in the view.php file that allows me to use the same array of variables in the query? I have similar data in multiple tables that I have to use union all to combine them.

David Winterburn commented on Jun 24, 2020

How about putting the variable statement in the report's setup function and the query in the DataTables widget's dataSource property in view file:

//MyReport.php
function setup()
{
    $this->src("pdoMysql")
    ->query("SET @startDate = :startDate")
    ->params([":startDate" => $startDate]);
}

//MyReport.view.php
DataTables::create(array(
    ...
    "dataSource" => function($scope) {
                        return $this->src('pdoMysql')
                        ->query($queryWithParams);
                    },
));
David Vanacore commented on Jun 25, 2020

Thank you for help. It seems like the SET @startDate is not be sent back to the query in the view.php file. When I show what the input is set to with 

        <pre><code><?php echo json_encode($this->params,JSON_PRETTY_PRINT) ?></code></pre>

It shows up correctly, but when the @startDate is in the query statement it doesn't seem to do anything.

David Winterburn commented on Jun 26, 2020

Ok, so this is a really hackish solution as our datasource hasn't had an option for using multiple queries per session yet:

    use \koolreport\core\Utility as Util;
    DataTables::create(array(
                    'name' => 'Dt1",
                    'dataSource' => function($scope) {
                        $pdoSetting = $this->settings()['dataSources']['pdoMysql']; //assuming you set up a 'pdoMysql' source in the report's settings
                        $connectionString = Util::get($pdoSetting, "connectionString", "");
                        $username = Util::get($pdoSetting, "username", "");
                        $password = Util::get($pdoSetting, "password", "");                        
                        $key = md5($connectionString.$username.$password);
                        $connection = \koolreport\datasources\PdoDataSource::$connections[$key];
                        $stm = $connection->prepare("set @myVar=:myVar");
                        $stm->execute([':myVar' => 20]); //replace value 20 here with your param value
                        
                        return $this->src('pdoMysql')
                        ->query('select * from customer_product_dollarsales2
                        where orderDay > @myVar')
                        ;
                    },

Hope this helps.

David Vanacore commented on Jun 27, 2020

Is there any other way that I might be able to accomplish the SQL SET variable, other than this hack?

David Winterburn commented on Jun 29, 2020

Hi,

For now it's the only way I can think of to perform multiple queries per connection session in a data source. You can always choose to use multiple params of the same values as the alternative.

We will consider adding support for multiple queries in data source in the future so that you could have a more elegant solution for these types of problem. Thanks!

David Vanacore commented on Jun 29, 2020

Thank you for all of your help. You can close this ticket.

Build Your Excellent Data Report

Let KoolReport help you to make great reports. It's free & open-source released under MIT license.

Download KoolReport View demo
solved
None

Designed and built with by KoolPHP Inc.

Code licensed MIT.