[FIXED] CakePHP - Creating a virtual field with a complex MySQL query

Issue

I'm trying to create a Virtual Field on the fly, with the following Model associations:

Retailer hasMany Invoice
Invoice hasMany Invoiceitem

Each invoice has a date (invoice_date) and price (price_paid).

I'm retrieving the Retailers, subject to various conditions, and for each Retailer I want to retrieve the amount spent in the past month. I've decided to start slightly less ambitiously, and remove the restriction on the amount being in the past month... instead, I'll just do it over all time for now!

I have the following definition of the virtual field:

$this->Retailer->virtualFields['totalamount'] =
          'SELECT SUM(price_paid) AS totalamount FROM invoices i JOIN
           (SELECT retailer_id, MAX(invoice_date) AS maxdate FROM invoices 
           GROUP BY retailer_id) sm
           ON i.retailer_id = sm.retailer_id GROUP BY i.retailer_id';

However, when I retrieve the results, I get the following error:

SQLSTATE[21000]: Cardinality violation: 1242 Subquery returns more than 1 row

I really only know the very basics of MySQL and virtual fields.

Solution

You need to make sure the code (i.e., your subquery) that defines a virtual field returns a single value. Your code (as the error message says) can return more than 1 row. You also need to correlate the subquery to the record being returned. In your case, I believe that means to use the id column of the Retailer model.

Try changing your virtual field definition to:

(SELECT SUM(i.price_paid) 
 FROM invoice AS i
 WHERE YEAR(i.invoice_date) = YEAR(CURRENT_DATE() - INTERVAL 1 MONTH)
  AND MONTH(i.invoice_date) = MONTH(CURRENT_DATE() - INTERVAL 1 MONTH)
  AND i.retailer_id = Retailer.id)

Answered By - AgRizzo