$c = $modx->newQuery('modUser');
$c->innerJoin ('modUserAttributes','UserAttributes'); // tried leftJoin too
$c->innerJoin ('modUserGroupMember','UserGroupMembers');
$c->innerJoin ('modUserGroup','UserGroup','UserGroupMembers.user_group = UserGroup.id');
$c->leftJoin ('modUserGroupRole','UserGroupRole','UserGroupMembers.role = UserGroupRole.id');
$c->where(array(
'active' => '1',
'UserAttributes.photo:!=' => '',
'UserGroupMembers.user_group' => '2',
));
$c->select('
`modUser`.*,
`UserGroup`.`id` AS `usergroup`,
`UserGroup`.`name` AS `usergroup_name`,
`UserGroupRole`.`name` AS `role`,
`UserGroupRole`.`name` AS `role_name`
');
$c = $modx->newQuery('modUser');
$c->innerJoin ('modUserProfile','Profile');
$c->innerJoin ('modUserGroupMember','UserGroupMembers');
$c->innerJoin ('modUserGroup','UserGroup','`UserGroupMembers`.`user_group` = `UserGroup`.`id`');
$c->leftJoin ('modUserGroupRole','UserGroupRole','`UserGroupMembers`.`role` = `UserGroupRole`.`id`');
$c->where(array(
'active' => true,
'Profile.photo:!=' => '',
'UserGroupMembers.user_group' => '2',
));
$c->select('
`modUser`.*,
`UserGroup`.`id` AS `usergroup`,
`UserGroup`.`name` AS `usergroup_name`,
`UserGroupRole`.`name` AS `role`,
`UserGroupRole`.`name` AS `role_name`
');
$users = $modx->getCollection('modUser',$c);
$c->where(array(
'Profile.photo:!=' => '',
'Profile.extended.availability:>=' => time (), //this line is the problem
'UserGroupMembers.user_group' => '2',
));
MODX Staff
These are stored as raw JSON in a text field. You’ll have to use a LIKE expression OR a boolean full-text expression for extended attribute searches, but your particular situation won’t work here because you cannot use MySQL comparison operators (i.e. greater than or equal to) in these expressions. You could do a partial match though for any user with an availability specified or fetch all records and use PHP to filter your results on the extended attributes.
Sorry for reviving the post, is there a special syntax for extended json values in a where ?
$c->where(array( 'Profile.photo:!=' => '', 'Profile.extended.availability:>=' => time (), //this line is the problem 'UserGroupMembers.user_group' => '2', ));
<?php
// assuming rest of criteria specified first...
$c->where(array(
'Profile.photo:!=' => '',
'Profile.extended:LIKE' => '%"availability":%', //get any profile with an availability specified
'UserGroupMembers.user_group' => '2',
));
$results = array();
$collection = $modx->getCollectionGraph('modUser', '{"Profile":{}}', $c);
foreach ($collection as $object) {
$extended = $object->get('extended');
if ($extended && isset($extended['availability']) && $extended['availability'] >= time()) {
$results[] = $object;
}
}
unset($collection);
// now use your filtered user objects contained in $results
?>
MODX Staff