CODEWITHSUNDEEP
symfonydoctrine
Romain Rnd
Romain Rnd

Reputation: 551

Symfony query with Doctrine

I'm working on a little exercise to improve myself with Symfony 3. I have got a mistake in building a query to get adverts not linked to applications and on which the date is older than 30 days.

I have created a new method in my controller, a new query in my advertRepository and a service named "Purger" in which I have to delete these old adverts.

I paste you the code just below (controller, service class, AdvertRepository, services.yml) : 

public function purgeAction($days) 
{
  // Appel du service
  $testPurge = $this->get('oc_platform.purger.advert');
  $testPurge->purge($days);

  return new Response("Purge done !");
}

namespace OC\PlatformBundle\Purger\Advert;

use OC\PlatformBundle\Entity\Advert;
use Doctrine\ORM\EntityManager;

class OCAdvertsPurger 
 {
  private $em;

  public function __construct(EntityManager $em)
  {
    $this->em = $em;
  }

  //Ce service va récupérer et supprimer toutes les annonces dont la date de 
  modification
  // est plus vieille que X jours. Ce "X" est le paramètre de la méthode.
  public function purge($days)
   {
     $listAdverts = $this->em->getRepository('OCPlatformBundle:Advert')-
     >getOldAdvertsOnly($days);
   }
 }


public function getOldAdvertsOnly($days)
{
  // Liste de toutes les annonces
  $qb = $this->createQueryBuilder('a')
    ->leftJoin('a.applications', "app")
    ->addSelect('app')
  ;

  // On retire celles attachées à des candidatures
  $qb->where($qb->expr()->isNull('app.advert_id'));

  return $qb
    ->getQuery()
    ->getResult()
  ;
}

public function deleteOldAdvertsOnly($adverts)
{ 
  $qb = $this->createQuery('DELETE $adverts FROM 
  OC\PlatformBundle\Entity\Advert  WHERE DATE_DIFF(CURRENT_DATE(), 
  updated_at) > $days');

  return $qb
    ->getQuery()
    ->getResut()
  ;
}
    services:
        oc_platform.purger.advert:
        class: OC\PlatformBundle\Purger\Advert\OCAdvertsPurger
        arguments: 
            - "@doctrine.orm.entity_manager"

I tried to do something with the DATE_DIFF function() in the same query with queryBuilder but there was a mistake because queryBuilder() doesn't know this function as CURRENT_DATE() I think so I was trying to make it work with two different queries (one with queryBuilder and one with the DQL langage).

Really thanks for going to help me !

Upvotes: 0

Views: 1995

Answers (3)

Romain Rnd
Romain Rnd

Reputation: 551

Thank you guys ! I just modified my query and it worked ! I have to bear in mind your trick about adding SQL functions to Doctrine :)

Upvotes: 0

Flo
Flo

Reputation: 356

To use DATEDIFF function (or any other) you have to install Doctrine Extension :

composer require beberlei/DoctrineExtensions (https://github.com/beberlei/DoctrineExtensions)

and in your config.yml :

orm:
 entity_managers:
  default:
    dql:
      datetime_functions:
        DateDiff: DoctrineExtensions\Query\Mysql\DateDiff

You can now use DATEDIFF in your query builder as you would do in SQL.

Functions available :

MySQL :

ACOS, AES_DECRYPT, AES_ENCRYPT, ANY_VALUE, ASCII, ASIN, ATAN, ATAN2, BINARY, BIT_COUNT, BIT_XOR, CEIL, CHAR_LENGTH, COLLATE, CONCAT_WS, CONVERT_TZ, COS, COT, COUNTIF, CRC32, DATE, DATE_FORMAT, DATEADD, DATEDIFF, DATESUB, DAY, DAYNAME, DAYOFWEEK, DAYOFYEAR, DEGREES, DIV, EXP, EXTRACT, FIELD, FIND_IN_SET, FLOOR, FROM_UNIXTIME, GREATEST, GROUP_CONCAT, HEX, HOUR, IFELSE, IFNULL, LAST_DAY, LEAST, LOG, LOG10, LOG2, LPAD, MATCH, MD5, MINUTE, MONTH, MONTHNAME, NOW, NULLIF, PI, POWER, QUARTER, RADIANS, RAND, REGEXP, REPLACE, ROUND, RPAD, SECOND, SECTOTIME, SHA1, SHA2, SIN, SOUNDEX, STD, STDDEV, STRTODATE, STR_TO_DATE, SUBSTRING_INDEX, TAN, TIME, TIMEDIFF, TIMESTAMPADD, TIMESTAMPDIFF, TIMETOSEC, UNHEX, UNIX_TIMESTAMP, UTC_TIMESTAMP, UUID_SHORT, VARIANCE, WEEK, WEEKDAY, YEAR, YEARWEEK

Oracle

DAY, LISTAGG, MONTH, NVL, TO_CHAR, TO_DATE, TRUNC, YEAR

Sqlite

DATE, MINUTE, HOUR, DAY, WEEK, WEEKDAY, MONTH, YEAR, STRFTIME, DATE_FORMAT*, CASE WHEN THEN ELSE END, IFNULL, REPLACE, ROUND

PostgreSQL

TO_DATE, TO_CHAR, AT_TIME_ZONE, COUNT_FILTER, STRING_AGG

Upvotes: 1

Sergei Kutanov
Sergei Kutanov

Reputation: 825

I think you can do this in just one query. Which would looks smt like this:

$dateThreshold = new \DateTime();
$dateThreshold->modify("-30 days");
$qb = $this->createQueryBuilder('add')
    ->delete('add')
    ->where("add.app_id IS NULL")
    ->andWhere("add.updated_at <= :date_threshold")
    ->setParameter("date_threshold", $dateThreshold)
    ->getQuery()->execute()

I'm not familiar with your db structure, but you should get an idea frmo the example above.

Upvotes: 0

Related Questions