Fenom $_modx->getResources complex select

Development
, ,
1

Hello I am trying to use calculations in select within fenom getResources. The problem is that I need to have tv’s accecible there to use :slight_smile:

{var $resources = $_modx->getResources(
['published' => 1, 'deleted' => 0],
['parents' => $_modx->config.ubeeo_vacancy_parent, 'includeTVs' => 'ubeeo_latitude,ubeeo_longitude,ubeeo_city,ubeeo_employment,ubeeo_organisationNameExternal', 'sortby' => 'publishedon', 'sortdir' => 'ASC', 'limit' =>  9999, 'depth' => 0, 'having' => 'afstand > 10', 'showLog' => 1, 'select' => ['AS:Afstand' => '( 3959 * acos( cos( radians(52.010269) ) * cos( radians( tv.ubeeo_latitude ) ) * cos( radians( tv.ubeeo_longitude ) - radians(4.709830) ) + sin( radians(52.010269) ) * sin( radians( tv.ubeeo_latitude ) ) ) )']]
)}

I tried TV. and tv. and also without tv. or TV. but the error I get is:

[2021-10-12 11:46:54] (ERROR @ /var/www/core/components/pdotools/model/pdotools/pdofetch.class.php : 1083) [pdoTools] Could not load collection of "modResource": Error 42S22: Unknown column 'tv.ubeeo_latitude' in 'field list'

And of course the tv is not in there… in modResource. but how do I select modTemplateVar? (if thats the one I need)

2

I believe instead of tv.ubeeo_latitude you have to use `TVubeeo_latitude`.`value` in the ‘select’ to make it work.

2 Likes
3

Yes!
Thank you, this works now:

{var $resources = $_modx->getResources(
['published' => 1, 'deleted' => 0],
['parents' => $_modx->config.ubeeo_vacancy_parent, 
'having' => 'afstand > 10', 
'includeTVs' => 'ubeeo_latitude,ubeeo_longitude,ubeeo_city,ubeeo_employment,ubeeo_organisationNameExternal', 
'sortby' => 'publishedon', 
'sortdir' => 'ASC', 
'limit' =>  9999, 
'depth' => 0, 
'select' => [
'modResource'=>'pagetitle,id,',
'afstand' => '( 3959 * acos( cos( radians(52.010269) ) * cos( radians( `TVubeeo_latitude`.`value` ) ) * cos( radians( `TVubeeo_longitude`.`value` ) - radians(4.709830) ) + sin( radians(52.010269) ) * sin( radians( `TVubeeo_latitude`.`value` ) ) ) ) AS `afstand`']]
)}

the afstand as key could be anything, it is ignored because its not an existing class name. So I needed to add AS `afstand` at the end.

Thank yo so much!