如何选择除默认字段之外的特定字段? [英] How to select a specific field additionally to a tables default fields?
问题描述
我希望使用JOIN从表格中选择数据,并在CakePHP中选择视图,如下所示:
$ this - > Annonces-> find('all')
- > where($ arrFiltres)
- > order($ arrOrder)
- > join b'table'=>'annonces_suivis',
'alias'=>'AnnoncesSuivis',
'conditions'=> [...],
]
并且希望能够选择第一个表中的所有字段和联合表像这样:
- > select(['Annonces。*','AnnoncesSuivis.id']);
但这会创建一个错误的SQL查询。
。*
不支持ORM Query,它会将此转换为
Annonces。* AS Annonces __ *
这是无效的SQL。它将使用较低级别的数据库查询( Connection :: newQuery()
),它不添加别名,但它不会返回实体,所以可能不是
请参阅 Cookbook>数据库访问& ORM>数据库基础> \Cake\Database\Connection :: newQuery()
传递表对象
从CakePHP 3.1开始,您可以将表对象传递给 Query :: select()
,这将导致表的所有字段
$ this-> Annonces
- > find 'all')
- > select(['AnnoncesSuivis.id'])
- > select($ this-> Annonces)
- > join b'table'=>'annonces_suivis',
'alias'=>'AnnoncesSuivis',
'conditions'=> [/ * ... * /],
] )
- >其中($ arrFiltres)
- > order($ arrOrder);
这样
AnnoncesSuivis.id
请参阅 book.cakephp.org/3.0/en/orm/query-builder.html#selecting-all-fields-from-a-tablerel =nofollow>食谱>数据库访问& ORM>查询构建器>从表中选择所有字段
从架构构建字段
这就是传递一个表对象会导致内部,它也支持在CakePHP< 3.1。
$ query = $ this-> Annonces-> find
$ fields = $ query-> aliasFields(
$ this-> Annonces-> schema() - > columns(),
$ this-> Annonces-> alias()
);
$ query
- > select(array_merge(['AnnoncesSuivis.id'],$ fields))
- > join([
'table' =>'annonces_suivis',
'alias'=>'AnnoncesSuivis',
'conditions'=> [/ * ... * /],
])$ b b - >其中($ arrFiltres)
- > order($ arrOrder);
这也适用于
字段
选项它可以传递到Table :: find()
,虽然你必须在这种情况下使用一个单独的查询对象,例如$ fields = $ this-> Annonces-> query() - > aliasFields(
$ this-> Annonces-& ) - > columns(),
$ this-> Annonces-> alias()
);
$ this-> Annonces-> find('all',[
'fields'=> array_merge(['AnnoncesSuivis.id'],$ fields)
// ...
]);
使用
Query :: autoFields()
在ealier CakePHP版本中,您还可以使用
Query :: autoFields()
true
将自动包含主表字段和可能的安全包。
请参阅食谱>数据库访问& ORM>检索数据&结果集>将条件传递给包含
自动选择所有字段是默认行为,直到通过
查询:: select()
,在这种情况下,您必须显式启用Query :: autoFields()
。$ this-> Annonces
- > find('all')
- > select(['AnnoncesSuivis.id'])
- > autoFields(true)
- > alias'=>'AnnoncesSuivis',
'conditions'=> [/ * ... * /],
])
- > where($ arrFiltres)
- > order($ arrOrder);
这应该给你想要的查询,但是如上所述,这将只适用于主表和包含,如果您想包含手动连接表的所有字段,则必须逐个指定它们。
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.
解决方案
.*
isn't supported by the ORM Query, it will convert this toAnnonces.* 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 ofAnnonces
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 toTable::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 totrue
, 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 enableQuery::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.
这篇关于如何选择除默认字段之外的特定字段?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!