两个学说请求存在问题

Question

您好，我在请求方面遇到了一个小问题：在一个动作executeFiche中，我有三个请求

public function executeFiche(sfWebRequest $request){

   // Récupération du logement correspondant à l'ID passé dans l'URL
   $this->forward404Unless($this->logement = Doctrine::getTable('Logement')->find(array($request->getParameter('id'))), sprintf('Object logement does not exist (%s).', $request->getParameter('id')));

   // Récupération du (ou des) locataire(s) actuel(s) du logement
   $locataires = Doctrine::getTable('Logement')->createQuery('l')
     ->leftJoin('l.Bail b')
     ->leftJoin('b.Locataire')
     ->where('l.id = ?', $request->getParameter('id'))
     ->andWhere('(b.datefin >= ?', date('Y-m-d', time()))
     ->orWhere('b.datefin = 0000-00-00)')
     ->execute();

 // Récupération du (ou des) locataire(s) précédent(s) du logement
 $locatairesprec = Doctrine::getTable('Logement')->createQuery('l')
     ->leftJoin('l.Bail b')
     ->leftJoin('b.Locataire')
     ->where('l.id = ?', $request->getParameter('id'))
     ->andWhere('b.datefin < ?', date('Y-m-d', time()))
     ->andWhere('b.datefin != 0000-00-00')
     ->orderBy('datedeb')
     ->execute();

 $this->locataires = $locataires;
 $this->locatairesprec = $locatairesprec;
  }

问题是我的两个请求（第一个是好的）阻碍了自己并且返回的结果是错误的。

编辑： SQL 请求

    SELECT l.id AS l__id, l.adresse AS l__adresse, l.montee AS l__montee, l.etage AS
 l__etage, l.numetage AS l__numetage, l.numlogt AS l__numlogt, l.taille AS l__taille,
 l.surfacehab AS l__surfacehab, l.typelog AS l__typelog, l.intergen AS l__intergen,
 l.ascenseur AS l__ascenseur, l.ascenseuracc AS l__ascenseuracc, l.accessibl AS 
l__accessibl, l.adaptable AS l__adaptable, l.adapte AS l__adapte, l.chauffage AS 
l__chauffage, l.chargeschauf AS l__chargeschauf, l.chargeseauch AS l__chargeseauch,
 l.chargeseaufr AS l__chargeseaufr, l.reservataire AS l__reservataire, l.loyer AS 
l__loyer, l.loyercc AS l__loyercc, l.commentaires AS l__commentaires, l.created_at AS 
l__created_at, l.updated_at AS l__updated_at, b.id AS b__id, b.locataire AS b__locataire, 
b.logement AS b__logement, b.datedeb AS b__datedeb, b.datefin AS b__datefin, b.colloc AS 
b__colloc, b.bailglissant AS b__bailglissant, l2.nud AS l2__nud, l2.titre AS l2__titre,
 l2.nom AS l2__nom, l2.prenom AS l2__prenom, l2.nationalite AS l2__nationalite, 
l2.datenaissance AS l2__datenaissance, l2.statutmatri AS l2__statutmatri, l2.statutpro AS
 l2__statutpro, l2.nbenfants AS l2__nbenfants, l2.monoparental AS l2__monoparental, 
l2.numprec AS l2__numprec, l2.rueprec AS l2__rueprec, l2.quartierprec AS l2__quartierprec,
 l2.codepostalprec AS l2__codepostalprec, l2.villeprec AS l2__villeprec, l2.statutlogprec 
AS l2__statutlogprec FROM logement l LEFT JOIN bail b ON l.id = b.logement LEFT JOIN 
locataire l2 ON b.locataire = l2.nud WHERE (l.id = '1' AND (b.datefin >= '2010-07-01' OR
 b.datefin = '0000-00-00'))

    0.03s, "doctrine" connection
    #

    SELECT l.id AS l__id, l.adresse AS l__adresse, l.montee AS l__montee, l.etage AS 
l__etage, l.numetage AS l__numetage, l.numlogt AS l__numlogt, l.taille AS l__taille,
 l.surfacehab AS l__surfacehab, l.typelog AS l__typelog, l.intergen AS l__intergen, 
l.ascenseur AS l__ascenseur, l.ascenseuracc AS l__ascenseuracc, l.accessibl AS 
l__accessibl, l.adaptable AS l__adaptable, l.adapte AS l__adapte, l.chauffage AS 
l__chauffage, l.chargeschauf AS l__chargeschauf, l.chargeseauch AS l__chargeseauch, 
l.chargeseaufr AS l__chargeseaufr, l.reservataire AS l__reservataire, l.loyer AS l__loyer,
 l.loyercc AS l__loyercc, l.commentaires AS l__commentaires, l.created_at AS l__created_at, l.updated_at AS l__updated_at, b.id AS b__id, b.locataire AS b__locataire, 
b.logement AS b__logement, b.datedeb AS b__datedeb, b.datefin AS b__datefin, b.colloc AS
 b__colloc, b.bailglissant AS b__bailglissant, l2.nud AS l2__nud, l2.titre AS l2__titre,
 l2.nom AS l2__nom, l2.prenom AS l2__prenom, l2.nationalite AS l2__nationalite, 
l2.datenaissance AS l2__datenaissance, l2.statutmatri AS l2__statutmatri, l2.statutpro AS 
l2__statutpro, l2.nbenfants AS l2__nbenfants, l2.monoparental AS l2__monoparental, 
l2.numprec AS l2__numprec, l2.rueprec AS l2__rueprec, l2.quartierprec AS l2__quartierprec,
 l2.codepostalprec AS l2__codepostalprec, l2.villeprec AS l2__villeprec, l2.statutlogprec
 AS l2__statutlogprec FROM logement l LEFT JOIN bail b ON l.id = b.logement LEFT JOIN 
locataire l2 ON b.locataire = l2.nud WHERE (l.id = '1' AND b.datefin < '2010-07-01' AND
 b.datefin != '0000-00-00') ORDER BY datedeb

编辑

感谢您的回答，

但是当我想将查询放入我的模型中时，我遇到了其他问题：我有一个错误，带有 '$request ->getParameter('id')'。我将其交换为“$this->getId()”，Doctrine 告诉我有一个错误。

对于括号，我在下面将其关闭。我不知道另一种方法来生成 SQL 的顺序在哪里。它是有：

WHERE l.id = $request->getParameter('id') AND ( b.datefin >= date('Y-m-d', time()) OR b.datefin = 0000-00-00 )

编辑： 我仍然有我的问题。当第二个请求要返回某些内容时，第一个请求不会返回所有条目

Answer 1

作为一个好的 symfony 实践，您可以首先将查询放入模型中（例如 LogementTable.class.php）。

您的查询中还存在一些语法问题。

---

更新： 我没有注意到后面的行上的括号被关闭

在下面的行中，您打开括号，但它没有在之后关闭：

->andWhere('(b.datefin >= ?', date('Y-m-d', time()))

---

第二个错误，在SQL 日期必须用引号引起来：

->orWhere("b.datefin = '0000-00-00')")
// ...
->andWhere("b.datefin != '0000-00-00'")

---

更新 2：

尝试将此作为您的第二个请求：

 $locatairesprec = Doctrine::getTable('Logement')->createQuery('l')
     ->leftJoin('l.Bail b')
     ->leftJoin('b.Locataire')
     ->where('l.id = ?', $request->getParameter('id'))
     ->andWhere('(b.datefin < ?', date('Y-m-d', time()))
     ->andWhere("b.datefin != '0000-00-00')")
     ->orderBy('datedeb')
     ->execute();