Search code examples
phppdodynamic-sqlatk4

DSQL condition with and without comma

Hi I am writing some DSQL queries with Agile Toolkit but am a little confused. Given the following DSQL code:

$select = $select->add('Model_Rental')->dsql()
                         ->field('id')
                         ->where('dvd_id', $select->getField('id'))
                         ->where('is_returned!=', 'Y');
return "if(($select) is null,'N','Y')";

I get the following SQL error:

pdo_error: SQLSTATE[HY093]: Invalid parameter number: no parameters were bound
mode: select
params: Array ( )
query: select (select `name` from `movie` where `dvd`.`movie_id` = `movie`.`id` ) `movie`,`code`,if((select `id` from `rental` where `dvd_id` = `dvd`.`id` and `is_returned` != :a ) is null,'N','Y') `is_rented`,`id` from `dvd`
template: select [options] [field] [from] [table] [join] [where] [group] [having] [order] [limit]

But when I define the condition right inside the same quote I don't get the error:

$select = $select->add('Model_Rental')->dsql()
                         ->field('id')
                         ->where('dvd_id', $select->getField('id'))
                         ->where('is_returned!="Y"');
return "if(($select) is null,'N','Y')";

Which gives me the expected:

select (select `name` from `movie` where `dvd`.`movie_id` = `movie`.`id` ) `movie`,`code`,if((select `id` from `rental` where `dvd_id` = `dvd`.`id` and is_returned!="Y" ) is null,'N','Y') `is_rented`,`id` from `dvd`

Why do I have to write the state as:

->where('is_returned!="Y"');

Why can I not write is as usual:

->where('is_returned!=', 'Y');

Thanks!

Solution

  • Update: DSQL has been refactored into a stand-alone library: git.io/dsql

    $select representing a $dsql object contains both the query and the arguments. When you convert it into string, it is not inserting arguments. You need this

    return ($this->expr("if([subselect] is null, 'N','Y')")->setCustom('subselect'=>$select));

    See also: http://agiletoolkit.org/doc/dsql/expr

    NOTE: this does not return string, it returns DSQL query.

    To answer your question:

    By default where() treats second argument as a parameter and adds it into parametric variable. You loose it when casting to string. If you specify a single argument, no parameter is used and you get a fully working query.