[symfony] Problem with two Doctrine request

Question

Hello,

Hello I have a little problem with requests : In an action executeFiche, I have three requests

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

The problem is my two requests (the first is alright) hinder themselves and the result returned is wrong.

Edit : SQL request

    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

Answer 1

As a good symfony practice, you could start by putting the queries in a model (something like LogementTable.class.php).

You have also some syntax problems in your queries.

---

Update : I didn't notice the parenthesis is closed on the line after

In the following line, you open the parenthesis but it isn't closed after :

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

---

Second error, in SQL dates must be surrounded by quotes :

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

---

Update 2 :

Try this as your second request :

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

Answer 2

Thanks for this answer,

But when I want to put the queries in my model, I have others problems : I have an error, with '$request->getParameter('id')'. I exchange it to '$this->getId()' and Doctrine tell me I have an error.

For the parenthesis, I close them in the next. I don't know another way to generate SQL with order in the where. It is to have :

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

Edit : I still have my problem. When the second request have something to return, the first doesn't return all the entries