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.

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

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.