Problems converting query to XPDO (left join / group / count)

68 Posts

megasteve4 Reply #1, 14 years, 3 months ago

So I have this query:

SELECT `cust_agencies`.*,`cust_reviews`.*, count(`cust_reviews`.`score`) as `total_score`
FROM `cust_agencies`
LEFT JOIN `cust_reviews`
ON `cust_agencies`.`agency_id` = `cust_reviews`.`agency_id`
WHERE `cust_agencies`.`ignored` LIKE "n"
GROUP BY `cust_agencies`.`agency_id`
ORDER BY `total_score` DESC

which works find in phpma. However I would like to convert it to xpdo......

I have got this far

     
$agency_query = $modx->newQuery('Agencies');
$agency_query ->select('Agencies.*, Reviews.*', count('Reviews.score'));
$agency_query ->leftJoin('Agencies', 'Reviews', array('Agencies.agency_id =Reviews.agency_id'));
$agency_query->where(array('Agencies.county' => $county_id, 'Agencies.ignored' => 'n'));
$query->groupby('Reviews.score');
//$agency_query->prepare();
//return $agency_query->toSql();
$agencies = $modx->getCollection('Agencies',$agency_query );

however that fails - I believe that you can not use count() like that. Does anyone have any recommendations for a join / count / group by query?

I have had a good look but to no avail. Any help appreciated!

Thanks.

88 Posts

lazylegs Reply #2, 14 years, 3 months ago

You are correct with your guess about the count() function. That is the php function count() not SQL function.

To get the count you need to use the sql function like this

$agency_query ->select('Agencies.*, Reviews.*, count(Reviews.score) as total_score');

Almost retired from web-development industry but still randomly writing at Lazylegs.info and on schedule hopefully in near future to finish Oracle and PostgreSQL ports of MODX

68 Posts

megasteve4 Reply #3, 14 years, 3 months ago

Thanks for the pointer lazylegs (... bizarrely enough I have just been reading a couple of articles on your site!! - great content BTW like the epic geolocation article http://lazylegs.info/articles/xpdo-and-modx-with-geonames-and-haversine-formula/)

My proposed xpdo above had some classic 'need to go to bed' errors

Have now come at it with a clearer head and the below works with the join / count and group by...

      $agency_query = $modx->newQuery('Agencies');
      $agency_query ->select('Agencies.*, Reviews.*, count(Reviews.score) as total_score');
      $agency_query ->leftJoin('Reviews', 'Reviews', array('Agencies.agency_id = Reviews.agency_id'));
      $agency_query ->where(array('Agencies.county' => $county_id, 'Agencies.ignored' => 'n'));
      $agency_query ->groupby('Agencies.agency_id');
      $agency_query ->sortby('total_score','DESC');
      //debug $agency_query->prepare();
      //debug return $agency_query->toSql();

      $agencies = $modx->getCollection('Agencies',$agency_query );

Thanks

88 Posts

lazylegs Reply #4, 14 years, 3 months ago

I should learn to write like opengeek does on this section of forums. A recommendation for this kind of queries is to use next kind of approach for select() and groub by methods if planning to port the possible extra to multiple database platforms and to keep the code database agnostic.

$agency_query ->select(array(
    $modx->getSelectColumns('Agencies', 'Agencies'),
    $modx->getSelectColumns('Reviews', 'Reviews'), 
    /* If reviews and agencies share same column names, add third option "re_" for example 
       to distinct the columns with prefix re_ when used in the loop for getCollection */
    'total_score' => "count({$modx->escape('Reviews')}.{$modx->escape('score')}"
    /* This will alias the count() function to total_score to be use in the output of the query
       Eg. the array associative key is turned to alias, same goes for subqueries etc.. 
       which you might have notice be used in the article
    */
));

Same goes for the groupby() where MySQL can have one column only by SQL standard you have to list all columns of the query that are not aggregate grouping functions (correct me if wrong). A quick and dirty solution I've used is to use the same getSelectColumns function in this manner

$agency_query ->groupby($modx->getSelectColumns('Agencies', 'Agencies') .",".$modx->getSelectColumns('Reviews', 'Reviews'));

This way the columns are always escaped. Could select certain columns if there would be need for it, they just need to match the cols in the select clause.

Hopefully did not shoot badly off, few months away from xPDO world and I have forgot most of it

Almost retired from web-development industry but still randomly writing at Lazylegs.info and on schedule hopefully in near future to finish Oracle and PostgreSQL ports of MODX