Symfony 排序模型有请求

Question

我有两个模型：操作和操作历史记录

Operation:
  columns:
    startdate:      { type: timestamp, notnull: true }
    enddate:        { type: timestamp, notnull: true }
    year:           { type: integer, notnull: true }
    abscomment:     { type: string(500) }
    person_id:      { type: integer, notnull: true }
    operationtype_id: { type: integer, notnull: true }
  relations:
    Person:        { onDelete: CASCADE, local: person_id, foreign: id, foreignAlias: Operations}
    OperationType:   { onDelete: CASCADE, local: absencetype_id, foreign: id, foreignAlias: Operations }

OperationHistory:
  columns:
    oh_comment:      { type: string(500), notnull: true }
    oh_date:         { type: timestamp, notnull: true }
    status_id:      { type: integer, notnull: true }
    operation_id:     { type: integer, notnull: true }
    updater:        { type: integer, notnull: true }
  indexes:
    date:
      fields:
        ohdate:
          sorting: DESC
  relations:
    Operation:        { onDelete: CASCADE, local: operation_id, foreign: id, foreignAlias: OperationsHistory }
    Person:         { onDelete: CASCADE, local: updater, foreign: id, foreignAlias: OperationsHistory }
    OperationStatus:  { onDelete: CASCADE, local: status_id, foreign: id, foreignAlias: OperationsHistory }

为了获取所有按人员操作，我在操作历史记录上使用索引。因此，如果我需要具有特定状态的所有人员的操作：

public function getOperations($person, $status){
  $q = $this->createQuery('o')
    ->leftJoin('o.OperationsHistory oh')
    ->leftJoin('p.Person p')
    ->groupBy('ah.absence_id')
    ->having('ah.status_id = ?', $status);

  return $q->execute();
}

由于 ohdate 上的索引，我假设使用 groupBy，我只能获得有关特定操作的最后一个操作历史记录。没有having子句，这很好，但我只想要具有特定状态的操作。但如果我设置这个having子句，我什么也得不到。

事实上，我需要翻译这个 sql 请求：

SELECT *
FROM operation o
LEFT JOIN (
  SELECT *
  FROM operation_history
  ORDER BY ohdate DESC
) oh ON o.id = oh.absence_id
LEFT JOIN person p ON p.id = o.person_id
WHERE p.id = 1
GROUP BY oh.operation_id
HAVING oh.status_id = 1

抱歉我的英语不好，我希望这些信息对我有用。

感谢你 ！

Answer 1

仅根据您的上述问题很难理解您想要做什么 - 您可以提供一些其他信息吗？

您可以使用 Doctrine_Query 类的 join 和 orderby 方法：

$query = Doctrine_Core::getTable('operation')->createQuery()
->innerjoin('operation_history ......')
->orderby('.....')

Answer 2

通过使用 Doctrine_RawSql()，它可以工作！

public function getAbsenceQueries($person, $status){
    $q = new  Doctrine_RawSql();
    $q->select('{o.*}, {oh.*}')
      ->from('operation o LEFT JOIN (SELECT * from operation_history order by ohdate DESC) oh ON o.id=oh.operation_id LEFT JOIN person p ON p.id = o.person_id')
      ->where('mg.group_id = ?', $group)
      ->groupBy('ah.absence_id')
      ->having('ah.status_id = ?', $status)
      ->addComponent('o','Operation o')
      ->addComponent('oh','o.OperationsHistory oh')
      ->addComponent('p','o.Person p');

    return $q->execute();
}