未找到列：1054“字段列表”中的未知列“PlantingBlock.id” [英] Column not found: 1054 Unknown column 'PlantingBlock.id' in 'field list'

问题描述

我需要使用cakephp和mysql传递一个字符串到存储过程。

I need to pass a string to a stored procedure using cakephp and mysql.

这是我传递给存储过程的字符串，

Here is the string i'm passing to stored procedure,

PlantingBlock.id IN (13,10)

下面是我的存储过程，

SET whereClause = " AND 1 = 1 ";
IF(in_clause IS NULL OR in_clause = '') THEN
    SET whereClause = CONCAT(whereClause, ' AND ',in_clause);
END IF;

SET statement = 'SELECT PlantingBlock.* FROM (
    SELECT PB.*, 
    B.block,
    V.variety,
    ST.size_type,
    PLA.date AS plant_date,
    PLA.week_no,
    C.crop,
    C.id AS crop_id
    FROM lf_planting_blocks AS PB,
         lf_blocks AS B,
         lf_properties AS P,
         lf_property_types AS PT,  
         lf_size_types AS ST,
         lf_varieties AS V,
         lf_plantings AS PLA,
         lf_crops AS C
    WHERE
        PB.block_id = B.id
        AND B.property_id = P.id
        AND P.property_type_id = PT.id
        AND B.size_type_id = ST.id
        AND PB.variety_id = V.id
        AND V.crop_id = C.id
        AND PB.planting_id = PLA.id
        AND PB.plant_still_active = true
        AND B.is_deleted = false
        AND P.is_deleted = false
        AND PT.is_deleted = false
        AND ST.is_deleted = false
        AND V.is_deleted = false
        AND C.is_deleted = false';

SET statement = CONCAT(statement, whereClause, ') AS PlantingBlock ORDER BY PlantingBlock.plant_date DESC');

这会产生一个错误，
SQLSTATE [42S22]：未找到列：1054未知栏目列表中的PlantingBlock.id列

This gives an error called, SQLSTATE[42S22]: Column not found: 1054 Unknown column 'PlantingBlock.id' in 'field list'

推荐答案

问题是你试图添加一个条件到内部select同时使用赋予此子查询的别名引用 id 列。

The problem is that you try to add a condition to an inner select while referring to id column using an alias that gave to this subquery.

因此，您可以使用括号关闭子查询，将 WHERE 子句应用到您的外部 SELECT

Therefore you either close subquery with parenthesis and apply WHERE clause to your outer SELECT

SELECT PlantingBlock.* 
FROM 
(
   ...
) AS PlantingBlock
 WHERE PlantingBlock.id IN (13,10) 
 ORDER BY PlantingBlock.plant_date DESC

或更好，但仅引用内部select中的id字段

or better yet just refer to id field in your inner select.

SELECT PlantingBlock.* 
FROM 
(
   ...
   AND PB.id IN (13,10)
) AS PlantingBlock
 ORDER BY PlantingBlock.plant_date DESC

这篇关于未找到列：1054“字段列表”中的未知列“PlantingBlock.id”的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！