getCollectionGraph usage - xPDO | MODX Community Forums

19 Posts

modx125 Reply #1, 13 years, 1 month ago

Hi all,

Here is what I have and what I'm trying to do:

Applicants apply to position, fill out 'where did you hear about us'. This data is stored in table modUserProfile.extended in JSON form (I believe). What I am trying to do is run a query to basically COUNT the number of applicants that said 'Through a friend','Craigslist','Indeed',...etc.

Here is an example of the table field that this data is stored in:

{"referred":"Through a friend","headshot":"77547235.jpeg","registerbtn":"1","rand_id":"137014","sample_report":"8.26.13_-Starbucks_1377560995.xls"}

Here is what I have tried:

$el = $modx->newQuery('modUserProfile');
$el->where(array('modUserProfile.city' => ''.$g->name.''));	
$el->where(array('modUserProfile.extended' => 'Elance'));	
$elance = $modx->getCollection('modUserProfile',$el);
$ret.='<td>'.count($elance).'</td>';

However, it has returned no results. I know I'm not checking the 'extended' field correctly because I'm not too experienced with JSON. Any ideas? I have checked this http://rtfm.modx.com/xpdo/2.x/getting-started/using-your-xpdo-model/retrieving-objects/getcollectiongraph which seems to be the right path but I'm unable to put it all together. Thanks in advance!

This question has been answered by BobRay. See the first response.

[ed. note: modx125 last edited this post 13 years, 1 month ago.]

5,888 Posts

Bruno17 Reply #2, 13 years, 1 month ago

you can not really search in json-fields, better would be to have your searchable datas stored in a custom-table.

You can try, if something like that is working:

$el = $modx->newQuery('modUserProfile');
$el->where(array('modUserProfile.city' => $g->name));   
$el->where(array('modUserProfile.extended:LIKE' => '"referred":"Through a friend"'));
if ($elance = $modx->getCollection('modUserProfile',$el)){
    $ret.='<td>'.count($elance).'</td>';
}

-------------------------------

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!

19 Posts

modx125 Reply #3, 13 years, 1 month ago

Quote from: Bruno17 at Sep 13, 2013, 12:40 PM

you can not really search in json-fields, better would be to have your searchable datas stored in a custom-table.

Thanks for this. I was wondering if even possible to search in json-fields...now I know. I'll take a look at your solution and see what I can implement. Thanks again!

discuss.answer

24,544 Posts

BobRay Reply #4, 13 years, 1 month ago

It is possible to do a brute-force search on extended fields by getting every profile in the DB and then using $profile->get('extended') and in_array() on each of them, but it would be very slow and memory intensive. If you don't expect to have a lot of users, it might be acceptable.

To expand on Bruno17's suggestion, you could extend the modUser object and add an alias for your own Profile table with something like this:

<object class="extMyUser" extends="modUser">    
    <composite alias="MyProfile" class="extMyProfile" local="id" foreign="user" owner="local" cardinality="one" />
</object>

Then you could use getCollectionGraph to pull in any users, along with their custom profile, and (if necessary) the standard profile all at once using any criteria with a single query.

Did I help you? Buy me a beer
Get my Book: MODX:The Official Guide
MODX info for everyone: http://bobsguides.com/modx.html
My MODX Extras
Bob's Guides is now hosted at A2 MODX Hosting

19 Posts

modx125 Reply #5, 13 years, 1 month ago

Quote from: BobRay at Sep 13, 2013, 06:10 PM

It is possible to do a brute-force search on extended fields by getting every profile in the DB and then using $profile->get('extended') and in_array() on each of them, but it would be very slow and memory intensive. If you don't expect to have a lot of users, it might be acceptable.

Ok, I see what you mean here. It's likely the best solution since our user volume is pretty low...and this query is only accessed monthly. Thanks for this info and the expansion on the getCollectionGraph, it was very helpful!

24,544 Posts

BobRay Reply #6, 13 years, 1 month ago

In that case, something like this might do it:

$matches = array();
$profiles = $modx->getCollection('modUserProfile');

foreach ($profiles as $profile) {
    $extended = $profile->get('extended');
    if ($extended && in_array('Elance', $extended, true) ) {
         $matches[] = $profile;
    }
}

/* At this point $matches is an array of the 
   profile objects that match the criterion */

If you need the user object (for the username), you can use $profile->getOne('User');

Did I help you? Buy me a beer
Get my Book: MODX:The Official Guide
MODX info for everyone: http://bobsguides.com/modx.html
My MODX Extras
Bob's Guides is now hosted at A2 MODX Hosting

MODX Staff
10,725 Posts

opengeek Reply #7, 13 years, 1 month ago

I would recommend using getIterator() instead of getCollection() here. With the iterator, you will prevent using memory for all the Profile records at once, instead only loading one object at a time from the result set as it is iterated. This will allow you to process a lot more records with this approach, possibly in a shorter amount of time.

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

Answered getCollectionGraph usage - xPDO