But I don't WANT to order by primary key

302 Posts

jaredc Reply #1, 17 years, 1 month ago

I have a search results do-hickey for an app that requires looking at 3 tables. So I figured on using getCollectionGraph - but I couldn’t figure out how to add sort and other criteria info. So then I went with the create a newQuery first, and just use getCollection with a graphed criteria object:



// query obj

$crit = $xpdo->newQuery(’TableOne’);



// graph

$graph = "{’TableTwo’:{’key’:{’TableThree’:{’key’:{}}}}}";

$crit->bindGraph($graph);



// ... a few "andCondition" statements here from the search form that basically look like:

$crit->andCondition("TableOne.fieldA LIKE ’$formData%’");



// sort order

$crit->sortBy(’TableOne.fieldA’);

$crit->sortBy(’TableOne.fieldB’);



// get collection

$collection = $xpdo->getCollection(’TableOne’,$crit);

(Table names etc. changed to protect the innocent.)

I then go thru the rigamarole of creating a table with the ever fabulous MakeTable class. It seems to be [miraculously] pulling the correct records, but sorting them first by TableOne.primaryKey - which I never specified. I don’t know where it’s coming from, or more importantly how to get rid of it.

I’m not sure I’m even going about this correctly. But it seems to be giving me about the results I want so I feel hopeful. I welcome any ideas...

Standard Disclaimer
I could be totally wrong.

MODX Staff
10,725 Posts

opengeek Reply #2, 17 years, 1 month ago

One thing I see right off, is that you are binding a graph, but then you are calling getCollection(). Try calling getCollectionGraph() at the end, similar to this example from 0.9.7 and see what difference it makes...

<?php
$crit = $modx->newQuery('modManagerUser');
$crit->bindGraph('{"modUserProfile":{"internalKey":{}}}');
$wa = array();
if ($_SESSION['mgrRole'] != 1) {
	$crit->where(array ('modUserProfile_internalKey.role:!=' => 1));
}
if (!empty($sqlQuery)) {
    $crit->where(array('modManagerUser.username:LIKE' => '%'.$sqlQuery.'%'), XPDO_SQL_AND, null, 1);
    $crit->orCondition(array('modUserProfile_internalKey.fullname:LIKE' => '%' . $sqlQuery . '%'), null, 1);
    $crit->orCondition(array('modUserProfile_internalKey.email:LIKE' => '%' . $sqlQuery . '%'), null, 1);
}
$crit->sortby('`modManagerUser`.`username`');
$users = $modx->getCollectionGraph('modManagerUser', '{"modUserProfile":{"internalKey":{}}}', $crit);

If it’s still not sorting properly, we’ll explore other possible problems...

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

302 Posts

jaredc Reply #3, 17 years, 1 month ago

Thanks Jason-

Like I mentioned, I tried getCollectionGraph first, but couldn’t figure out how to get it to sort how i wanted, so I did the graph binding in the criteria object and passed it to getCollection instead. Which seemed to work as far as getting the results I wanted. I’ll modify it as your example shows and give ’er a go. But I still don’t understand why it’s ordering by the primary key.

update
Tried getCollectionGraph again, still my collection set isn’t accurate. For example, it returns 2 objects (right number) that are the same (which is bad - they should be different). Because they’re the same, I have no idea how they’re being ordered.

Standard Disclaimer
I could be totally wrong.