2

OK, here is my table/model structure: Models are associated approprately.

table structure

I am trying to query the sum of holding value for a given client id, with the date for a given sum of value as the array key.

I having consulted the docs I have produced the following parameters for my query in the Client model:

$findParameters = array(
    'conditions' => array(
        'Account.client_id' => $clientId,
        'Holding.holding_date = LAST_DAY(Holding.holding_date)', //gets month-end dates only
        'MONTH(Holding.holding_date)' => array(3,6,9,12) //Use for quarter-end dates
        ),
    'fields' => array(
        'Holding.holding_date',
        'SUM(Holding.value) AS portfolio_value'
        ),
    'group' => array('Holding.holding_date')
);

When I run the query by doing

$holdings = $this->Account->Holding->find( 'all', $findParameters );

I get a result like this:

Array
(
    [0] => Array
        (
            [Holding] => Array
                (
                    [holding_date] => 2009-12-31
                )

            [0] => Array
                (
                    [portfolio_value] => 273239.07
                )

        )

    [1] => Array
        (
            [Holding] => Array
                (
                    [holding_date] => 2010-03-31
                )

            [0] => Array
                (
                    [portfolio_value] => 276625.28
                )

        )
...

Which is great but I want the result in an array like this:

Array (
    [2009-12-31] => 273239.07
    [2010-03-31] => 276625.28
    ...
)

So I try doing:

$holdings = $this->Account->Holding->find( 'list', $findParameters )

But I get the error: 

Error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'Account.client_id' in 'where clause'

The query looks as if it is no longer performing the joins on the tables. Any idea why, given it works fine if I use all instead of list? And how to get my desired result?

EDIT: I have acheived my result using Cake's hash class but was wondering if a direct query is a superior and more efficient method.

My method:

    $holdings = $this->Account->Holding->find( 'all', $findParameters );

    $values = Hash::extract($result, '{n}.0.portfolio_value');
    $dates = Hash::extract($result, '{n}.Holding.holding_date');

    $result = array_combine($dates, $values);
    return $result;
Improve this question
1
  • ha, just finished my answer with the Hash tip too. Good you found it yourself ;) -edit: actually, I do it in a different way, but Hash::combine is doing what you do : 2 extract and one combine. Commented Sep 13, 2013 at 13:52

2 Answers 2

Reset to default
2

General comment

CakePHP's ORM is great for easy find queries (including group by sort...). When it comes to more advanced stuff, you'll need either to process the data retrieved in PHP or make plain SQL Query.

About Associative Arrays

Model::find('all') or Model::find('first') will always return an associative array. The other find methods will return different kind of arrays, but you won't be able to get the result you're looking for using out-of-the box Model functions.

About fields not being added to the correct subarray

To get your field properly displayed together when you have SUM(), AVG() or COUNT() you can read here about virtual fields in SQL Queries

How to extract/format arrays in CakePHP

The easiest way would be to use the Hash class to extract and format the data the way you want.

Using Hash::combine you could achieve what you're looking for like this :

$result = Hash::combine($holdings, '{n}.Holding.holding_date', '{n}.0.portfolio_value');
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Unfortunately I get an SQLSTATE error when I use AS Holding.portfolio_value
Although your method without the first step does work, and a 1-liner is better than a 3-liner!
@harryg I've added a link to the doc where you can see the proper way to perform what I was trying with the first step.
Helpful but can't be used on conditions. Your method works well without though and I trust it is quite efficient!
0

Please try 

$findParameters = array(
    'conditions' => array(
        'Account.client_id' => $clientId,
        'Holding.holding_date = LAST_DAY(Holding.holding_date)', //gets month-end dates only
        'MONTH(Holding.holding_date)' => array(3,6,9,12) //Use for quarter-end dates
        ),
    'fields' => array(
        'Holding.holding_date',
        'SUM(Holding.value) AS portfolio_value'
        ),
    'group' => array('Holding.holding_date'),
    'contain' => array('Account),
);
Improve this answer

1 Comment

Unfortunately that makes no difference

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.