Mysql rand() function in Zend framework 2

Looking at the API it appears that the order function accepts a string or array of parameters order(string | array $order). My first thought was to use a key/val array. However, as I look at the actual code of the Db\Sql\Select, the string or array that you are passing gets quoted (see here). Assuming that your Db platform is Mysql, this is the function that quotes the fields (see here). It appears to iterate through each of the fields, and add these quotes, rendering your rand() function a useless string.

Getting to the point, the solution is up to you, but it does not appear that you can do it the way you want with this current version of ZF2.

You will need to extend the Db\Sql\Select class, OR extend the Db\Adapter\Platform\Mysql class, OR change the code in these classes, OR execute your query as a full select statement, OR change up your logic.

By changing up your logic I mean, for example, if your table has an Integer primary key, then first select the MAX(id) from the table. Then, choose your random numbers in PHP prior to executing your query like $ids[] = rand(1, $max) for as many results as you need back. Then your sql logic would look like SELECT * FROM table WHERE id IN(453, 234, 987, 12, 999). Same result, just different logic. Mysql's rand() is very "expensive" anyways.

Hope this helps!

Here you can use \Zend\Db\Sql\Expression. Example function from ModelTable:

public function getRandUsers($limit = 1){
        $limit = (int)$limit;

        $resultSet = $this->tableGateway->select(function(Select $select) use ($limit){

            $select->where(array('role' => array(6,7), 'status' => 1));

            $rand = new \Zend\Db\Sql\Expression('RAND()');

            $select->order($rand);
            $select->limit($limit);            

            //echo $select->getSqlString();
        });
        return $resultSet;

    }