CODEWITHSUNDEEP
cakephpcakephp-2.0cakephp-2.1
BadHorsie
BadHorsie

Reputation: 14544

Query to join latest entry from related model table (CakePHP)

I have two tables:

orders

id  |  timestamp
--------------------------
1   |  2013-05-01 12:56:33

order_prices

This table stores snapshots of the order price. When the order is placed, the first entry is made with the original price. After that, the price can be changed through the admin system, so when the price is changed, a new price gets logged.

id  |  order_id  |  price   |  timestamp
--------------------------------------------------
1   |  1         |  400.00  |  2013-05-01 12:56:33
1   |  1         |  450.00  |  2013-06-01 18:07:35
1   |  1         |  300.00  |  2013-07-01 17:42:16

Models

class Order extends AppModel {

    public $hasMany = array('OrderPrice');
}


class OrderPrice extends AppModel {

    public $belongsTo = array('Order');
}

So my question is how do I perform a find on the Order, while pulling in the 'current' price (i.e. the latest entry in the order_prices table for the order), or the original price, or the price at any historic point in time?

I would like to do this in the most hassle-free way for repeat usage. I don't know if this can be done with virtual fields, but that would be ideal because then I can just pull in the current order price when I need it.

My solution so far, using containable behavior:

$this->Order->find('first', array(
    'contain' => array(
        'OrderPrice' => array(
            'order' => 'OrderPrice.timestamp DESC',
            'limit' => 1
        )

));

However, this means I have to add that every time, which isn't ideal as I'll be getting the order price a lot. I could really do with a way of putting as much of this into the model as possible so I don't have to repeat it.

I tried adding a default order property for the OrderPrice model, but it doesn't seem to work on containable, and you can't add a default limit property.

class OrderPrice extends AppModel {

    // Doesn't work from containable
    public $order = 'ObeOrderPrice.timestamp DESC';

Upvotes: 1

Views: 669

Answers (2)

BadHorsie
BadHorsie

Reputation: 14544

Okay, I have what I think is the best solution, unless anyone has any other ideas.

I store the containable options for the OrderPrice as variables in the model, so that I don't have to rewrite them any time I want to join the current/original price onto the Order. I can use them from any model.

Then I have some custom find types to allow me to perform normal queries on the Order with any parameters I want, while automatically joining the custom/original price.

app/Model/OrderPrice.php

/**
 * Containable array options for finding current price
 *
 * @var array
 */
public $containCurrent = array(
    'fields' => 'OrderPrice.price',
    'order' => 'OrderPrice.timestamp DESC',
    'limit' => 1
);

/**
 * Containable array options for finding the original price
 *
 * @var array
 */
public $containOriginal = array(
    'fields' => 'OrderPrice.price',
    'order' => 'OrderPrice.timestamp',
    'limit' => 1
);

app/Model/Order.php

/**
 * Custom find type which automatically joins the current order price
 *
 * @param  string $state
 * @param  array $query
 * @param  array $results
 * @return array
 * @link http://book.cakephp.org/2.0/en/models/retrieving-your-data.html#creating-custom-find-types
 */
protected function _findJoinCurrentPrice($state, $query, $results = array()) {
    if ($state == 'before') {
        $query['contain']['OrderPrice'] = $this->OrderPrice->containCurrent;
        return $query;
    } else {
        $results = $this->_refinePriceFindResults($query, $results);
    }
    return $results;
}

/**
 * Custom find type which automatically joins the original order price
 *
 * @param  string $state
 * @param  array $query
 * @param  array $results
 * @return array
 * @link http://book.cakephp.org/2.0/en/models/retrieving-your-data.html#creating-custom-find-types
 */
protected function _findJoinOriginalPrice($state, $query, $results = array()) {
    if ($state == 'before') {
        $query['contain']['OrderPrice'] = $this->OrderPrice->containOriginal;
        return $query;
    } else {
        $results = $this->_refinePriceFindResults($query, $results);
    }
    return $results;
}

/**
 * Refine the results of a custom price find
 *
 * If the find returns only one order and/or one price, the data for both
 * models will be returned as element [0] of an otherwise empty array.
 * This method modifies the results array by removing the redundant wrapper
 * array, so that the model data is structured like a normal find('first').
 *
 * @param  array $query The query array from the custom find method
 * @param  array $results The results array from the custom find method
 * @return array The modified results
 */
protected function _refinePriceFindResults($query, $results) {
    if (empty($results)) {
        return false;
    }
    if (isset($query['conditions']['id']) || isset($query['conditions']['Order.id'])) {
        $results = array_shift($results);
    }
    if (isset($results['OrderPrice']['0'])) {
        if (count($results['OrderPrice']) == 1) {
            $results['OrderPrice'] = array_shift($results['OrderPrice']);
        }
    }   
    return $results;
}

Controller

// I can perform a normal find, using any number of complex parameters.

$this->Order->find('joinCurrentPrice', array(
    'fields' => array('Order.type_id'),
    'conditions' => array('Order.id' => $id),
    'contain' => array(
        'Customer' => array(
            'fields' => array('Customer.first_name', 'Customer.last_name')
        )
    )
));


// And the result is the same as the normal find, but the price model is
// automatically joined without any extra work.

array(
    'Order' => array(
        'type_id' => 2,
        'id' => '843654'
    ),
    'Customer' => array(
        'id' => 7348,
        'first_name' => 'Joe',
        'last_name' => 'Bloggs'
    ),
    'OrderPrice' => array(
        'price' => '549.00',
        'order_id' => '843654'
    )
)

I just need to figure out how to do this same thing for historic prices, so I can join a price from any time in the order's history, not just the first or last.

Upvotes: 0

Dave
Dave

Reputation: 29121

I think this is less of a specific issue with the way your doing it, and more of an issue with the location you're putting your code. (using Containable just like you are is ideal in my opinion)

"However, this means I have to add that every time, which isn't ideal as I'll be getting the order price a lot. I could really do with a way of putting as much of this into the model as possible so I don't have to repeat it."

This makes me think you're putting your find() in multiple places in the Controller(s)... to your point, you should have methods in your model that can be accessed from any controller at any time... something like this:

//OrdersController
public function view($id) {
    $order = $this->Order->getOrder($id);
    $this->set(compact('order'));
}

//Order model
public function getOrder($id) {
    return $this->Order->find('first', array(
        'contain' => array(
            'OrderPrice' => array(
                'order' => 'OrderPrice.timestamp DESC',
                'limit' => 1
            )
    ));
}

This will make it so you literally don't have to repeat your contain() more than once. You can also extend the method to allow for different parameters to be passed for limit, order, date range(s)...etc - but you still have the method on a single location in the model.

Side note: I often have two methods - in this case getOrder() and getOrders() - the first being very simple way to get a single order, and the second with all the parameters that allow me to pull orders in many different ways, directions, limits, joins...etc etc. I'm not sure if this is ideal, but it's suited me well.

Upvotes: 1

Related Questions