KoolReport's Forum

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

How to pass advanced query #2710

Open 50Grosh opened this topic on on Jun 6 - 19 comments

50Grosh commented on Jun 6

Hello

is there any way to pass query like this ?

SET @sql = NULL;

WITH cte AS(
    SELECT DISTINCT ROW_NUMBER() OVER(PARTITION BY event_items_id) AS idx
    FROM event_items_additional
)
SELECT GROUP_CONCAT(
           CONCAT('MAX(IF(rn_add = ', cte.idx, ', additional_option_name, NULL)) AS addition', cte.idx, ','
                  'MAX(IF(rn_qst = ', cte.idx, ', ask_user, NULL)) AS question', cte.idx
       )) INTO @sql
FROM cte;

SET @cte = 'WITH cte AS(
                                        SELECT post_title, users.id AS user_id, name, surname, additional_option_name, ask_user, additional_option_price, 
                                        ROW_NUMBER() OVER(PARTITION BY name, surname ORDER BY IF(additional_option_name IS NULL, 1, 0), post_title) AS rn_add, 
                                        ROW_NUMBER() OVER(PARTITION BY name, surname ORDER BY IF(ask_user IS NULL, 1, 0), post_title) AS rn_qst 
FROM users 
LEFT JOIN event_items 
            ON users.id = event_items.id 
LEFT JOIN event_items_additional 
            ON users.id = event_items_additional.event_items_id 
LEFT JOIN event_items_ask_user 
            ON users.id = event_items_ask_user.event_items_id)';

SET @sql = CONCAT(@cte,
                  'SELECT user_id AS post_title, user_id, name, surname,',
                  @sql,
                  ',SUM(additional_option_price) AS additional_option_price FROM cte GROUP BY post_title, user_id, name, surname'
);

SELECT @sql;

PREPARE stmt1 FROM @sql;
EXECUTE stmt1;

DEALLOCATE PREPARE stmt1;

I try like this guy https://www.koolreport.com/forum/topics/1593 but doesn't work

Greetings

Sebastian Morales commented on Jun 7

Is it possible for you to try using these inside a procedure (with parameter if needed) and pass an sql calling the procedure to your report? Let us know if there's any difficulty. Rgds,

50Grosh commented on Jun 13

Hi I'm coming back asking for help

I wrote myself a procedure that looks like this

CALL Report(@is_payment := :is_payment, @post_title := :post_title, @additional_option_name := :additional_option_name, @ask_user := :ask_user)
DELIMITER $$
CREATE PROCEDURE `Report`(
    IN `@is_payment` TINYINT(1),
    IN `@post_title` TEXT, 
    IN `@additional_option_name` TEXT,
    IN `@ask_user` TEXT)
BEGIN
SET @sql = NULL;

WITH cte AS(
    SELECT DISTINCT ROW_NUMBER() OVER(PARTITION BY event_items_id) AS idx
    FROM event_items_additional
)

SELECT GROUP_CONCAT(
           CONCAT('MAX(IF(rn_add = ', cte.idx, ', additional_option_name, NULL)) AS additional_option_name', cte.idx, ','
                  'MAX(IF(rn_qst = ', cte.idx, ', ask_user, NULL)) AS ask_user', cte.idx
       )) INTO @sql
FROM cte;

SET @cte = 'WITH cte AS(SELECT post_title, users.id AS user_id, name, surname, additional_option_name, ask_user, additional_option_price, ROW_NUMBER() OVER(PARTITION BY name, surname ORDER BY IF(additional_option_name IS NULL, 1, 0), post_title) AS rn_add, ROW_NUMBER() OVER(PARTITION BY name, surname ORDER BY IF(ask_user IS NULL, 1, 0), post_title) AS rn_qst
FROM users
LEFT JOIN 
event_items 
	ON users.id = event_items.id
LEFT JOIN event_items_additional
	ON users.id = event_items_additional.event_items_id
LEFT JOIN event_items_ask_user 
	ON users.id = event_items_ask_user.event_items_id
WHERE 
	FIND_IN_SET(@is_payment, post_title) 
    AND
    FIND_IN_SET(@post_title, post_title)
    AND
	FIND_IN_SET(@additional_option_name, post_title) 
    AND
    FIND_IN_SET(@ask_user, post_title) 
)';

SET @sql = CONCAT(@cte,
                  'SELECT user_id, name, surname,',
                  @sql,
                  ',SUM(additional_option_price) AS additional_option_price FROM cte GROUP BY user_id, name, surname'
);

PREPARE stmt1 FROM @sql;
EXECUTE stmt1;

DEALLOCATE PREPARE stmt1;
END$$
DELIMITER ;

hoping it will work like this


...
WHERE
     ".(($this->params["p1"]!=array())?" AND p1 IN (:p1)":"")."
     ".(($this->params["p2"]!=array())?" AND p2 IN (:p2)":"")."
     ".(($this->params["p3"]!=array())?" AND p3 IN (:p3)":"")."
     ".(($this->params["p4"]!=array())?" AND p4 IN (:p4)":"")."
...


 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 ' @post_title := , @additional_option_name := '1', @ask_user := )'

Now i stuck with some errors and dont know how to implement Procedure with params in correct way I took a long time to do this but I got stuck.

Also Im not sure if create procedure will solve the problem becauce of default parameters

protected function defaultParamValues()
    {
        return array(
            "is_payment"=>array(),
            "post_title"=>array(),
            "additional_option_name"=>array(),
            "ask_user"=>array(),
        );
    }

    protected function bindParamsToInputs()
    {
        return array(

            "is_payment",
            "post_title",
            "additional_option_name",
            "ask_user"
        );
    }
    protected function settings()
    {
        return $this -> getConfig();
    }

    protected function setup()
    {
        var_dump($this->params["is_payment"]);
        $this->src('skaleo')->query("CALL ReportAdditionals(@is_payment := :is_payment, @post_title := :post_title, @additional_option_name := :additional_option_name, @ask_user := :ask_user)")


            ->params(array(
                ":is_payment" => $this->params["is_payment"],
                ":post_title" => $this->params["post_title"],
                ":additional_option_name" => $this->params["additional_option_name"],
                ":ask_user" => $this->params["ask_user"],
            ))
            ->pipe($this->dataStore('asksF'));
    }

Kind Regarts

Sebastian Morales commented on Jun 14

Which database and datasource type (in report's settings) do you use?

Did you try this command:

        $this->src('skaleo')->query("CALL ReportAdditionals(:is_payment, :post_title, :additional_option_name, :ask_user)")
        ...
50Grosh commented on Jun 14

 class KoolReportConnectionConfig extends \koolreport\KoolReport
{
    private $DB_NAME = DB_NAME;
    private $DB_HOST = DB_HOST;
    private $DB_USER = DB_USER;
    private $DB_PASSWORD = DB_PASSWORD;
    private $DB_CHARSET = DB_CHARSET;

    public function getConfig()
    {
        return array(
            "dataSources" => array(
                "skaleo" => array(
                    "connectionString" => "mysql:host=$this->DB_HOST;dbname=$this->DB_NAME",
                    "username" => "$this->DB_USER",
                    "password" => "$this->DB_PASSWORD",
                    "charset" => "$this->DB_CHARSET"
                )
            )
        );
    }
}



50Grosh commented on Jun 14

After change "Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 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 ' , := , )' at line 1 in C:\xampp\htdocs\skaleo\wp-content\plugins\skaleo\libs\koolreport\core\src\datasources\PdoDataSource.php:435"

50Grosh commented on Jun 14

I think problem is in procedure and null/ empty array default params.

I try also make procedure without params and after calling it pipe to Filter but this also doesn't work.

Sebastian Morales commented on Jun 14

Pls try this:

        $this->src('skaleo')->query("EXECUTE ReportAdditionals :is_payment, :post_title, :additional_option_name, :ask_user") // assuming ReportAdditionals is your procedure's name
            ->params(array(
                ":is_payment" => $this->params["is_payment"],
                ":post_title" => $this->params["post_title"],
                ":additional_option_name" => $this->params["additional_option_name"],
                ":ask_user" => $this->params["ask_user"],
            )) 
        ...

As for default parameter values, you have to handle them in your procedure in case they are null.

50Grosh commented on Jun 14

same syntax error

50Grosh commented on Jun 14

  Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 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 ' , ,' at line 1 in C:\xampp\htdocs\skaleo\wp-content\plugins\skaleo\libs\koolreport\core\src\datasources\PdoDataSource.php:435

50Grosh commented on Jun 14
        $this->src('skaleo')->query("CALL ReportAdditionals(@is_payment := :is_payment, @post_title := :post_title, @additional_option_name := :additional_option_name, @ask_user := :ask_user)")

This way is correct for me but i struggle with null params and my procedure in general. I tought maybe you have some suggestion.

Thank you for your help and fast reply :)

Sebastian Morales commented on Jun 16

Did you solve the case when the parameters are null? Tks,

50Grosh commented on Jun 20

I fix my Procedure but still have problem with this part

        $this->src('skaleo')->query("CALL ReportAdditionals( :is_payment, :post_title, :additional_option_name,  :ask_user)")

still thinking how to work around null values

in worst case i need to put some nonsensical default valuest

50Grosh commented on Jun 20

Thanks for asking :)

50Grosh commented on Jun 20

Problem is also when passing to values to parameter,

 Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1318 Incorrect number of arguments for PROCEDURE test.ReportAdditionals; expected 4, got 5 
Sebastian Morales commented on Jun 20

Pls output your parameter values in case of error and let us know the result:

$params = array(
                ":is_payment" => $this->params["is_payment"],
                ":post_title" => $this->params["post_title"],
                ":additional_option_name" => $this->params["additional_option_name"],
                ":ask_user" => $this->params["ask_user"],
            );
var_dump($params);
$this->src('skaleo')->query(...) 
            ->params($params)
...
50Grosh commented on Jun 20

array(4) { [":is_payment"]=> array(1) { [0]=> string(0) "" } [":post_title"]=> array(1) { [0]=> string(0) "" } [":additional_option_name"]=> array(2) { [0]=> string(12) "Chcesz piwko" [1]=> string(4) "Cola" } [":ask_user"]=> array(1) { [0]=> string(0) "" } }

50Grosh commented on Jun 20

err

Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1318 Incorrect number of arguments for PROCEDURE test.ReportAdditionals; expected 4, got 5 in \koolreport\core\src\datasources\PdoDataSource.php:435

Sebastian Morales commented on Jun 21

I think the problem is that your param ":additional_option_name" is of type array while your procedure expects a text one.

50Grosh commented on Jun 21

For now i need to change my all logic in this report.

Thanks for help

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
help needed

None