Repositories and queries

The findBy() methods are enough for simple conditions. Searching by part of a string, "greater" and "less" comparisons and counts are written in the repository — the class that gathers every query for one entity. The controller stays short.

Your own method

// src/Repository/PostRepository.php
namespace App\Repository;
 
use App\Entity\Post;
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
 
class PostRepository extends ServiceEntityRepository
{
    /** @return Post[] */
    public function findPublished(): array
    {
        return $this->createQueryBuilder('p')
            ->andWhere('p.published = true')
            ->getQuery()
            ->getResult();
    }
}

p is a short name for the entity in the query. Conditions use the names of class properties, not table columns.

Parameters

Values are supplied through parameters — that protects against SQL injection:

public function search(string $text): array
{
    return $this->createQueryBuilder('p')
        ->andWhere('p.title LIKE :text')
        ->setParameter('text', '%' . $text . '%')
        ->getQuery()
        ->getResult();
}

Ordering and limiting

public function findLatest(int $limit = 10): array
{
    return $this->createQueryBuilder('p')
        ->andWhere('p.published = true')
        ->orderBy('p.createdAt', 'DESC')
        ->setMaxResults($limit)
        ->getQuery()
        ->getResult();
}

For pagination add setFirstResult() — how many records to skip.

What to return

Method	Result
`getResult()`	An array of objects
`getOneOrNullResult()`	One object or `null`
`getSingleScalarResult()`	A single value: a number, a string
`getArrayResult()`	Arrays instead of objects

Counting

public function countPublished(): int
{
    return (int) $this->createQueryBuilder('p')
        ->select('COUNT(p.id)')
        ->andWhere('p.published = true')
        ->getQuery()
        ->getSingleScalarResult();
}

Conditions as needed

A query is assembled piece by piece — handy for filters:

public function filter(?string $text, ?int $year): array
{
    $query = $this->createQueryBuilder('p');
 
    if ($text) {
        $query->andWhere('p.title LIKE :text')
            ->setParameter('text', "%$text%");
    }
 
    if ($year) {
        $query->andWhere('p.year = :year')
            ->setParameter('year', $year);
    }
 
    return $query->getQuery()->getResult();
}

DQL

The same query can be written as a string in the DQL language. It resembles SQL but works with entities:

$posts = $this->getEntityManager()
    ->createQuery('SELECT p FROM App\Entity\Post p WHERE p.published = true ORDER BY p.id DESC')
    ->getResult();

Common mistake

A value must not be put into the text of a condition: ->andWhere("p.title = '$title'") opens the way to SQL injection. Always use :name and setParameter(). A second slip: where() erases the conditions added before it, so use andWhere() when building in pieces.

Thanks for reading!