How to pass parameter in nested SELECT clause.

Jenni Yrjänä commented on Jan 4

Hello!

I am trying to retrieve information from database using this:

public function setup() {

    $this->src('table')
    ->query("
    SELECT video.title, a.videoid, SUM(a.end_player-a.seekto)
    FROM a
    INNER JOIN video ON video.videoid = a.videoid
    WHERE a.videoid IN ( SELECT videoid
                                        FROM video
                                        WHERE category=CONCAT('#', :courseid, '#')
    GROUP BY a.videoid;
    ")
    ->params(array(
        ':courseid'=>$this->params["id"],
    ))
    ->pipe($this->dataStore('result1'));
}

}

It seems that koolreport get broken if parameter is passed in nested select clause because for example

" ... WHERE category = '#100#' ..."

seems to be working just fine.

How do I solve this problem?

Sebastian Morales commented on Jan 4

I'm not sure if it's possible for PHP database driver to bind parameter in a sql function call (e.g, CONCAT in this case) like that. There are certain limitations of where and when one can bind parameters. In this case we would try to use an alternative solution:

    $this->src('table')
    ->query("
    SELECT video.title, a.videoid, SUM(a.end_player-a.seekto)
    FROM a
    INNER JOIN video ON video.videoid = a.videoid
    WHERE a.videoid IN ( SELECT videoid
                                        FROM video
                                        WHERE category=:category
    GROUP BY a.videoid;
    ")
    ->params(array(
        ':category'=>'#' . $this->params["id"] . '#' ,
    ))
    ->pipe($this->dataStore('result1'));

Hope this works for you.

Jenni Yrjänä commented on Jan 4

Thank you! Your answer really solved the problem!

CONCAT works on simpler sentences, for example:

$this->src('table')

    ->query("
    SELECT count(videoid)
    FROM video
    WHERE category=CONCAT('#', :courseid, '#')
    ")
    ->params(array(
        ':courseid'=>$this->params["id"],

))

But when I used CONCAT + parameter in nested clause, it stopped working.

Thank you again! This was really helpful!

KoolReport's Forum

How to pass parameter in nested SELECT clause. #3222

Build Your Excellent Data Report