#1440: Can't use placeholders if ->limit() is used in the query
------------------------------+---------------------------------------------
Reporter: P.Vogelaar | Owner: romanb
Type: defect | Status: reopened
Priority: minor | Milestone: 1.0.3
Component: Query/Hydration | Version: 1.0.0
Resolution: | Keywords: limit placeholder where
Has_test: 0 | Mystatus: Pending Core Response
Has_patch: 0 |
------------------------------+---------------------------------------------
Changes (by anj):
* status: closed => reopened
* resolution: invalid =>
Old description:
> I use the sfDoctrine plugin with symfony 1.1. The doctrine version is
> 1.0-rc2.
>
> The following works:
>
> {{{
>
> $root =
> Doctrine::getTable('Question')->getTree()->findRoot($root_question_id);
> $query = Doctrine_Query::create()
> ->select('
> q.name,
> a.name,
> )
> ->from('Question q')
> ->leftJoin("q.Answer a")
> ->leftJoin("q.QuestionGroup qg")
> ->leftJoin("q.Result r")
> ->where('q.level > 0')
> ->orderBy('qg.weight ASC, qg.name ASC')
> ->offset($pager['offset'])
> ->limit($pager['limit'])
> ;
> }}}
>
> But if I add this:
>
> {{{
> $query->addWhere('r.anonymous_user_id = ?', 'test');
> }}}
>
> I get:
>
> {{{
> SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error
> in your SQL syntax; check the manual that corresponds to your MySQL
> server version for the right syntax to use near '? ORDER BY q5.weight
> ASC, q5.name ASC LIMIT 3' at line 1
> }}}
>
> As soon as I comment "->limit($pager['limit'])", the query works again.
New description:
I use the sfDoctrine plugin with symfony 1.1. The doctrine version is
1.0-rc2.
The following works:
{{{
$root =
Doctrine::getTable('Question')->getTree()->findRoot($root_question_id);
$query = Doctrine_Query::create()
->select('
q.name,
a.name,
)
->from('Question q')
->leftJoin("q.Answer a")
->leftJoin("q.QuestionGroup qg")
->leftJoin("q.Result r")
->where('q.level > 0')
->orderBy('qg.weight ASC, qg.name ASC')
->offset($pager['offset'])
->limit($pager['limit'])
;
}}}
But if I add this:
{{{
$query->addWhere('r.anonymous_user_id = ?', 'test');
}}}
I get:
{{{
SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error
in your SQL syntax; check the manual that corresponds to your MySQL server
version for the right syntax to use near '? ORDER BY q5.weight ASC,
q5.name ASC LIMIT 3' at line 1
}}}
As soon as I comment "->limit($pager['limit'])", the query works again.
Comment:
Have also the same problem on current 1.1 branch.
Not working:
{{{
$query = Doctrine_Query::Create()
->from('Recipe r')
->leftJoin('r.Company c')
->leftJoin('r.Ingredient ri')
->addWhere('c.id = ?', 2)
->limit(20)
->offset(0);
}}}
Working:
{{{
$query = Doctrine_Query::Create()
->from('Recipe r')
->leftJoin('r.Company c')
->leftJoin('r.Ingredient ri')
->addWhere('c.id = 2')
->limit(20)
->offset(0);
}}}
Working:
{{{
$query = Doctrine_Query::Create()
->from('Recipe r')
->leftJoin('r.Company c')
//->leftJoin('r.Ingredient ri')
->addWhere('c.id = ?', 2)
->limit(20)
->offset(0);
}}}
Models:
Recipe Company = n:1
Recipe Ingredient = n:m
After some debugging we found out, that only the combination of n:m
relations and limit/offset params in the query with mysql database, the
Doctrine_Query builds the subquery without replacing the "?" sign with the
given values.
Doctrine_Query Line about 1221:
so only if $needsSubQuery is set to true and limit and offset params are
used, the exception is thrown:
{{{
if ( ( ! empty($this->_sqlParts['limit']) || !
empty($this->_sqlParts['offset'])) && $needsSubQuery) {
$subquery = $this->getLimitSubquery();
// what about composite keys?
$idColumnName =
$table->getColumnName($table->getIdentifier());
switch (strtolower($this->_conn->getDriverName())) {
case 'mysql':
// mysql doesn't support LIMIT in subqueries
$list = $this->_conn->execute($subquery,
$params)->fetchAll(Doctrine::FETCH_COLUMN);
$subquery = implode(', ',
array_map(array($this->_conn, 'quote'), $list));
break;
case 'pgsql':
// pgsql needs special nested LIMIT subquery
$subquery = 'SELECT '
.
$this->_conn->quoteIdentifier('doctrine_subquery_alias.' . $idColumnName)
. ' FROM (' . $subquery . ') AS
doctrine_subquery_alias';
break;
}
}}}
--
Ticket URL: <http://trac.doctrine-project.org/ticket/1440#comment:3>
Doctrine <http://www.phpdoctrine.org>
PHP Doctrine Object Relational Mapper
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"doctrine-svn" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.co.uk/group/doctrine-svn?hl=en-GB
-~----------~----~----~----~------~----~------~--~---
