CakePHP: Join single item from related table

[smerny]

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

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 then only the one with the smallest rank (if there are multiple with the same rank it doesn't really matter which one is pulled).

 

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.

 

I've learned I can 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... but perhaps I am wrong on feeling that I should avoid this?

 

I tried doing this, but I get duplicate data... I'm assuming the order and limit don't work here (or atleast not how I assumed it would). It is also still joining Project which I would like to avoid if possible as that data is not necessary in the Items index:

    $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.*','ItemPic.*')
    );
    $paginated = $this->Paginator->paginate();

Resulting SQL: (still joining Project, not restricting ItemPic join)

SELECT `Item`.*, `ItemPic`.*, `Item`.`id` FROM `abc`.`items` AS `Item` 
LEFT JOIN `abc`.`item_pics` AS `ItemPic` 
ON(`ItemPic`.`item_id` = `Item`.`id`) 
LEFT JOIN `abc`.`projects` AS `Project` 
ON (`Project`.`item_id` = `Item`.`id`)
WHERE `Item`.`type` IN (0, 2) LIMIT 20

Resulting Data: (getting the same Item with all its associated ItemPics)

array(
        (int) 0 => array(
                'Item' => array(
                        'id' => '3',
                        ...
                ),
                'ItemPic' => array(
                        'id' => '1',
                        'item_id' => '3',
                        ...
                )
        ),
        (int) 1 => array(
                'Item' => array(
                        'id' => '3',
                        ...
                ),
                'ItemPic' => array(
                        'id' => '3',
                        'item_id' => '3',
                        ...
                )
        ),
        (int) 2 => array(
                'Item' => array(
                        'id' => '3',
                        ...
                ),
                'ItemPic' => array(
                        'id' => '4',
                        'item_id' => '3',
                        ...
                )
        ),
        ...
)