根据最小字段从相关表中加入单个项目 [英] Join single item from related table based on min of field

问题描述

我有一个 Item 模型，它具有以下关联：

I have an Item model which has the following associations:

public $hasOne = array(
    'Project' => array(
        'className' => 'Project',
        'foreignKey' => 'item_id'
    )
);
public $hasMany = array(
    'ItemPic' => array(
        'className' => 'ItemPic',
        'foreignKey' => 'item_id',
        'dependent' => false
    )
);

我想要项目的不同视图的自定义数据。看起来CakePHP自动包括 Project 数据（也许是因为它是 hasOne ？），不包括 ItemPic 数据。在 index 我真的不想要 Project 数据...但是，我想要< c $ c> ItemPic 数据。对于每个项记录拉，我想要一个单一的 ItemPic 记录加入它。 ItemPic 应该基本上是 ItemPic.item_id = Item.id 和 ORDER BY ItemPic.rank LIMIT 1

I am wanting custom data for different views of Item. It seems like CakePHP automatically includes Project data (maybe because it is hasOne?) and does not include the ItemPic data. In the index I really don't even want the Project data... however, I do want the ItemPic data. For each Item record pulled, I want a single ItemPic record joined to it. This ItemPic should be basically ItemPic.item_id = Item.id and ORDER BY ItemPic.rank LIMIT 1.

这样做的目的基本上是为了在索引中可以显示项目列表和与每个项目关联的图片。我想要所有的图像以及视图中的 Project 数据为单个

The purpose of this is basically so that in the index I can show a list of Items and a picture associated with each item. I would like all of the images along with the Project data in the view for a single Item, but not in the list/index.

我被告知我可以使用可容纳 like this：

I was told I could use containable like this:

// In the model
public $actsAs = array('Containable');

// In the controller
$this->paginate = array(
    'conditions' => $conditions,
    'contain' => array(
        'ItemPic' => array(
            'fields' => array('file_name'),
            'order' => 'rank',
            'limit' => 1
        )
    )
);

以上实际上是我的工作原理...但是，我也被告知，一个额外的查询，为每一个项 ...我觉得应该避免。

The above actually works how I want... however, I was also told that doing this would cause an extra query to be ran for every single Item... which I feel I should avoid.

这样做，但我得到重复的数据，它不附加任何 ItemPic 数据：

I tried doing this, but I get duplicate data and it doesn't attach any ItemPic data:

    $this->paginate = array(
            'conditions' => $conditions,
            'joins' =>  array(
                    array(
                            'table' => 'item_pics',
                            'alias' => 'ItemPic',
                            'type' => 'LEFT',
                            'conditions' => array(
                                    'ItemPic.item_id = Item.id'
                            ),
                            'order' => 'rank ASC',
                            'limit' => 1
                    )

            )
    );
    $paginated = $this->Paginator->paginate();

推荐答案

您可以试试：

$this->paginate = array(
        'conditions' => $conditions,
        'joins' =>  array(
                array(
                        'table' => 'item_pics',
                        'alias' => 'ItemPic',
                        'type' => 'LEFT',
                        'conditions' => array(
                                'ItemPic.item_id = Item.id'
                        ),
                        'order' => 'rank ASC',
                        'limit' => 1
                )

        ),
    'fields' => array('Item.*','Project.*','ItemPic.*') 
    );

在fileds部分，您可能会或可能不会根据您的要求分配项目 。

In the fileds section you may or may not assign "Item" , "Project" according to your requirment.

感谢

这篇关于根据最小字段从相关表中加入单个项目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！