How to select a specific field additionally to a tables default fields?

Question

I an looking to use a JOIN to select data from a table and a view in CakePHP like so :

$this->Annonces->find('all')
        ->where($arrFiltres)
        ->order($arrOrder)
        ->join([
            'table' => 'annonces_suivis',
            'alias' => 'AnnoncesSuivis',
            'conditions' => [...],      
        ]);

And would like to be able to select all the fields from the first table and som of the jointed table like so :

->select(['Annonces.*', 'AnnoncesSuivis.id']);

But this creates a faulty SQL query.

Iminourea

Answer 1

.* isn't supported by the ORM Query, it will convert this to

Annonces.* AS Annonces__*

which is invalid SQL. It would work with the lower level Database Query (Connection::newQuery()), which doesn't add aliases, however it won't return entities, so that's probably not what you want.

See Cookbook > Database Access & ORM > Database Basics > \Cake\Database\Connection::newQuery()

CakePHP 4+: Use selectAlso()

You can use selectAlso() to select all fields on a table and also select some additional fields:

$query = $articlesTable->find();
$query->selectAlso(['count' => $query->func()->count('*')]);

See Cookbook > Database Access & ORM > Query Builder > Selecting Specific Fields

Pass a table object

As of CakePHP 3.1 you can pass table objects to Query::select(), which will cause all the fields of the table to be selected.

$this->Annonces
    ->find('all')
    ->select(['AnnoncesSuivis.id'])
    ->select($this->Annonces)
    ->join([
        'table' => 'annonces_suivis',
        'alias' => 'AnnoncesSuivis',
        'conditions' => [ /* ... */ ],     
    ])
    ->where($arrFiltres)
    ->order($arrOrder);

That way the AnnoncesSuivis.id field, and all fields of Annonces will be selected.

See Cookbook > Database Access & ORM > Query Builder > Selecting All Fields From a Table

Build the fields from the schema

That's what passing a table object will cause internally too, and it's also supported in CakePHP < 3.1.

$query = $this->Annonces->find('all');

$fields = $query->aliasFields(
    $this->Annonces->schema()->columns(),
    $this->Annonces->alias()
);

$query
    ->select(array_merge(['AnnoncesSuivis.id'], $fields))
    ->join([
        'table' => 'annonces_suivis',
        'alias' => 'AnnoncesSuivis',
        'conditions' => [ /* ... */ ],     
    ])
    ->where($arrFiltres)
    ->order($arrOrder);

This would also work for the fields option that can be passed to Table::find(), though you'd have to use a separate query object in that case, like

$fields = $this->Annonces->query()->aliasFields(
    $this->Annonces->schema()->columns(),
    $this->Annonces->alias()
);

$this->Annonces->find('all', [
    'fields' => array_merge(['AnnoncesSuivis.id'], $fields)
    // ...
]);

Use Query::autoFields()

In ealier CakePHP version, you could also make use of Query::autoFields(), which, when set to true, will automatically include the fields of the main table and possible containments.

See Cookbook > Database Access & ORM > Retrieving Data & Results Sets > Passing Conditions to Contain

Auto selecting all fields is the default behavior until you set fields via Query::select(), in that case you'll have to explicitly enable Query::autoFields().

$this->Annonces
    ->find('all')
    ->select(['AnnoncesSuivis.id'])
    ->autoFields(true)
    ->join([
        'table' => 'annonces_suivis',
        'alias' => 'AnnoncesSuivis',
        'conditions' => [ /* ... */ ],     
    ])
    ->where($arrFiltres)
    ->order($arrOrder);

This should give you the desired query, however as mentioned this will only work for the main table and containments, if you'd wanted to include all fields of a manually joined table, then you'd have to specify them one by one.

Tocopherol

Thank you very much. I will put that in our production version :) – Iminourea

in my case I have to join so many tables,and also need almost all fields from all of them.Specifying all doesn't seen to be a good solution. Is there a way to use * or something to select all at once ? – Velvetvelveteen

@Velvetvelveteen As I said, here is currently no way to use * with the ORM query builder. If you need almost all, then just let the ORM select all by not specifying any fields manually at all. Also see github.com/cakephp/cakephp/issues/6904 – Tocopherol

when I use ->select() without any argument passed in it, cakephp3 only returning base tables columns and not all the columns of all joined tables. – Velvetvelveteen

@Velvetvelveteen That's the behavior I've described. You will have to use containments instead of manual joins. – Tocopherol

Answer 2

You also can create virtual field in Entity:

namespace App\Model\Entity;
use Cake\ORM\Entity;

class User extends Entity { 
    protected function _getFullName() {
       return $this->_properties['first_name'] . ' ' . $this->_properties['last_name']; 
    } 
}

echo $entity->full_name;

Ralfston