How to use mysql function in xPDO query?

- 25 Posts
simonp98 Reply #1, 15 years, 6 months ago
I have built a query that returns a set of resources from the db and sorts them based upon the value of a tv. My problem is that the I want to sort the result using the tv as an integer rather than a string.
I can achieve what I want with a straight forward sql query and the mysql CAST function, but cannot work out how to do this using xPDO. Below are examples of the xPDO query that works with a string value and a the sql query that returns the tv as an Integer

Please can someone show me how to do this using xPDO or at least point me in the right direction?

Thank you in advance.

Here is my original xPDO query that sorts by string value
```
$c = $modx->newQuery('modResource');
$c->innerJoin('modTemplateVarResource', 'Price', '`Price`.`contentid` = `modResource`.`id` AND `Price`.`tmplvarid` = 4');
$c->where(array('published' => 1, 'deleted' => 0, 'Parent' => 3));
$c->limit($limit,$offset);
$c->sortby('Price.value', strtoupper($sort_order_value));
```
here is the statement I am trying to acheive
```
SELECT CAST(modx_site_tmplvar_contentvalues.value AS SIGNED) AS price 
FROM modx_site_content 
INNER JOIN modx_site_tmplvar_contentvalues 
ON modx_site_tmplvar_contentvalues.contentid = modx_site_content.id 
WHERE modx_site_tmplvar_contentvalues.tmplvarid = 4 
AND modx_site_content.parent = 3 
ORDER BY price 
DESC LIMIT 10
```

25 Posts

simonp98 Reply #2, 15 years, 5 months ago

Update:

Here is the solution I found, just in case it is useful for anybody, I was able to use the CAST function in the select method ...

$c = $modx->newQuery('modResource');
$c->select(array('modResource.*','CAST(PriceTV.value AS SIGNED) AS price'));
$c->innerJoin('modTemplateVarResource', 'PriceTV', '`PriceTV`.`contentid` = `modResource`.`id` AND `PriceTV`.`tmplvarid` = 4');
$c->where(array('published' => 1, 'deleted' => 0, 'Parent' => 6));	
$c->limit($limit,$offset);
$c->sortby('price', strtoupper($sort_order_value));