Symfony sorted model with having request

I have two models : Operation and OperationHistory

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,开发者_如何学Python 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 }

In order to get all Operation by Person, I use an index on OperationHistory. So if I need all the person's operation with a specific status:

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();
}

Thanks to the index on ohdate, I assume with the groupBy, I only get the last OperationHistory about a specific Operation. Without the having clause, It's good, but I only want Operations with a specific Status. But if I set this having clause, I get nothing at all.

In fact, I need to translate this sql request :

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

sorry for my bad english and I hope these informations will be usefull to help me.

Thank u !

---

Its very difficult to understand what you are trying to do just based on your questions above - can you include some other information ?

You can use join and orderby methods of the Doctrine_Query class :

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

---

By using Doctrine_RawSql(), it works !

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();
}