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