缺少表“Grupo"的 FROM 子句条目蛋糕 [英] missing FROM-clause entry for table "Grupo" cakephp

问题描述

我的代码有问题，我想生成一个用户列表，但这有一个组，只需要一组用户.错误说:

hi i have aproblem in my code, I want generate a list of user but this have a group and need just a group of user. the error say:

错误:SQLSTATE[42P01]:未定义的表:7 错误:缺少表Grupo"的 FROM 子句条目

Error: SQLSTATE[42P01]: Undefined table: 7 ERROR: missing FROM-clause entry for table "Grupo"

这是我的代码:

    public function add()
{
    $this->loadModel('SoyaProveedor');
    $this->loadModel('Soya');

    $this->set('oleaginosas', $this->Soya->find('list', array(
        'fields'=> array('id','username'),
        'conditions' => array('Grupo.categoria' => 'Soya' , 'Grupo.subcategoria' => 'Productor de Oleaginosas')
        )));

    if ($this->request->is('post')) {
    $this->request->data['SoyaProveedor']['nombre'] = strtoupper($this->request->data['SoyaProveedor']['nombre']);
    $this->request->data['SoyaProveedor']['codigo'] = strtoupper($this->request->data['SoyaProveedor']['codigo']);
    if ($this->SoyaProveedor->save($this->request->data)) {
        $this->Session->setFlash(__('La Información fue Guardada.'));
        return $this->redirect(array('action' => 'index'));
        }
    }
}

蛋糕的sql查询生成它:

the sql query of the cake generate it:

SQL 查询:SELECT "Soya"."id" AS "Soya__id", "Soya"."username" AS"Soya__username" FROM "public"."users" AS "Soya" WHERE"Grupo"."categoria" = '大豆' AND "Grupo"."subcategoria" = '生产商de Oleaginosas'

SQL Query: SELECT "Soya"."id" AS "Soya__id", "Soya"."username" AS "Soya__username" FROM "public"."users" AS "Soya" WHERE "Grupo"."categoria" = 'Soya' AND "Grupo"."subcategoria" = 'Productor de Oleaginosas'

推荐答案

您需要在查询中加入 grupos 表，您在问题中的查询没有加入.有许多简单的解决方案.

You need the grupos table to be joined in the query, your query in the question has no joins. There are a number of simple solutions.

递归是对执行什么联接和查询的非常粗略的控制，默认情况下 find('list') 的递归值为 -1.

Recursive is a very coarse control of what joins and queries are executed, by default find('list') has a recursive value of -1.

-1 表示没有连接，这就是结果查询中没有连接的原因.将其设置为 0 值会为所有 hasOne 和belongsTo 关联添加到主查询的连接.

-1 means no joins, which is why there is no join in the resultant query. Setting it to a value of 0 adds a join to the main query for all hasOne and belongsTo associations.

小心使用/依赖递归，因为它很容易生成带有您不需要的连接的查询 - 和/或触发相关数据的许多后续查询(如果设置为大于 0 的值).

Be wary of using/relying on recursive as it's very easy to generate queries with joins you don't need - and/or triggering many subsequent queries for related data (if set to a value larger than 0).

但是这个 find 调用:

However this find call:

$data = $this->Soya->find('list', array(
    'fields'=> array('Soya.id','Soya.username'),
    'recursive' => 0, // added
    'conditions' => array(
        'Grupo.categoria' => 'Soya' , 
        'Grupo.subcategoria' => 'Productor de Oleaginosas'
    )
));

应该导致这个查询(如果 Soya 模型与 Grupo 有一个关联关联):

Should result in this query (If the Soya model has a belongsTo association to Grupo):

SELECT
    "Soya"."id" AS "Soya__id",
    "Soya"."username" AS "Soya__username"
FROM
    "public"."users" as "Soya"
LEFT JOIN
    "public"."Grupos" as "Grupo" on ("Soya"."grupo_id" = "Grupo"."id")
...
Possibly more joins
...
WHERE
   "Grupo"."categoria" = 'Soya' 
    AND 
    "Grupo"."subcategoria" = 'Productor de Oleaginosas'

或者使用可包含的

可包含行为允许更好地控制什么查询被执行.鉴于问题中的信息使用它意味着:

Or Use containable

The containable behavior allows better control of what queries are executed. Given the info in the question to use it that means:

<?php

class Soya extends AppModel {
    // Assumed from information in the question
    public $useTable = 'users';

    public $belongsTo = array('Grupo');

    // added
    public $actsAs = array('Containable');

}

将允许您在控制器中执行以下操作:

Will permit you to do the following in your controller:

$data = $this->Soya->find('list', array(
    'fields'=> array('Soya.id','Soya.username'),
    'contain' => array('Grupo'), // added
    'conditions' => array(
        'Grupo.categoria' => 'Soya' , 
        'Grupo.subcategoria' => 'Productor de Oleaginosas'
    )
));

这将生成以下查询(正好是一个连接):

Which will generate the following query (exactly one join):

SELECT
    "Soya"."id" AS "Soya__id",
    "Soya"."username" AS "Soya__username"
FROM
    "public"."users" as "Soya"
LEFT JOIN
    "public"."Grupos" as "Grupo" on ("Soya"."grupo_id" = "Grupo"."id")
WHERE
   "Grupo"."categoria" = 'Soya' 
    AND 
    "Grupo"."subcategoria" = 'Productor de Oleaginosas'

这篇关于缺少表“Grupo"的 FROM 子句条目蛋糕的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！