Cake PHP连接查询作为关联数组返回 [英] Cake PHP joined query returned as associative array

问题描述

好的，这里是我的表/模型结构：模型是适当地关联的。

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

我试图查询给定客户ID的持有金额总和

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')
);

当我通过执行

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

我得到这样的结果：

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
    ...
)

所以我试试：

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

但我遇到错误：

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?

编辑：我已经使用Cake的

I have acheived my result using Cake's hash class but was wondering if a direct query is a superior and more efficient 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;

推荐答案

一般评论

CakePHP的ORM非常适合查询查询（包括 group by sort ...）。当涉及到更高级的东西，你需要处理在PHP检索的数据或使 plain SQL Query 。

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.

模型:: find（'all'） 或 Model :: find（'first'） 将总是返回一个关联数组。其他find方法将返回不同类型的数组，但是你不能使用开箱即用的模型函数来获取结果。

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.

要在 SUM（）， AVG（）或 COUNT（），您可以在这里阅读关于虚拟字段 SQL查询

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

最简单的方法是请使用 Hash

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

使用 Hash :: combine 're寻找像这样：

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');

这篇关于Cake PHP连接查询作为关联数组返回的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！