xPDO for the advanced

289 Posts

ReSpawN Reply #1, 15 years, 6 months ago

Alright, the topic title may be a bit illusive, but I thought it would be beneficial to create a central topic for the more advanced problems and questions concerning xPDO and it’s functionality. I’ll start off with a problem of my own which I can’t seem to get solved. Shaun (splittingred) also replied to me on Twitter, but this solution didn’t work for me (while I am sure the solution itself is fine).

Perhaps some of these questions aren’t even possible, so they would make for a nice improvement on existing functions or new features.

My problem ánd question is with the getIterator function and getCount, in combination with grouping and distinct rows (unique). I want to use xPDO within MODx because it offers great flexibility in my queries, and so that I won’t have to type up huge chunks of SQL code. So, the problem! I want to SELECT DISTINCT rows from my database while maintaining the where clause. My current buildup is as follows:

$query = $this->modx->newQuery('history');

$query->where(array(
	'userid' => 7
));

$query->groupby('location');

var_dump(count($this->modx->getCollection('history', $query)));

Now, what this does is select all rows from the model name "history" and group it by location. So what I am basically getting is 4 unique, out of 20 total results. This is perfect, however for such a simple query (in SQL), this portion of code is quite extensive. I wanted to dumb it down, like so:

var_dump($this->modx->getCount('history', array(
	'userid' => 7,
	'location:DISTINCT'
)));

This however, is simply not possible. The groupby on the other portion of code works as a DISTINCT, but still a bit more complicated than I want. See, I simply want a number of rows. Now, granted, this doesn’t have to work, but it should at least work with a query.

$query = $this->modx->newQuery('history');
$query->where(array(
	'userid' => 7,
	'location:DISTINCT'
));

// or

$query->where(array(
	'userid' => 7
));

$query->distinct('location');
// or
$query->unique('location');

var_dump(count($this->modx->getCollection('history', $query)));

Now, this example doesn’t work because both distinct and unique do not exist within the xPDOObject class nor it’s extensions (as far as I know). I fiddled around with the Graph functions, but those seem to be more for join manipulations. Not really what I am trying to accomplish.

The second portion of code that acctually works, doesn’t work with getIterator, as it returns 2 as opposed to 4. When I loop over the $stack (object...) and push it into an array and thén count it, it works fine again.

So, what could be the best possible solution?

@MarkGHErnst

Developer at Adwise Internetmarketing, the Netherlands.

☆ A M B ☆
2,475 Posts

Everett Reply #2, 15 years, 5 months ago

I’ve noticed that DISTINCT is somewhat frowned upon in the larger database community... Oracle poo-poo’s it explicitly, although in MySQL it is damn handy. So, given that it is an "oddity" on MySQL and thus maybe not "database agnostic", I don’t know if xPDO will incorporate such a feature.

xPDO is a great framework, but consider the luxury it is to have "database agnostic" code... how often have you deployed code onto multiple environments where the database engine was volatile? I’ve done it exactly zero times. It’s sorta like the Java mantra "code once, deploy anywhere", but instead of being system agnostic, it’s database agnostic... in other words, what makes sense for MODx as a platform and deployment method may be setting the bar arbitrarily high for your custom additions to MODx (Snippets, Plugins, etc).

So for reporting queries especially I say "forget it." MySQL makes reporting queries pretty simple, and I don’t think there’s any shame in working those verbatim queries into your code "just like the old days". The framework should help you, not hold you back. Man, the stuff I’ve done with xPDO makes all the CRUD stuff wicked easy to deal with, but reporting with stuff like DISTINCT or sub-selects or any of that... meh... I usually end up just writing the query, piping in a couple variables.

Just my two-cents.

Hosting stuff: http://fireproofsocks.com/
Coding stuff: http://craftsmancoding.com/
Blog stuff: http://tipsfor.us/

19 Posts

Redjik Reply #3, 15 years, 4 months ago

I would recommend to look at groupby + select...

btw, this couple is much better optimized in comparason with distinct...

MODX Staff
10,725 Posts

opengeek Reply #4, 15 years, 4 months ago

FWIW, MySQL and SQLite are the only databases that support GROUP BY while still including all the columns that are not calculated or appear in the GROUP BY. So that is not a database agnostic approach either. TBH, the problem is that xPDOObject instances represent unique rows of a specific table. Reporting queries do not return instances of these table rows. They return specific calculated data for specific purposes. However, that said you can use the xPDO API’s to construct any valid query, it just simply is not always appropriate to use the methods like getObject/getCollection with these queries because they expect a complete table row instance. For such queries, you can still construct the xPDOQuery in the appropriate way, then simply prepare() the PDOStatement object, execute() it and fetch() the result sets rows it returns, using them as you would any PDO result set. For example:

<?php
$query = $this->modx->newQuery('history');
$query->select(array(
    'location',
    'COUNT(id) AS locationCount'
));
$query->where(array(
	'userid' => 7
));

$query->groupby('location');
$query->sortby('locationCount', 'DESC');

if ($query->prepare() && $query->stmt->execute()) {
    while ($row = $query->stmt->fetch(PDO::FETCH_ASSOC)) {
        // do stuff with the row data here
    }
}

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