php - 无法创建 Doctrine 查询 - 许多 addFrom

Question

我在将 SQL 查询转换为 Doctrine 时遇到问题。

我有以下设置:

路径(层级)

编号 |模板类型 | object_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

在尝试将其转换为 Doctrine 时，我似乎遇到了麻烦。

$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”

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

有什么想法吗？

Answer 1

当您开始进行复杂的查询时，有时您不想依赖 doctrine 来生成您的 sql 代码。更不用说您在尝试转换一个已经工作的查询以满足 Doctrine 时所经历的痛苦。

在这些情况下，我通常会建议将查询作为 SQL 运行，而不是通过 doctrine 查询构建器传递。

看看Doctrine_RawSql()