Bonjour à tous,

Dans le cadre d'un projet web d'outil de gestion d'une maison d'acceuil specialise, j'ai mis en place en BDD un planning des activites theorique.

Elle se compose de cette facon :
Code yaml : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
Planning:
  actAs:
    Timestampable: ~
  columns:
    begin_at: { type: date, default: null }
#    end_at: { type: date, default: null }
 
PlanningWeek:
  columns:
    number: { type: integer, notnull: true }
    planning_id: { type: integer, notnull: true }
  relations:
    Planning: { class: Planning, local: planning_id, type: one, foreign: id, foreignType: many, foreignAlias: Weeks }
 
PlanningActivite:
  columns:
    day: { type: enum, values: [lundi, mardi, mercredi, jeudi, vendredi, samedi, dimanche], notnull: true }
    planning_week_id: { type: integer, notnull: true }
    activite_id: { type: integer, notnull: true }
  relations:
    Week: { class: PlanningWeek, local: planning_week_id, type: one, foreign: id, foreignType: many, foreignAlias: PlanningActivites }
    Activite: { class: Activite, local: activite_id, type: one, foreign: id, foreignType: many, foreignAlias: PlanningActivites }
 
ActiviteGroupe:
  columns:
    name: { type: string(50), notnull: true, unique: true }
 
Activite:
  columns:
    name: { type: string(50), notnull: true, unique: true }
    groupe_id: { type: integer, notnull: true }
  relations:
    Groupe: { class: ActiviteGroupe, local: groupe_id, type: one, foreign: id, foreignType: many, foreignAlias: Activites }
 
Creneau:
  columns:
    begin_at: { type: time, notnull: true }
    end_at: { type: time, notnull: true }
    planning_activite_id: { type: integer, notnull: true }
  relations:
    PlanningActivite: { class: PlanningActivite, local: planning_activite_id, type: one, foreign: id, foreignType: many, foreignAlias: Creneaux }
    Encadrants: { refClass: ActiviteCreneauEncadrant, class: sfGuardUser, local: creneau_id, foreign: encadrant_id, foreignAlias: PlanningActivitesCreneaux }
    Residents: { refClass: ActiviteCreneauResident, class: Resident, local: creneau_id, foreign: resident_id, foreignAlias: PlanningActivitesCreneaux }
 
ActiviteCreneauEncadrant:
  options:
    symfony:
      form: false
      filter: false
  columns:
    creneau_id: { type: integer, notnull: true }
    encadrant_id: { type: integer, notnull: true }
 
ActiviteCreneauResident:
  options:
    symfony:
      form: false
      filter: false
  columns:
    creneau_id: { type: integer, notnull: true }
    resident_id: { type: integer, notnull: true }

Je souhaite afficher les activites mises en place d'un jour precis d'une semaine precise, d'un planning precis. Prenom par exemple le lundi de la premiere semaine (id: 4) de mon deuxieme planning.

J'effectue la requete suivante (methode getActivitesOf) :
Code php : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
<?php
 
/**
 * ActiviteGroupeTable
 * 
 * This class has been auto-generated by the Doctrine ORM Framework
 */
class ActiviteGroupeTable extends Doctrine_Table
{
    /**
     * Returns an instance of this class.
     *
     * @return object ActiviteGroupeTable
     */
    public static function getInstance()
    {
        return Doctrine_Core::getTable('ActiviteGroupe');
    }
 
    public static function getActivitiesOf(PlanningWeek $week, $day)
    {
        $pactivites = PlanningActiviteTable::getInstance()->createQuery('pa')
                ->select('pa.activite_id')
                ->where('pa.planning_week_id = ?', $week->getId())
                ->andWhere('pa.day = ?', $day)
                ->execute(array(), Doctrine::HYDRATE_SINGLE_SCALAR);
 
        if (count($pactivites) == 0)
            return NULL;
        $pactivites = is_array($pactivites) ? implode(',', $pactivites) : $pactivites;
 
        $q = ActiviteGroupeTable::getInstance()->createQuery('ag');
        $q->innerJoin('ag.Activites a WITH a.id IN ('.$pactivites.')');
        $q->innerJoin('a.PlanningActivites pa WITH pa.planning_week_id = ? AND pa.day = ?',
                        array($week->getId(), $day));
        $q->leftJoin('pa.Creneaux cr');
//        $q->addOrderBy('ag.name, a.name, cr.begin_at');
        $q->addOrderBy('ag.name, a.name');
 
//        echo '<pre>';
//        print_r($q->execute(array(), Doctrine::HYDRATE_ARRAY));
//        echo $q->getSqlQuery();
//        die('--end--');
 
        return $q->execute();
    }
 
    public static function getGroupesActivites($inArray = false)
    {
        $q = ActiviteGroupeTable::getInstance()->createQuery('ag')
                ->select('ag.name, a.name')
                ->leftJoin('ag.Activites a')
                ->orderBy('ag.name, a.name')
                ;
 
        if ($inArray == true)
            return $q->execute(array(), Doctrine::HYDRATE_ARRAY);
        return $q->execute();
    }
}

Cette requete fonctionne, car le tableau retourne correspond bien au bonnes activites.

Le soucis ce fait a l'affichage avec ce template:

Code php : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
<div id="middleContent" class="colRight">
    <h3>Planning des activites actuel > Semaine <?php echo $week->getNumber() ?> > <?php echo $day ?></h3>
 
    <?php if ($week->getPlanning()->isEditable()): ?>
    <div>
        <?php include_partial('addActiviteForm', array('form' => $addActiviteForm)); ?>
    </div>
    <?php endif; ?>
 
 
    <?php if ($activiteGroupes == NULL): ?>
        <strong>Aucune activité n'est associée à ce jour</strong>
    <?php else: ?>
 
    <?php foreach ($activiteGroupes as $ag): ?>
    <?php if ($ag->getActivites()->count() == 0): continue; endif; ?>
        <h4><?php echo $ag->getName() ?></h4>
        <div class="activitesContent">
        <?php foreach($ag->getActivites() as $a): ?>
            <?php if ($a->getPlanningActivites()->count() == 0): continue; endif; ?>
            <div class="activite">
                <table>
                    <tr>
                        <th colspan="3" class="high red"><?php echo $a->getName() ?></th>
                    </tr>
                    <?php $pas = $a->getPlanningActivites(); ?>
 
                    <?php if ($pas[0]->getCreneaux()->count() == 0): ?>
                        <tr>
                            <td class="noway"><strong><?php echo $pas[0]->getId() ?> Aucun créneau pour cette activité</strong></td>
                        </tr>
                    <?php else: ?>
 
                    <tr class="high">
                        <th>Créneau <?php echo $pas[0]->getId() ?></th>
                        <th>Participants</th>
                        <th>Encadrants</th>
                    </tr>
 
                    <?php foreach($pas[0]->getCreneaux() as $cr): ?>
                        <tr>
                            <td>
                                <?php
                                    $begin = new DateTime($cr->getBeginAt()); 
                                    echo $begin->format('H\hi');
                                ?>
                                -
                                <?php
                                    $end = new DateTime($cr->getEndAt()); 
                                    echo $end->format('H\hi');
                                ?>
                            </td>
                            <td class="middle">
                                <ul>
                                <?php foreach($cr->getResidents() as $rek => $re): ?>
                                    <li>
                                        <a href="<?php echo url_for('resident_show', $re) ?>">
                                            <?php echo $re ?>
                                        </a>
                                    </li>
                                <?php endforeach; ?>
                                </ul>
                            </td>
                            <td>
                                <ul>
                                <?php foreach($cr->getEncadrants() as $enk => $en): ?>
                                    <li><?php echo $en->getName() ?></li>
                                <?php endforeach; ?>
                                </ul>
                            </td>
                        </tr>
                    <?php endforeach; ?>
                        <tr>
                            <td colspan="3" class="addCreneau">
                                <div>
                                    <img src="/images/buttons/add.png" alt="+"/>
                                    <a href="#">Ajouter un créneau</a>
                                </div>
                            </td>
                        </tr>
 
                    <?php endif; // Creneaux ?>
                </table>
            </div>
        <?php endforeach; ?>
            <div style="clear:left"></div>
        </div>
    <?php endforeach; ?>
 
    <?php endif; ?>
</div>

En mode objet, doctrine me retourne tout les PlanningActivites de tout les planning (1 et 2) existant dans ma bdd ! Pourquoi ? Tout simplement car elle ignore mes innerjoin et se contente de refaire des select sans mes conditions, comme le montre ce log :

Code sql : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
    SET NAMES 'UTF8'
    0.00s, "doctrine" connection
 
    SELECT i.id AS i__id, i.number AS i__number, i.planning_id AS i__planning_id FROM im_planning_week i WHERE (i.id = '4') LIMIT 1
    0.00s, "doctrine" connection
 
-- Mes deux requetes --
 
    SELECT i.activite_id AS i__activite_id FROM im_planning_activite i WHERE (i.planning_week_id = '4' AND i.day = 'lundi')
    0.00s, "doctrine" connection
 
    SELECT i.id AS i__id, i.name AS i__name, i2.id AS i2__id, i2.name AS i2__name, i2.groupe_id AS i2__groupe_id, i3.id AS i3__id, i3.day AS i3__day, i3.planning_week_id AS i3__planning_week_id, i3.activite_id AS i3__activite_id, i4.id AS i4__id, i4.begin_at AS i4__begin_at, i4.end_at AS i4__end_at, i4.planning_activite_id AS i4__planning_activite_id FROM im_activite_groupe i INNER JOIN im_activite i2 ON i.id = i2.groupe_id AND (i2.id IN (9, 1, 4)) INNER JOIN im_planning_activite i3 ON i2.id = i3.activite_id AND ((i3.planning_week_id = '4' AND i3.day = 'lundi')) LEFT JOIN im_creneau i4 ON i3.id = i4.planning_activite_id ORDER BY i.name, i2.name
    0.00s, "doctrine" connection
 
-- Les requetes supplementaires de doctrine --
 
    SELECT i.id AS i__id, i.begin_at AS i__begin_at, i.created_at AS i__created_at, i.updated_at AS i__updated_at FROM im_planning i WHERE (i.id = '2')
    0.00s, "doctrine" connection
 
    SELECT i.id AS i__id, i.name AS i__name, i2.id AS i2__id, i2.name AS i2__name FROM im_activite_groupe i LEFT JOIN im_activite i2 ON i.id = i2.groupe_id ORDER BY i.name, i2.name
    0.00s, "doctrine" connection
 
    SELECT i.id AS i__id, i.day AS i__day, i.planning_week_id AS i__planning_week_id, i.activite_id AS i__activite_id FROM im_planning_activite i WHERE (i.activite_id IN ('11'))
    0.00s, "doctrine" connection
 
    SELECT i.id AS i__id, i.day AS i__day, i.planning_week_id AS i__planning_week_id, i.activite_id AS i__activite_id FROM im_planning_activite i WHERE (i.activite_id IN ('10'))
    0.00s, "doctrine" connection
 
    SELECT i.id AS i__id, i.name AS i__name, i.first_name AS i__first_name, i.birthday AS i__birthday, i.mas_enter_at AS i__mas_enter_at, i.photo AS i__photo, i.situation_id AS i__situation_id, i.respiration_id AS i__respiration_id, i.alimentation_mode_id AS i__alimentation_mode_id, i.alimentation_regime_id AS i__alimentation_regime_id, i.alimentation_boisson_id AS i__alimentation_boisson_id, i.alimentation_autonomie_id AS i__alimentation_autonomie_id, i.alimentation_materiel_id AS i__alimentation_materiel_id, i.alimentation_fausse_route AS i__alimentation_fausse_route, i.alimentation_musulman AS i__alimentation_musulman, i.chaise_percee AS i__chaise_percee, i.elemination_autonomie_id AS i__elemination_autonomie_id, i.mobilite_deplacement_id AS i__mobilite_deplacement_id, i.mobilite_appareillage AS i__mobilite_appareillage, i.sommeil_regulier AS i__sommeil_regulier, i.sommeil_barriere AS i__sommeil_barriere, i.sommeil_protege_barriere AS i__sommeil_protege_barriere, i.sommeil_lit_medicalise AS i__sommeil_lit_medicalise, i.sommeil_change_de_nuit AS i__sommeil_change_de_nuit, i.sommeil_securite AS i__sommeil_securite, i.habillement_id AS i__habillement_id, i.deshabillement_id AS i__deshabillement_id, i.hygiene_autonomie_id AS i__hygiene_autonomie_id, i.communication_id AS i__communication_id, i.croyance_id AS i__croyance_id, i.created_at AS i__created_at, i.updated_at AS i__updated_at, i.slug AS i__slug, i2.id AS i2__id, i2.creneau_id AS i2__creneau_id, i2.resident_id AS i2__resident_id FROM im_resident i LEFT JOIN im_activite_creneau_resident i2 ON i.id = i2.resident_id WHERE (i2.creneau_id IN ('25'))
    0.00s, "doctrine" connection
 
    SELECT i.id AS i__id, i.first_name AS i__first_name, i.last_name AS i__last_name, i.email_address AS i__email_address, i.username AS i__username, i.algorithm AS i__algorithm, i.salt AS i__salt, i.password AS i__password, i.is_active AS i__is_active, i.is_super_admin AS i__is_super_admin, i.last_login AS i__last_login, i.is_ldap AS i__is_ldap, i.created_at AS i__created_at, i.updated_at AS i__updated_at, i2.id AS i2__id, i2.creneau_id AS i2__creneau_id, i2.encadrant_id AS i2__encadrant_id FROM im_sf_guard_user i LEFT JOIN im_activite_creneau_encadrant i2 ON i.id = i2.encadrant_id WHERE (i2.creneau_id IN ('25'))
    0.00s, "doctrine" connection
 
    SELECT i.id AS i__id, i.day AS i__day, i.planning_week_id AS i__planning_week_id, i.activite_id AS i__activite_id FROM im_planning_activite i WHERE (i.activite_id IN ('3'))
    0.00s, "doctrine" connection
 
    SELECT i.id AS i__id, i.begin_at AS i__begin_at, i.end_at AS i__end_at, i.planning_activite_id AS i__planning_activite_id FROM im_creneau i WHERE (i.planning_activite_id IN ('3'))
    0.00s, "doctrine" connection
 
    SELECT i.id AS i__id, i.day AS i__day, i.planning_week_id AS i__planning_week_id, i.activite_id AS i__activite_id FROM im_planning_activite i WHERE (i.activite_id IN ('2'))
    0.00s, "doctrine" connection
 
    SELECT i.id AS i__id, i.begin_at AS i__begin_at, i.end_at AS i__end_at, i.planning_activite_id AS i__planning_activite_id FROM im_creneau i WHERE (i.planning_activite_id IN ('2'))
    0.00s, "doctrine" connection
 
    SELECT i.id AS i__id, i.name AS i__name, i.first_name AS i__first_name, i.birthday AS i__birthday, i.mas_enter_at AS i__mas_enter_at, i.photo AS i__photo, i.situation_id AS i__situation_id, i.respiration_id AS i__respiration_id, i.alimentation_mode_id AS i__alimentation_mode_id, i.alimentation_regime_id AS i__alimentation_regime_id, i.alimentation_boisson_id AS i__alimentation_boisson_id, i.alimentation_autonomie_id AS i__alimentation_autonomie_id, i.alimentation_materiel_id AS i__alimentation_materiel_id, i.alimentation_fausse_route AS i__alimentation_fausse_route, i.alimentation_musulman AS i__alimentation_musulman, i.chaise_percee AS i__chaise_percee, i.elemination_autonomie_id AS i__elemination_autonomie_id, i.mobilite_deplacement_id AS i__mobilite_deplacement_id, i.mobilite_appareillage AS i__mobilite_appareillage, i.sommeil_regulier AS i__sommeil_regulier, i.sommeil_barriere AS i__sommeil_barriere, i.sommeil_protege_barriere AS i__sommeil_protege_barriere, i.sommeil_lit_medicalise AS i__sommeil_lit_medicalise, i.sommeil_change_de_nuit AS i__sommeil_change_de_nuit, i.sommeil_securite AS i__sommeil_securite, i.habillement_id AS i__habillement_id, i.deshabillement_id AS i__deshabillement_id, i.hygiene_autonomie_id AS i__hygiene_autonomie_id, i.communication_id AS i__communication_id, i.croyance_id AS i__croyance_id, i.created_at AS i__created_at, i.updated_at AS i__updated_at, i.slug AS i__slug, i2.id AS i2__id, i2.creneau_id AS i2__creneau_id, i2.resident_id AS i2__resident_id FROM im_resident i LEFT JOIN im_activite_creneau_resident i2 ON i.id = i2.resident_id WHERE (i2.creneau_id IN ('4'))
    0.00s, "doctrine" connection
 
    SELECT i.id AS i__id, i.first_name AS i__first_name, i.last_name AS i__last_name, i.email_address AS i__email_address, i.username AS i__username, i.algorithm AS i__algorithm, i.salt AS i__salt, i.password AS i__password, i.is_active AS i__is_active, i.is_super_admin AS i__is_super_admin, i.last_login AS i__last_login, i.is_ldap AS i__is_ldap, i.created_at AS i__created_at, i.updated_at AS i__updated_at, i2.id AS i2__id, i2.creneau_id AS i2__creneau_id, i2.encadrant_id AS i2__encadrant_id FROM im_sf_guard_user i LEFT JOIN im_activite_creneau_encadrant i2 ON i.id = i2.encadrant_id WHERE (i2.creneau_id IN ('4'))
    0.00s, "doctrine" connection
 
    SELECT i.id AS i__id, i.first_name AS i__first_name, i.last_name AS i__last_name, i.email_address AS i__email_address, i.username AS i__username, i.algorithm AS i__algorithm, i.salt AS i__salt, i.password AS i__password, i.is_active AS i__is_active, i.is_super_admin AS i__is_super_admin, i.last_login AS i__last_login, i.is_ldap AS i__is_ldap, i.created_at AS i__created_at, i.updated_at AS i__updated_at FROM im_sf_guard_user i WHERE (i.id = '3') LIMIT 1
    0.00s, "doctrine" connection
 
    SELECT i.id AS i__id, i.title AS i__title, i.content AS i__content, i.category_id AS i__category_id, i.author_id AS i__author_id, i.created_at AS i__created_at, i.updated_at AS i__updated_at, i.slug AS i__slug FROM im_news i ORDER BY i.updated_at DESC LIMIT 20
    0.00s, "doctrine" connection

Alors voila, comment eviter que doctrine aille chercher des donnees que je ne veux pas en l'empechant de refaire des requetes ?

Merci d'avance de votre aide et n'hesitez pas a me demander des precisions si cela ne vous semble pas clair !

Sullivan