namespace Rapsys\AirBundle\Repository;
+use Doctrine\ORM\AbstractQuery;
use Doctrine\ORM\Query\ResultSetMapping;
+
use Symfony\Component\Routing\Generator\UrlGeneratorInterface;
+use Rapsys\AirBundle\Repository;
+
/**
* UserRepository
*/
-class UserRepository extends EntityRepository {
+class UserRepository extends Repository {
/**
* Find users with translated highest group and civility
*
* @return array The user ids keyed by group and pseudonym
*/
- public function findIndexByGroupPseudonym(): array {
+ public function findChoicesAsArray(): array {
//Set the request
$req =<<<SQL
SELECT
u.pseudonym,
g.id AS g_id,
g.title AS g_title
- FROM RapsysAirBundle:User AS u
- LEFT JOIN RapsysAirBundle:UserGroup AS gu ON (gu.user_id = u.id)
- LEFT JOIN RapsysAirBundle:Group AS g ON (g.id = gu.group_id)
- ORDER BY g.id DESC
+ FROM Rapsys\AirBundle\Entity\User AS u
+ JOIN Rapsys\AirBundle\Entity\UserGroup AS gu ON (gu.user_id = u.id)
+ JOIN Rapsys\AirBundle\Entity\Group AS g ON (g.id = gu.group_id)
+ WHERE g.title <> 'User'
+ ORDER BY g.id DESC, u.pseudonym ASC
LIMIT 0, :limit
) AS a
GROUP BY a.id
//Process result
foreach($res as $data) {
//Without group or simple user
- if (empty($data['g_title']) || $data['g_title'] == 'User') {
- //Skip it
- continue;
- }
+ #XXX: moved in sql by removing LEFT JOIN and excluding user group
+ #if (empty($data['g_title']) || $data['g_title'] == 'User') {
+ # //Skip it
+ # continue;
+ #}
//Get translated group
$group = $this->translator->trans($data['g_title']);
//Set data
//XXX: ChoiceType use display string as key
- $ret[$group][$data['pseudonym']] = $data['id'];
+ $ret[$group][trim($data['pseudonym'].' ('.$data['id'].')')] = intval($data['id']);
}
//Send result
return $ret;
}
+ /**
+ * Find user ids by pseudonym
+ *
+ * @param array $pseudonym The pseudonym filter
+ * @return array The user ids
+ */
+ public function findIdByPseudonymAsArray(array $pseudonym): array {
+ //Set the request
+ $req =<<<SQL
+SELECT
+ a.id
+FROM (
+ SELECT
+ u.id
+ FROM Rapsys\AirBundle\Entity\User AS u
+ LEFT JOIN Rapsys\AirBundle\Entity\UserGroup AS gu ON (gu.user_id = u.id)
+ WHERE u.pseudonym IN (:pseudonym)
+ ORDER BY gu.group_id DESC, u.pseudonym ASC
+ LIMIT 0, :limit
+) AS a
+GROUP BY a.id
+ORDER BY NULL
+SQL;
+
+ //Replace bundle entity name by table name
+ $req = str_replace($this->tableKeys, $this->tableValues, $req);
+
+ //Get result set mapping instance
+ //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
+ $rsm = new ResultSetMapping();
+
+ //Declare all fields
+ //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
+ //XXX: we don't use a result set as we want to translate group and civility
+ $rsm->addScalarResult('id', 'id', 'integer');
+
+ //Return result
+ return $this->_em
+ ->createNativeQuery($req, $rsm)
+ ->setParameter('pseudonym', $pseudonym)
+ //XXX: instead of array_column on the result
+ ->getResult(AbstractQuery::HYDRATE_SCALAR_COLUMN);
+ }
+
/**
* Find applicant by session id
*
//Set the request
$req =<<<SQL
SELECT u.id, u.pseudonym
-FROM RapsysAirBundle:Application AS a
-JOIN RapsysAirBundle:User AS u ON (u.id = a.user_id)
+FROM Rapsys\AirBundle\Entity\Application AS a
+JOIN Rapsys\AirBundle\Entity\User AS u ON (u.id = a.user_id)
WHERE a.session_id = :id
SQL;
c.title AS c_title,
u.country_id AS o_id,
o.title AS o_title,
+ GREATEST(u.created, u.updated, COALESCE(c.created, '1970-01-01'), COALESCE(c.updated, '1970-01-01'), COALESCE(o.created, '1970-01-01'), COALESCE(o.updated, '1970-01-01'), COALESCE(g.created, '1970-01-01'), COALESCE(g.updated, '1970-01-01')) AS modified,
GROUP_CONCAT(g.id ORDER BY g.id SEPARATOR "\\n") AS ids,
- GROUP_CONCAT(g.title ORDER BY g.id SEPARATOR "\\n") AS titles,
- GREATEST(COALESCE(u.updated, 0), COALESCE(c.updated, 0), COALESCE(o.updated, 0)) AS modified
-FROM RapsysAirBundle:User AS u
-LEFT JOIN RapsysAirBundle:Civility AS c ON (c.id = u.civility_id)
-LEFT JOIN RapsysAirBundle:Country AS o ON (o.id = u.country_id)
-LEFT JOIN RapsysAirBundle:UserGroup AS gu ON (gu.user_id = u.id)
-LEFT JOIN RapsysAirBundle:Group AS g ON (g.id = gu.group_id)
+ GROUP_CONCAT(g.title ORDER BY g.id SEPARATOR "\\n") AS titles
+FROM Rapsys\AirBundle\Entity\User AS u
+LEFT JOIN Rapsys\AirBundle\Entity\Civility AS c ON (c.id = u.civility_id)
+LEFT JOIN Rapsys\AirBundle\Entity\Country AS o ON (o.id = u.country_id)
+LEFT JOIN Rapsys\AirBundle\Entity\UserGroup AS gu ON (gu.user_id = u.id)
+LEFT JOIN Rapsys\AirBundle\Entity\Group AS g ON (g.id = gu.group_id)
WHERE u.id = :id
SQL;
->addScalarResult('c_title', 'c_title', 'string')
->addScalarResult('o_id', 'o_id', 'integer')
->addScalarResult('o_title', 'o_title', 'string')
+ ->addScalarResult('modified', 'modified', 'datetime')
//XXX: is a string because of \n separator
->addScalarResult('ids', 'ids', 'string')
//XXX: is a string because of \n separator
->addScalarResult('titles', 'titles', 'string')
- ->addScalarResult('modified', 'modified', 'datetime')
->addIndexByScalar('id');
//Get result
$result['alternates'] = [];
//Set route
- $route = 'rapsys_air_user_view';
+ $route = 'rapsysair_user_view';
//Set route params
$routeParams = ['id' => $id, 'user' => $this->slugger->slug($result['pseudonym'])];
//Milonga Raphaël exception
if ($routeParams['id'] == 1 && $routeParams['user'] == 'milonga-raphael') {
//Set route
- $route = 'rapsys_air_user_milongaraphael';
+ $route = 'rapsysair_user_milongaraphael';
//Set route params
$routeParams = [];
}
//Iterate on each location
foreach(explode("\n", $result['ids']) as $k => $id) {
//Add role
- $roles[$role = 'ROLE_'.strtoupper($titles[$k])] = $role;
+ //XXX: roles are keyes by id
+ $roles[$id] = 'ROLE_'.strtoupper($titles[$k]);
//Add group
$groups[$id] = $this->translator->trans($titles[$k]);
u.pseudonym,
g.id AS g_id,
g.title AS g_title
- FROM RapsysAirBundle:User AS u
- JOIN RapsysAirBundle:UserGroup AS gu ON (gu.user_id = u.id)
- JOIN RapsysAirBundle:Group AS g ON (g.id = gu.group_id)
+ FROM Rapsys\AirBundle\Entity\User AS u
+ JOIN Rapsys\AirBundle\Entity\UserGroup AS gu ON (gu.user_id = u.id)
+ JOIN Rapsys\AirBundle\Entity\Group AS g ON (g.id = gu.group_id)
ORDER BY NULL
LIMIT 0, :limit
) AS c
- LEFT JOIN RapsysAirBundle:Application AS a ON (a.user_id = c.id)
- LEFT JOIN RapsysAirBundle:Dance AS d ON (d.id = a.dance_id)
+ LEFT JOIN Rapsys\AirBundle\Entity\Application AS a ON (a.user_id = c.id)
+ LEFT JOIN Rapsys\AirBundle\Entity\Dance AS d ON (d.id = a.dance_id)
GROUP BY d.id
ORDER BY NULL
LIMIT 0, :limit
'dances' => [],
'slug' => $slug = $this->slugger->slug($data['pseudonym']),
//Milonga Raphaël exception
- 'link' => $data['id'] == 1 && $slug == 'milonga-raphael' ? $this->router->generate('rapsys_air_user_milongaraphael', []) : $this->router->generate('rapsys_air_user_view', ['id' => $data['id'], 'user' => $slug]),
- 'edit' => $this->router->generate('rapsys_user_edit', ['mail' => $short = $this->slugger->short($data['mail']), 'hash' => $this->slugger->hash($short)])
+ 'link' => $data['id'] == 1 && $slug == 'milonga-raphael' ? $this->router->generate('rapsysair_user_milongaraphael', []) : $this->router->generate('rapsysair_user_view', ['id' => $data['id'], 'user' => $slug]),
+ 'edit' => $this->router->generate('rapsysuser_edit', ['mail' => $short = $this->slugger->short($data['mail']), 'hash' => $this->slugger->hash($short)])
];
//With dances
//Init dance when missing
if (!isset($ret[$group][$data['id']]['dances'][$name = $this->translator->trans($names[$k])])) {
$ret[$group][$data['id']]['dances'][$name] = [
- 'link' => $this->router->generate('rapsys_air_dance_name', ['name' => $this->slugger->short($names[$k]), 'dance' => $this->slugger->slug($name)]),
+ 'link' => $this->router->generate('rapsysair_dance_name', ['name' => $this->slugger->short($names[$k]), 'dance' => $this->slugger->slug($name)]),
'types' => []
];
}
//Set type
- $ret[$group][$data['id']]['dances'][$name]['types'][$type = $this->translator->trans($types[$k])] = $this->router->generate('rapsys_air_dance_view', ['id' => $id, 'name' => $this->slugger->slug($name), 'type' => $this->slugger->slug($type)]);
+ $ret[$group][$data['id']]['dances'][$name]['types'][$type = $this->translator->trans($types[$k])] = $this->router->generate('rapsysair_dance_view', ['id' => $id, 'name' => $this->slugger->slug($name), 'type' => $this->slugger->slug($type)]);
}
}
}
Rapsys Git / airbundle / blobdiff