Add created field to findAllIndexed results
[airbundle] / Repository / GoogleTokenRepository.php
index fa2a8545d404feec28e892096218ca95d38b89ef..29b96d28ca8381647f7b16ad04a8f70513f1054e 100644 (file)
@@ -29,20 +29,54 @@ class GoogleTokenRepository extends Repository {
                //Set the request
                $req = <<<SQL
 SELECT
-       t.id AS tid,
-       t.mail AS gmail,
-       t.user_id AS uid,
-       t.access,
-       t.refresh,
-       t.expired,
-       GROUP_CONCAT(c.id ORDER BY c.id SEPARATOR "\\n") AS cids,
-       GROUP_CONCAT(c.mail ORDER BY c.id SEPARATOR "\\n") AS cmails,
-       GROUP_CONCAT(c.summary ORDER BY c.id SEPARATOR "\\n") AS csummaries,
-       GROUP_CONCAT(IFNULL(c.synchronized, 'NULL') ORDER BY c.id SEPARATOR "\\n") AS csynchronizeds
-FROM Rapsys\AirBundle\Entity\GoogleToken AS t
-JOIN Rapsys\AirBundle\Entity\GoogleCalendar AS c ON (c.google_token_id = t.id)
-GROUP BY t.id
-ORDER BY NULL
+       b.tid,
+       b.gmail,
+       b.uid,
+       b.access,
+       b.refresh,
+       b.created,
+       b.expired,
+       b.cids,
+       b.cmails,
+       b.csummaries,
+       b.csynchronizeds,
+       b.dids,
+       GROUP_CONCAT(us.subscribed_id ORDER BY us.subscribed_id SEPARATOR "\\n") AS sids
+FROM (
+       SELECT
+               a.tid,
+               a.gmail,
+               a.uid,
+               a.access,
+               a.refresh,
+               a.created,
+               a.expired,
+               a.cids,
+               a.cmails,
+               a.csummaries,
+               a.csynchronizeds,
+               GROUP_CONCAT(ud.dance_id ORDER BY ud.dance_id SEPARATOR "\\n") AS dids
+       FROM (
+               SELECT
+                       t.id AS tid,
+                       t.mail AS gmail,
+                       t.user_id AS uid,
+                       t.access,
+                       t.refresh,
+                       t.created,
+                       t.expired,
+                       GROUP_CONCAT(c.id ORDER BY c.id SEPARATOR "\\n") AS cids,
+                       GROUP_CONCAT(c.mail ORDER BY c.id SEPARATOR "\\n") AS cmails,
+                       GROUP_CONCAT(c.summary ORDER BY c.id SEPARATOR "\\n") AS csummaries,
+                       GROUP_CONCAT(IFNULL(c.synchronized, 'NULL') ORDER BY c.id SEPARATOR "\\n") AS csynchronizeds
+               FROM Rapsys\AirBundle\Entity\GoogleToken AS t
+               JOIN Rapsys\AirBundle\Entity\GoogleCalendar AS c ON (c.google_token_id = t.id)
+               GROUP BY t.id
+               ORDER BY NULL
+       ) AS a
+       LEFT JOIN Rapsys\AirBundle\Entity\UserDance AS ud ON (ud.user_id = a.uid)
+) AS b
+LEFT JOIN Rapsys\AirBundle\Entity\UserSubscription AS us ON (us.user_id = b.uid)
 SQL;
 
                //Replace bundle entity name by table name
@@ -61,11 +95,14 @@ SQL;
                        ->addScalarResult('uid', 'uid', 'integer')
                        ->addScalarResult('access', 'access', 'string')
                        ->addScalarResult('refresh', 'refresh', 'string')
+                       ->addScalarResult('created', 'created', 'datetime')
                        ->addScalarResult('expired', 'expired', 'datetime')
                        ->addScalarResult('cids', 'cids', 'string')
                        ->addScalarResult('cmails', 'cmails', 'string')
                        ->addScalarResult('csummaries', 'csummaries', 'string')
                        ->addScalarResult('csynchronizeds', 'csynchronizeds', 'string')
+                       ->addScalarResult('dids', 'dids', 'string')
+                       ->addScalarResult('sids', 'sids', 'string')
                        ->addIndexByScalar('tid');
 
                //Set result array
@@ -97,11 +134,14 @@ SQL;
                                'uid' => $token['uid'],
                                'access' => $token['access'],
                                'refresh' => $token['refresh'],
+                               'created' => $token['created'],
                                'expired' => $token['expired'],
-                               'calendars' => []
+                               'calendars' => [],
+                               'dances' => [],
+                               'subscriptions' => []
                        ];
 
-                       //Iterate on 
+                       //Iterate on calendars
                        foreach($cids as $k => $cid) {
                                $result[$tid]['calendars'][$cid] = [
                                        'id' => $cid,
@@ -110,6 +150,26 @@ SQL;
                                        'synchronized' => $csynchronizeds[$k]
                                ];
                        }
+
+                       //Set dids
+                       $dids = explode("\n", $token['dids']);
+
+                       //Iterate on dances
+                       foreach($dids as $k => $did) {
+                               $result[$tid]['dances'][$did] = [
+                                       'id' => $did
+                               ];
+                       }
+
+                       //Set sids
+                       $sids = explode("\n", $token['sids']);
+
+                       //Iterate on subscriptions
+                       foreach($sids as $k => $sid) {
+                               $result[$tid]['subscriptions'][$sid] = [
+                                       'id' => $sid
+                               ];
+                       }
                }
 
                //Return result