when I try this:
$c->where(array('YEAR(`'.$classname.'`.`createdon`)' => $scriptProperties['year']));
I get
SELECT id, MONTH(createdon) as name
FROM `modx_boerse_angebote` AS `Angebote`
WHERE `YEAR(`Angebote`.`createdon`)` = '2010' <- how can I remove this backticks?
GROUP BY name
ORDER BY MONTH(`Angebote`.`createdon`) DESC
how can I remove the backticks arround: YEAR(`Angebote`.`createdon`)
ok I found a workarround, but isn’t there a better way?
$this->modx->_escapeCharOpen=’’;
$this->modx->_escapeCharClose=’’;
$c->where(array(’YEAR(`’.$classname.’`.`createdon`)’ => $scriptProperties[’year’]));
$this->modx->_escapeCharOpen=’`’;
$this->modx->_escapeCharClose=’`’;
<?php
$c->where("YEAR(" . $modx->escape($classname) . '.' . $modx->escape('createdon') . ") = " .$year, xPDOQuery::SQL_AND);
Quote from: OpenGeek at Nov 01, 2010, 09:00 AM
No no no.
Just make it a string SQL expression with a binding; xPDO does not at this time support using DB function literals in the key => value expressions handled by the O/R mappings.
<?php
$c->where("YEAR(" . $modx->getTableName($class) . '.' . $modx->escape('createdon') . ") = ?"), xPDOQuery::SQL_AND, array($year));
I was afraid about your ’No no no.’
Thanks!
Hmmm...
with:
$c->where("YEAR(" . $modx->getTableName($classname) . '.' . $modx->escape('createdon') . ") = ?", xPDOQuery::SQL_AND, array($year));
i get
SELECT `Angebote`.*, `Resource`.`pagetitle` AS `firma` FROM `modx_boerse_angebote` AS `Angebote` LEFT JOIN `modx_site_content` `Resource` ON `Angebote`.`firma_resource` = `Resource`.`id` WHERE YEAR(`modx_boerse_angebote`.`createdon`) = ? ORDER BY published ASC LIMIT 20
[Edit]
this seems to do it:
$c->where("YEAR(" . $modx->escape($classname) . '.' . $modx->escape('createdon') . ") = " .$year, xPDOQuery::SQL_AND);
-
MODX Staff
- 10,725 Posts
My apologies for confusion; I just didn’t notice you were using a model with classname as the table alias already, so I gave the example as if you were just using the tablename.
