CakePHP and Tricky Pagination

    I have the most painful place in CakePHP - pagination. Especially when it comes to tricks. Banal -> find ('all') always (well, almost always) works fine and wonderful, but pagination ...
    Given:

    1. There is a tablet of real estate (Immovable) with the indicated price (Immovable.price) and currency type (Immovable.currency_id)
    2. There is a currency label (Currency) with the fields: id, name, coeff (coefficient)

    Accordingly, in the currency table there are coefficients USD coeff = 1, for the currency Euro coeff = 1.34.
    Task:

    Sort issuance (paginate) by price, including currency. Those. a house for 100,000 euros is more expensive than a house for 110,000 dollars (at a rate of 1 to 1.34)
    Decision

    $this->paginate = array('order' => array('Count' => 'ASC'),
    'fields' => array('*','((`Immovable`.`price`*`Currency`.`coeff`)) as Count'));


    Yes, it’s in double brackets. Then the request will be correct with the set LEFT JOIN to Currency and the correct sorting by calculated field.

    Yes, I tested with MySQL, because I couldn’t work with other databases. I hope someone helps this thread :)

    Also popular now: