如何在表格默认字段之外选择特定字段? [英] How to select a specific field additionally to a tables default fields?

问题描述

我希望在 CakePHP 中使用 JOIN 从表和视图中选择数据，如下所示:

$this->Annonces->find('all')->where($arrFiltres)-> 订单($arrOrder)->加入(['表' =>'annons_suivis','别名' =>'AnnoncesSuvis','条件' =>[...]，]);

并且希望能够像这样从第一个表和联合表的部分中选择所有字段:

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

但这会产生错误的 SQL 查询.

解决方案

.* is not supported by the ORM Query, it will convert this to

Annonces.* AS Annonces__*

这是无效的 SQL.它适用于不添加别名的较低级别的数据库查询 (Connection::newQuery())，但它不会返回实体，因此这可能不是您想要的.>

参见食谱> 数据库访问 &ORM > 数据库基础 > CakeDatabaseConnection::newQuery()

传递一个表格对象

从 CakePHP 3.1 开始，您可以将表对象传递给 Query::select()，这将导致选择表的所有字段.

$this->Annonces-> 查找('全部')-> 选择(['AnnoncesSuvis.id'])->选择($this->Annonces)->加入(['表' =>'annons_suivis','别名' =>'AnnoncesSuvis','条件' =>[/* ... */],])->where($arrFiltres)-> 订单($arrOrder);

这样 AnnoncesSuivis.id 字段和 Annonces 的所有字段都会被选中.

见Cookbook > 数据库访问 &ORM > 查询生成器 > 从表中选择所有字段

从架构构建字段

这也是内部传递表对象的原因，CakePHP 也支持这种情况 <3.1.

$query = $this->Annonces->find('all');$fields = $query->aliasFields($this->Annonces->schema()->columns(),$this->Annonces->alias());$查询->选择(array_merge(['AnnoncesSuvis.id'], $fields))->加入(['表' =>'annons_suivis','别名' =>'AnnoncesSuvis','条件' =>[/* ... */],])->where($arrFiltres)-> 订单($arrOrder)；

这也适用于可以传递给 Table::find() 的 fields 选项，尽管您必须在其中使用单独的查询对象案例，如

$fields = $this->Annonces->query()->aliasFields($this->Annonces->schema()->columns(),$this->Annonces->alias());$this->Annonces->find('all', ['字段' =>array_merge(['AnnoncesSuvis.id'], $fields)//...]);

使用Query::autoFields()

在更早的 CakePHP 版本中，您还可以使用 Query::autoFields()，当设置为 true 时，将自动包含主要的字段表和可能的容器.

见Cookbook > 数据库访问 &ORM > 检索数据 &结果集 > 传递条件以包含

自动选择所有字段是默认行为，直到您通过 Query::select() 设置字段，在这种情况下，您必须显式启用 Query::autoFields().

$this->Annonces-> 查找('全部')-> 选择(['AnnoncesSuvis.id'])-> 自动字段(真)->加入(['表' =>'annons_suivis','别名' =>'AnnoncesSuvis','条件' =>[/* ... */],])->where($arrFiltres)-> 订单($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 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 > CakeDatabaseConnection::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.

这篇关于如何在表格默认字段之外选择特定字段?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！