CODEWITHSUNDEEP
cakephpcakephp-1.3cakephp-model
RC.
RC.

Reputation: 28197

Virtual Fields as aliases to other Model field - SQL: Unknown Column Error

I'm new to an existing Cake project where we are trying to use a virtualField within a model to alias another model field. In Context:

class Product extends AppModel {

    var $name = 'Product';
    var $hasOne = array('ProductPrice');

    var $virtualFields = array(
        'price' => 'ProductPrice.current_price'
    );

    // Typical fields in the database for Product.  id, name, etc.
}

class ProductPrice extends AppModel {
    var $name = 'ProductPrice';
    var $belongsTo = array('Product');

    //  Fields are product_id, current_price
}

The ProductPrice model is for a view in the database that contains different price tiers with a current_price column allowing retrieval of the current price of the product. When accessing the Product model via something like:

$this->Product->find('all' ....);

I don't have any problems with getting the price field. The problem is if the query to Product is done indirectly through something like 

$this->Inventory->find('all');

We get:
SQL Error: 1054: Unknown column 'ProductPrice.current_price' in 'field list' [CORE/cake/libs/model/datasources/dbo_source.php, line 681]

I know that the issue is that the SQL generated by the Inventory query does not attempt to join the ProductPrice view. I assumed that this would happen automagically via the Product model as it knows it "hasOne" ProductPrice.

I've tried setting "recursive" on the Inventory model to 2,1, etc. with no success.

What am I missing?

Upvotes: 0

Views: 3359

Answers (1)

Dave
Dave

Reputation: 29121

TLDR:

You cannot use fields from a different model in a VirtualField.

Other options:

If you're doing a query like:

$this->Inventory->find('all');

You can use something like CakePHP's Containable behavior to make sure you're getting the data you want:

//controller code
$inv = $this->Inventory->getInventory();

//model code
class Inventory extends AppModel {

    public $actsAs = array('Containable');

    public function getInventory() {
        return $this->find('all', array(
            'contain' => array(
                'Product' => array(
                    'ProductPrice'
                )
            )
        ));
    }
}

Using containable like in the above code example should return the data in a format something like this:

[0] => Array
    (
        [Inventory] => Array
            (
                [id] => 12345
            )
        [Product] => Array
            (
                [0] => Array
                    (
                        [id] => 54321
                        [title] => product A
                    )
                [ProductPrice] => Array
                (
                    [id] => 6789
                    [current_price] => 24.99
                )
            )
//...

When you get the data like that, it should be easy to access the Product's current price.

You could also just do it in the controller, but it's better practice to keep your queries in the model to stay within the "Fat Model, Skinny Controller" mantra. If you really want to keep it in your controller, you can just do this:

$inv = $this->find('all', array(
    'contain' => array(
        'Product' => array(
            'ProductPrice'
        )
    )
);

(BUT - you still have to specify that a model $actsAs Containable (per first code example).

Upvotes: 3

Related Questions