phpphalcon

Preserve Order of IN in ORM Order


I'm trying to do a query where I preserve the order of the ids in a IN statement. I can't seem to do it with either the Model Manage Query Builder or the standard ORM 'order' array parameter. Am I missing something? I keep getting:

UNEXPECTED TOKEN IDENTIFIER(, NEAR TO 'id`enter code here`,17743,16688,16650

Here's my model manager:

$query = $this->modelsManager->createQuery('SELECT * FROM Projects WHERE id IN ('.implode(',', array_keys($finalIterations)).')
                    ORDER BY FIELD(id,'.implode(',', array_keys($finalIterations)).'');

It's pretty obvious PhQL doesn't like the FIELD key word. Is there a way for me to do what I'm trying to do with PhQL? It seems I will not be able to do what I need to.


Solution

  • Unfortunately as previously said, this is missing a feature in Phalcon.

    Have a look at this function, I've put it into my ModelBase abstract class which is parent class of all my models. It uses PhQL variable binding, so it's safe for handling direct user input.

    You could have reimplemented custom \Phalcon\Mvc\Model\Criteria but this solution seems to be easier to work with, at least for me.


    ModelBase abstract

    public function appendCustomOrder( \Phalcon\Mvc\Model\CriteriaInterface &$criteria, $orderField, array &$orderValues = [] ) {
        if(!empty($orderValues)) {
            $queryKeys = $bindParams = [];
            foreach($orderValues as $key => $id) {
                $queryKey = 'pho'.$key;
                $queryKeys[] = ':'.$queryKey.':';
                $bindParams[$queryKey] = $id;
            }
    
            // TODO: add support for multiple orderBy fields
            $criteria->orderBy('FIELD('.$orderField.','.implode(',',$queryKeys).')');
            // there's no 'addBind' function, need to merge old parameters with new ones
            $criteria->bind( array_merge( (array) @$criteria->getParams()['bind'], $bindParams ) );
        }
    }
    

    Controller usage

    $projectIDs = [17743, 16688, 16650];
    $projectsModel = new Projects();
    
    $criteria = $projectsModel->query->inWhere( 'id', $projectIDs );
    $projectsModel->appendCustomOrder( $criteria, 'id', $projectIDs );
    
    $projectsData = $criteria->execute();
    

    This will generate valid PhQL syntax similar to this one:

    SELECT `projects`.`id` AS `id`, `projects`.`title` AS `title` 
    FROM `projects` 
    WHERE `projects`.`id` IN (:phi0, :phi1, :phi2) 
    ORDER BY FIELD(`projects`.`id`, :pho0, :pho1, :pho2)