[solved] Xpdo and jointure with a php array

[tulavu]

Hello,

I am trying to develop a game to learn foreign vocabulary.

When the page loads I use xpdo to fetch all the words from the database, the words are sorted by ‘id’ :

    $c = $modx->newQuery( DOODLECLASS );
    $c->select( $modx->getSelectColumns( DOODLECLASS, DOODLECLASS ) );
    $c->sortby( 'id', 'ASC' );
    $c->prepare();
    $c->stmt->execute();

In another sql table I have a field where the scores for each word are recorded as JSON. After decoding this JSON I have a php array containing word ids and their scores.

In this example we can see word ‘0’ has a score of 624.

$words = [ '0' => 624, '1' => 216, ... , '1000' => 546 ]

Now I am tying to adapt the xpdo request in order to sort the words by their scores.

I think in order to sort the words by their scores I will have to make a jointure between the words table and the php array containing the scores.

Is it possible with xpdo to make a join with the data in my php array? Any hint to help me please?

[bobray]

You could certainly do it with a join, but if they are xPDO objects with aliases for their related objects, you should be able to get the words sorted by score with getCollectionGraph().

[halftrainedharry]

One way to do this is to use FIELD to sort the data. Something like this:

$words = [ '0' => 624, '1' => 216, ... , '1000' => 546 ];
asort($words); //sort by score
$keys = array_keys($words); //return all the keys
$str_keys = implode(',',$keys); //all keys in comma-separated string

$q = $modx->newQuery( DOODLECLASS );
$q->sortby('FIELD(id,' . $str_keys . ')');
...

If an ID is not in the list $str_keys then FIELD returns 0 and the row will be before all the other rows.

If you want the rows that are not in the list to be at the end, use arsort instead and sort by DESC.

arsort($words);
...
$q->sortby('FIELD(id,' . $str_keys . ')','DESC');

[tulavu]

Thank you very much it works perfectly