Weird error and this is driving me crazy all the day. To me it seems a bug because there are no positional parameters in my query. Here is the method:
public function getAll(User $user, DateTime $start = null, DateTime $end = null)
{
$params = array('user_id' => $user->getId());
$rsm = new \Doctrine\ORM\Query\ResultSetMapping(); // Result set mapping
$rsm->addScalarResult('subtype', 'subtype');
$rsm->addScalarResult('count', 'count');
$sms_sql =
"SELECT CONCAT('sms_', IF(is_auto = 0, 'user' , 'auto')) AS subtype, " .
"SUM(messages_count * (customers_count + recipients_count)) AS count " .
"FROM outgoing_message AS m INNER JOIN small_text_message AS s ON " .
"m.id = s.id WHERE status <> 'pending' AND user_id = :user_id";
$news_sql =
"SELECT CONCAT('news_', IF(is_auto = 0, 'user' , 'auto')) AS subtype, " .
"SUM(customers_count + recipients_count) AS count " .
"FROM outgoing_message AS m JOIN newsletter AS n ON m.id = n.id " .
"WHERE status <> 'pending' AND user_id = :user_id";
if($start) :
$sms_sql .= " AND sent_at >= :start";
$news_sql .= " AND sent_at >= :start";
$params['start'] = $start->format('Y-m-d');
endif;
$sms_sql .= ' GROUP BY type, is_auto';
$news_sql .= ' GROUP BY type, is_auto';
return $this->_em->createNativeQuery("$sms_sql UNION ALL $news_sql", $rsm)
>setParameters($params)->getResult();
}
And this throws the exception:
SQLSTATE[HY093]: Invalid parameter number: mixed named and positional
parameters
Array $arams is OK and so generated SQL:
var_dump($params);
array (size=2)
'user_id' => int 1
'start' => string '2012-01-01' (length=10)
Strangest thing is that it works with "$sms_sql" only! Any help would make my day, thanks.
Update
Found another strange thing. If i change only the name (to start_date instead of start):
if($start) :
$sms_sql .= " AND sent_at >= :start_date";
$news_sql .= " AND sent_at >= :start_date";
$params['start_date'] = $start->format('Y-m-d');
endif;
What happens is that Doctrine/PDO says:
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'sent1rt_date'
in 'where clause'
... as string 1rt was added in the middle of the column name! Crazy...
