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

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

 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"
                )
            )
        );
    }
}

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"

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.

same syntax error

  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

        $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 :)

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

Thanks for asking :)

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 

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) "" } }

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

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

Thanks for help