doctrine - Doctrine ORM 中的 View

Question

我在处理 Doctrine 中的 View 时遇到了问题。

我的数据库中有以下 View :

$q = Doctrine_Query::create()
                        ->select('c.id, c.name, m.id, m.name, m.status, 
                                  m.modified, m.entered, m.accountid')
                        ->from('Category c')
                        ->leftJoin('c.Mailing m');
$view = new Doctrine_View($q, 'view_mailings');
$q->execute(array(), Doctrine::HYDRATE_ARRAY);

结果包括所有记录。但是我需要获取一个子集
基于不同的 accountid 值并由不同的排序
列。我怎样才能做到这一点？

谢谢你的帮助。

Answer 1

一个不同的解决方案可能是为 config/doctrine/中的 View 创建一个架构，如下所示:

objective:
  tableName: objective
  columns:
    id:
      type: integer
    zone:
      type: string(50)
    province:
      type: string(30)
    city:
      type: string(100)
    population:
      type: integer
    city_obj_id:
      type: integer
    clinic_number:
      type: integer
    clinic_obj:
      type: integer
    comments:
      type: string(255)

然后像这样在 data/sql/中放入一个objective_view.sql:

DROP VIEW IF EXISTS objective;
DROP TABLE IF EXISTS objective;
CREATE VIEW objective AS
    SELECT
    c.id as id,
    z.name as zone,
    p.name as province,
    c.name as city,
    cs.amount as population,
    co.id as city_obj_id,
    co.clinic_num as clinic_number,
    co.clinic_obj as clinic_obj,
    co.comments as comments

    FROM
    city c left join city_objective co on co.city_id = c.id
    left join city_census cs on (cs.city_id = c.id and cs.year = 2009)
    left join province p on p.id = c.province_id
    left join zone z on z.id = p.zone_id;

通过这种方式，目标表将填充更新的数据，您可以对其执行查询以轻松获取相关信息。