How to select a specific field additionally to a tables default fields?
Solution 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.
Solution 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;
Mathieu de Lorimier
Web programmer for about 10 years Expert in MySql and PHp
Updated on June 07, 2022Comments
-
Mathieu de Lorimier almost 2 years
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.
-
Mathieu de Lorimier about 9 yearsThank you very much. I will put that in our production version :)
-
Dashrath almost 9 yearsin 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 ?
-
ndm almost 9 years@Dashrath 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 -
Dashrath almost 9 yearswhen I use ->select() without any argument passed in it, cakephp3 only returning base tables columns and not all the columns of all joined tables.
-
ndm almost 9 years@Dashrath That's the behavior I've described. You will have to use containments instead of manual joins.