How can I find first lower date than given from database. I need find date - 30 days than today date in database if this date not exist I need get first lower date than.
2017-08-30
2017-08-23
2017-08-16
2017-08-09
2017-08-02
2017-07-26
2017-07-19
For example my today data is
2017-08-30
so I need find date -30 days from 2017-08-30
(it will be 31.07.2017)
if this data not exist in database I need to take first lower date than 31.07.2017
so this date will be 2017-07-26
I tried:
$lastMonth = date_format(new \Datetime('-30 days'), 'Y-m-d');
for($i= 0; $i<7;){
$parts = explode('-', $lastMonth);
$lastMonth = date('Y-m-d', mktime(0, 0, 0, $parts[1], $parts[2] - $i, $parts[0]));
$query = $this->em->createQuery(
'SELECT sum(psc.score) / COUNT(psc.id) as avgscore, psc.date
FROM AppBundle:PageSpeedScore psc
WHERE psc.projectId = :projectId
AND psc.date = :date
GROUP BY psc.date, psc.projectId
ORDER BY psc.date
'
)->setParameter('projectId', $projectId)
->setParameter('date', $lastMonth);
if(empty($query->getResult())){
$i++;
}else{
$date = $query->getResult();
break;
}
}
This return me 2017-07-26 so its work but... this is not elegant way to do this I think. I have a lot of query to database now. I think I can do it with one query. Maybe someone can help me and tell me how can I do this in only mySQL query without for loop?