cakephp3 find data with selected columns

Question

Hi I'm trying to find data in Cakephp3

What I'm doing is I'd like to find data with selected columns which are product name, product code, and small image url.

$contain = [
    'Image' => [
            'ImageFile'
        ]
    ];

$products = $this->Product->find()
        ->contain($contain)
        ->select(['Product.name', 'Product.product_code', 'Product.Image.ImageFile.small_image_url'])
        ->where(['Product.name Like' => '%'.$keyword.'%']);

However, I got an error

Error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'Product.Image.ImageFile.small_image_url' in 'field list'

I have no idea how to fix this.

Thanks in advance!

Answer 1

Firstly,Can you please tell us what is structure of DB?

Secondly, There is a way to debug query in cake3 by debug kit. Please let us know what query is generating for:

$products = $this->Product->find()
        ->contain($contain)
        ->select(['Product.name', 'Product.product_code', 'Product.Image.ImageFile.small_image_url'])
        ->where(['Product.name Like' => '%'.$keyword.'%']);

Please Try this query and let me know what is output in debug kit:

$contain=[
    ['Image' => function($q) {
        return $q->autoFields(true);
    },
    'ImageFile'=> function($q) {
        return $q->autoFields(true);
    }
    ],
];
$products = $this->Product->find()
                 ->contain($contain)
                 ->autoFields(true)          
                 ->where(['Product.name Like' => '%'.$keyword.'%']);