/** * @see Console\Command\Command */ protected function execute(Console\Input\InputInterface $input, Console\Output\OutputInterface $output) { $em = $this->getApplication()->getKernel()->getContainer()->getService('em'); $sessionDiff = 3600 * 48; $requestTimeDiff = 3600; // select sections older than 48 hours $rsm = new \Doctrine\ORM\Query\ResultSetMapping(); $rsm->addEntityResult('\\Newscoop\\Entity\\Session', 's'); $rsm->addFieldResult('s', 'id', 'id'); $rsm->addFieldResult('s', 'start_time', 'start_time'); $rsm->addFieldResult('s', 'user_id', 'user_id'); $rsm->addJoinedEntityResult('\\Newscoop\\Entity\\Request', 'r', 's', null); $rsm->addFieldResult('r', 'last_stats_update', 'last_stats_update'); $rsm->addFieldResult('r', 'session_id', 'session'); $rsm->addScalarResult('last_update_diff', 'last_update_diff'); $sql = "SELECT\r\n s.id,\r\n s.start_time,\r\n s.user_id,\r\n MIN(TIME_TO_SEC(TIMEDIFF(NOW(), r.last_stats_update))) AS last_update_diff\r\n FROM\r\n Sessions s\r\n LEFT JOIN\r\n Requests r ON s.id = r.session_id\r\n WHERE\r\n TIME_TO_SEC(TIMEDIFF(NOW(), s.start_time)) >= {$sessionDiff}\r\n "; $query = $em->createNativeQuery($sql, $rsm); $sessions = $query->getResult(); if (count($sessions) == 0 || $sessions[0][0] == null) { $output->writeln('<error>There is nothing to remove.</error>'); return; } foreach ($sessions as $session) { if ($session['last_update_diff'] < $requestTimeDiff) { // if there was a request for this session less than one hour ago do not process the session continue; } foreach ($session[0]->getRequests() as $request) { $em->remove($request); $output->writeln('<info>Request for session with id: ' . $session[0]->getId() . ' was removed.</info>'); } $em->remove($session[0]); $output->writeln('<info>Session with id: ' . $session[0]->getId() . ' was removed.</info>'); } $em->flush(); return true; }