XPDO help with Select Distinct

98 Posts

jimsheppard Reply #1, 13 years, 8 months ago

MODX Revolution 2.2.4-pl (traditional)

I'm a frustrated xPDO newbie, attempting to create an A-Z list from a custom association members table. The links from list will serve as an alphabetical filter to an association members list.

`Members` (
  `MemberID` int(11) NOT NULL AUTO_INCREMENT,
  `Surname` varchar(255) DEFAULT NULL,
  `MemberName` varchar(255) DEFAULT NULL,
  `Status` varchar(100) DEFAULT NULL,
  `Seal` varchar(10) DEFAULT NULL,
  `CompanyID` int(11) DEFAULT NULL,
  PRIMARY KEY (`MemberID`)
) ENGINE=MyISAM;

In MySql this would be a simple

"SELECT DISTINCT UPPER(LEFT(Surname,1)) as letters FROM Members ORDER BY letters”;

How do I do this with xPDO?

I've looked at various examples but my current comfort level with xPDO is preventing me getting anywhere with this.

A few lines of code to get me started would be greatly appreciated.

Thanks

Give a man the answer, and he’ll only have a temporary solution. Teach him the principles that led you to that answer, and he will be able to create his own solutions in the future.

98 Posts

jimsheppard Reply #2, 13 years, 8 months ago

Found the answer here: http://rtfm.modx.com/display/xPDO20/xPDO.query. At least it does the job for me.

Give a man the answer, and he’ll only have a temporary solution. Teach him the principles that led you to that answer, and he will be able to create his own solutions in the future.

MODX Staff
10,725 Posts

opengeek Reply #3, 13 years, 8 months ago

Have you created a model and generated the classes and map for this table via xPDO? If not, just send that query direct and use the result set as you would any PDO result set:

$stmt = $modx->query("SELECT DISTINCT UPPER(LEFT(Surname,1)) as letters FROM Members ORDER BY letters");
if ($stmt) {
    foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
        echo $row['letters'];
    }
}

If you have created an xPDO model for this table, you could do something like this using the classes/map you generated to build the query with the API (xPDOQuery):

$query = $modx->newQuery('Members');
$query->distinct();
$query->select(array(
    'letters' => "UPPER(LEFT(Surname, 1))"
));
$query->sortby("letters", 'ASC');

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

98 Posts

jimsheppard Reply #4, 13 years, 8 months ago

Quote from: opengeek at Jan 22, 2013, 03:55 PM

If you have created an xPDO model for this table, you could do something like this using the classes/map you generated to build the query with the API (xPDOQuery):
$query = $modx->newQuery('Members');
$query->distinct();
$query->select(array(
    'letters' => "UPPER(LEFT(Surname, 1))"
));
$query->sortby("letters", 'ASC');

Although I have the query working using a different approach. This example crashes my server any idea what the reason may be?

Give a man the answer, and he’ll only have a temporary solution. Teach him the principles that led you to that answer, and he will be able to create his own solutions in the future.

81 Posts

Dunnock Reply #5, 13 years, 8 months ago

Wild guess would be that you need to have primary key field selected also, so for the select array add the PK field (most likely id or idx etc...).

MODX Staff
10,725 Posts

opengeek Reply #6, 13 years, 8 months ago

Quote from: dunnock at Jan 24, 2013, 06:05 AM

Wild guess would be that you need to have primary key field selected also, so for the select array add the PK field (most likely id or idx etc...).

That would be true only if use getObject or getCollection with the example; but in this case you would not want objects, just the result set:

$query = $modx->newQuery('Members');
$query->distinct();
$query->select(array(
    'letters' => "UPPER(LEFT(Surname, 1))"
));
$query->sortby("letters", 'ASC');
if ($query->prepare() && $query->stmt->execute()) {
    foreach ($query->stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
        echo $row['letters'];
    }
}

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

98 Posts

jimsheppard Reply #7, 13 years, 8 months ago

Thanks Jason, this PDO lark has a very steep learning curve, but eventually with help I'll get there. I am a mega personal genealogical project that I want to develop with MODx and PDO, but that will need to wait for a while.

I really believe that MODx is great, the problem I, and others have is the lack of clear tutorials. This isn't mean as a criticism as I have been guilty of the same in my areas of expertise. Often we are too familiar with our material and consequently tend to miss small details that are needed for a novice to get started with the process when preparing tutorial packages.

Give a man the answer, and he’ll only have a temporary solution. Teach him the principles that led you to that answer, and he will be able to create his own solutions in the future.