xPDO SELECT DISTINCT?

2 Posts

pick17 Reply #1, 15 years, 3 months ago

Running MODX Revolution 2.1.2-pl

I’ve defined a custom table MyApp_classschd with a classyear column.
xPDO schema says dbtype="year" precision="4" phptype="string"

Relevant example data is:

id classyear
1 2009
2 2009
3 2010
4 2010
5 2010
6 2011

Standard query SELECT DISTINCT campyear FROM myApp_classschd ORDER BY campyear yields the desired result:

classyear
2009
2010
2011

I see DISTINCT referenced in the xPDO->newQuery object:

public ’query’ =>
array
’command’ => string ’SELECT’
’distinct’ => string ’’
’columns’ =>
’from’ =>
’where’ =>
’groupby’ =>
’having’ =>
’orderby’ =>
’offset’ => string ’’
’limit’ => string ’’
’priority’ => string ’’

but I receive Call to undefined method xPDOQuery_mysql::distinct() when I attempt to use it. I cannot find info about DISTINCT in the xPDO->newQuery docs.

Can I use DISTINCT with xPDO->newQuery and if so how do I specify it?

Thanks

4,149 Posts

splittingred Reply #2, 15 years, 3 months ago

/** @var xPDOQuery $c */
$c->query['distinct'] = 'DISTINCT';

shaun mccormick | bigcommerce mgr of software engineering, former modx co-architect | github | splittingred.com

MODX Staff
10,725 Posts

opengeek Reply #3, 15 years, 2 months ago

FYI, I created a ticket for adding an xPDOQuery->distinct() method to set this attribute — Feature #5306.

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

2 Posts

pick17 Reply #4, 15 years, 2 months ago

I appreciate your help, Thanks

2 Posts

pick17 Reply #5, 15 years, 2 months ago

I am attempting to isolate all unique dates in my table.
The ’gamedate’ field is defined with the DATE format.
I run this snippet:

<?php
$path = MODX_CORE_PATH . 'components/umpassoc/';
$result = $modx->addPackage('umpassoc',$path . 'model/','umpassoc_');
if (! $result) {
  return 'Failed to add classes for package "umpassoc"';
}

$c = $modx->newQuery('UmpAssignments');
$c->select(array('gamedate'));
$c->query['distinct'] = 'DISTINCT';
$c->prepare();
echo "Query = [" . $c->toSql() . "]<br>";

$assigndates = $modx->getCollection('UmpAssignments',$c);
return;

The page returns:

Query = [SELECT DISTINCT `gamedate` FROM `umpassoc_ump_assignments` AS `UmpAssignments` ]
Fatal error: Maximum function nesting level of ’100’ reached, aborting! in ...\core\xpdo\om\xpdoobject.class.php on line 855

When I run this query through dbForge I get the desired results.
Can you spot something incorrect here or offer your expertise?
Thanks

MODX Staff
10,725 Posts

opengeek Reply #6, 15 years, 2 months ago

You are summarizing data across objects, but trying to get a collection of objects. This is not really the right approach. For reporting queries like this, it is much better to construct the query, execute it, and work with the result set directly, e.g.

<?php
$path = MODX_CORE_PATH . 'components/umpassoc/';
$result = $modx->addPackage('umpassoc',$path . 'model/','umpassoc_');
if (! $result) {
  return 'Failed to add classes for package "umpassoc"';
}

$assigndates = array();
$c = $modx->newQuery('UmpAssignments');
$c->select(array('gamedate'));
$c->query['distinct'] = 'DISTINCT';
if ($c->prepare() && $c->stmt->execute()) {
    $assigndates = $c->stmt->fetchAll(PDO::FETCH_ASSOC);
}

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

2 Posts

pick17 Reply #7, 15 years, 2 months ago

Understood, thanks