MODx Revoluton objects ordered by an extended field?

Hi there,

I'm trying to filter users by extended fields. It's simple enough to get a list of users and to order them by any profile information (email, name etc.) by doing an inner join on the profile table, but is it possible to order by, or even filter by extended fields?

As an example this get's all users and orders them by email address:

$criteria = $modx->newQuery('modUser');
$criteria->innerJoin('modUserProfile','Profile');
$criteria->sortby('Profile.email','ASC');

The extended fields are all stored together as a JSON object so I'm not sure how you'd work it into a query. Let's say you have an extended field 'favouritecolor' how would you access that for queries?

something like:

$criteria = $modx->newQuery('modUser');
$criteria->innerJoin('modUserProfile','Profile');
$criteria->sortby('Profile.extended.favorouritecolor','ASC');

Anyone know if/how it's possible?

Cheers,

Tom

It is not really possible since they are stored in a single field. Searching and sorting with SQL queries generally requires a normalized data model. In cases where you need to search/sort on extended profile data, it might be worth extending the User class in MODX and providing your own custom table to join and store this extra data in. You can then populate it into the extended profile data for quick access, while still maintaining the normalized data for the searching and sorting requirements.