How to use groupby with MySQL COUNT(*)

☆ A M B ☆
181 Posts

jgulledge19 Reply #1, 14 years, 10 months ago

This is the SQL I want convent to xpdo:

SELECT 
	DISTINCT `ChurchEvents`.`id` AS `ChurchEvents_id`, 
	COUNT(*) AS day_total, 
	DATE_FORMAT(`ChurchEvents`.`start_date`, '%Y-%m-%d' ) AS group_date 
FROM `modx_church_events` AS `ChurchEvents` 
WHERE ( 
	`ChurchEvents`.`start_date` >= '2012-01-01' AND 
	`ChurchEvents`.`start_date` <= '2012-12-31' ) 
GROUP BY group_date

I have got the SQL to create correctly in xpdo like with this:

$query = $this->modx->newQuery('ChurchEvents');
        $query->select('`ChurchEvents`.`id` AS `ChurchEvents_id`, COUNT(*) AS `day_total`, DATE_FORMAT(`ChurchEvents`.`start_date`, \'%Y-%m-%d\' ) AS group_date, `ChurchEvents`.`start_date`' );// http://rtfm.modx.com/display/xPDO20/xPDOQuery.select
        // now sort by location(s) $this->filters['locations']
        if ( !empty($this->filters['locations']) && $this->filters['useLocations']) {
            $query->innerJoin('ChurchEventLocations','EventLocations');
            $query->where(array( 'EventLocations.church_location_id:IN' => $this->filters['locations']));
        }
        $query->where(array(
            'start_date:>=' => $start,
            'start_date:<=' => $end
        ));
        if ( !empty($this->filters['prominent'])) {
            $query->andCondition(array( 'prominent' => $this->filters['prominent'] ) );
        }
        // this is currently only allow 1 calander view - should make this many
        if ( $this->filters['church_calendar_id'] > 0 ) {
            $query->andCondition(array( 'church_calendar_id' => $this->filters['church_calendar_id'] ) );
        }
        // church_ecategory_id
        if ( $this->filters['church_ecategory_id'] > 0 ) {
            $query->andCondition(array( 'church_ecategory_id' => $this->filters['church_ecategory_id'] ) );
        }
        // 
        if ( !empty($this->filters['filterSearch']) ) {
            $query->andCondition(array( 'title:LIKE' => '%'.$this->filters['filterSearch'].'%' ) );
        }
        if ( !$this->modx->user->isAuthenticated('mgr') ) {
            //echo '<br>test status <br>';
            $query->andCondition(array( 'status' => 'approved' ) );
        }
        //$query->sortby('start_date','ASC');
        $query->groupby('group_date');
        $query->prepare();
        echo 'SQL:<br>'.$query->toSQL().'<br>';
        $ev = $this->modx->getIterator('ChurchEvents',$query);
        $this->eventCount = array();
        foreach ($ev as $event ) {
            
            $tmp = $event->toArray();
            list($date) = explode(' ',$event->get('start_date'));
            echo '<br>D: '.$date. ' K: '.$event->get('day_total');
            echo ' Date: '.$event->get('start_date').' id: '.$event->get('id');
            print_r($tmp);
            if ( isset($this->eventCount[$date]) ) {
                $this->eventCount[$date] += $event->get('day_total');
            } else {
                $this->eventCount[$date] = $event->get('day_total');
            }
        }

I just can not get the day_total from the xpdo methods, how do I do that?
The data returned is the ID of the row/record and then only the default data that is assigned via the map class not the day_total or group_date as it does return these if I run the query in SQLYog.

Thanks for any help.

This question has been answered by opengeek. See the first response.

MODX ebook | My Site

discuss.answer

MODX Staff
10,725 Posts

opengeek Reply #2, 14 years, 10 months ago

You should not try to load summary query results into xPDO objects. This is not what they are for. They represent unique rows in the database. For summary queries, you can construct them using the xPDOQuery API, but instead of using the object retrieval methods, you should manually prepare() the PDOStatement, execute() it, and iterate through the result set...

<?php
// Instead of this:
//$ev = $this->modx->getIterator('ChurchEvents',$query);

// try this:
$this->eventCount = array();
if ($query->prepare() && $query->stmt->execute()) {
    while ($event = $query->stmt->fetch(PDO::FETCH_OBJ)) {
        list($date) = explode(' ', $event->start_date);
        if (isset($this->eventCount[$date])) {
            $this->eventCount[$date] += (integer) $event->day_total;
        } else {
            $this->eventCount[$date] = (integer) $event->day_total;
    }
}

Jason Coward
Chief Architect @ MODX
http://www.jasoncoward.com | http://twitter.com/drumshaman | https://github.com/opengeek

☆ A M B ☆
181 Posts

jgulledge19 Reply #3, 14 years, 10 months ago

Great! It works thanks!

MODX ebook | My Site

Answered How to use groupby with MySQL COUNT(*)