我无法将SQL查询转换为条令。

我有以下设置:

路径(层次结构)

Id |模板|类型|对象| Id |名称|路径| rgt | lft |级别

财产

Id |其他|列

发展

Id |其他|列

我正在尝试选择模板类型为“Property”的路径(这些路径在属性表中总是有一个'object_id'记录),并且我正在尝试加入父路径(在开发表中总是有一个记录)。

以下原始SQL工作得很好:

SELECT
    node.*,
    parent.*,
    development.*,
    property.*
FROM
    path AS node,
    path AS parent,
    development,
    property
WHERE
    node.lft
BETWEEN
    parent.lft AND  parent.rgt
AND
    node.template_type =  'Property'
AND
    parent.level = node.level - 1
AND
    development.id = parent.object_id
AND
    property.id = node.object_id

当我试图把它转变成教义时,我似乎遇到了麻烦。

$q = Doctrine_Query::create()

    ->select("node.*")
    ->addSelect("parent.*")
    ->addSelect("development.*")
    ->addSelect("property.*")

    ->from("Path node")
    ->addFrom("Path parent")
    ->addFrom("Development development")
    ->addFrom("Property property")

    ->where("node.lft BETWEEN parent.lft AND parent.rgt")
    ->addWhere("node.template_type = 'Property'")
    ->addWhere("parent.level = node.level - 1")
    ->addWhere("development.id = parent.object_id")
    ->addWhere("property.id = node.object_id");

它生成的查询如下:

SELECT p.id                          AS p__id, 
       p.template_type               AS p__template_type, 
       p.object_id                   AS p__object_id, 
       p.created_at                  AS p__created_at, 
       p.updated_at                  AS p__updated_at, 
       p.meta_page_title             AS p__meta_page_title, 
       p.meta_navigation_title       AS p__meta_navigation_title, 
       p.meta_path                   AS p__meta_path, 
       p.meta_keywords               AS p__meta_keywords, 
       p.meta_description            AS p__meta_description, 
       p.meta_visible_in_navigation  AS p__meta_visible_in_navigation, 
       p.root_id                     AS p__root_id, 
       p.lft                         AS p__lft, 
       p.rgt                         AS p__rgt, 
       p.level                       AS p__level, 
       p2.id                         AS p2__id, 
       p2.template_type              AS p2__template_type, 
       p2.object_id                  AS p2__object_id, 
       p2.created_at                 AS p2__created_at, 
       p2.updated_at                 AS p2__updated_at, 
       p2.meta_page_title            AS p2__meta_page_title, 
       p2.meta_navigation_title      AS p2__meta_navigation_title, 
       p2.meta_path                  AS p2__meta_path, 
       p2.meta_keywords              AS p2__meta_keywords, 
       p2.meta_description           AS p2__meta_description, 
       p2.meta_visible_in_navigation AS p2__meta_visible_in_navigation, 
       p2.root_id                    AS p2__root_id, 
       p2.lft                        AS p2__lft, 
       p2.rgt                        AS p2__rgt, 
       p2.level                      AS p2__level, 
       d.id                          AS d__id, 
       d.name                        AS d__name, 
       d.latitude                    AS d__latitude, 
       d.longitude                   AS d__longitude, 
       d.introduction                AS d__introduction, 
       d.description                 AS d__description, 
       d.thumbnail                   AS d__thumbnail, 
       d.path_id                     AS d__path_id, 
       p3.id                         AS p3__id, 
       p3.price                      AS p3__price, 
       p3.number_of_bedrooms         AS p3__number_of_bedrooms, 
       p3.key_features               AS p3__key_features, 
       p3.description                AS p3__description, 
       p3.thumbnail                  AS p3__thumbnail, 
       p3.property_type_id           AS p3__property_type_id, 
       p3.path_id                    AS p3__path_id 
FROM   path p, 
       path p2, 
       development d, 
       property p3 
WHERE  ( p.lft BETWEEN p2.lft AND p2.rgt 
         AND p.template_type = 'Property' 
         AND p2.level = node.level - 1 
         AND d.id = p2.object_id 
         AND p3.id = p.object_id ) 

从而导致此错误:

1054-“where子句”中的未知列“node.level”

有什么想法吗?我可以看到它应该说“和p2.level=p.level-1”,但它似乎没有转换节点。

有什么想法吗?