*/ class BookRepository extends ServiceEntityRepository { public function __construct(ManagerRegistry $registry) { parent::__construct($registry, Book::class); } public function createListQueryBuilder(): QueryBuilder { // Book::$borrow is the inverse side of a one-to-one, so Doctrine would otherwise load it // row by row. Labels are batch-loaded through their EAGER mapping. return $this->createQueryBuilder('b') ->addSelect('br') ->leftJoin('b.borrow', 'br') ->orderBy('b.id', 'ASC'); } public function createSearchQueryBuilder(BookSearch $search): QueryBuilder { $builder = $this->createListQueryBuilder(); $fields = [ 'title' => $search->title, 'author' => $search->author, 'edition' => $search->edition, 'publisher' => $search->publisher, ]; foreach ($fields as $field => $value) { if (trim((string)$value) !== '') { $builder ->andWhere(\sprintf("b.%s LIKE :%s ESCAPE '!'", $field, $field)) ->setParameter($field, self::containsPattern($value)); } } return $builder; } /** * Quick search: an ID or ISBN/ISSN matches exactly, anything else word by word. */ public function createQuickSearchQueryBuilder(string $query): QueryBuilder { $builder = $this->createListQueryBuilder(); $words = preg_split('/\s+/u', trim($query), flags: \PREG_SPLIT_NO_EMPTY) ?: []; foreach ($words as $index => $word) { $conditions = [ \sprintf("b.title LIKE :word%d ESCAPE '!'", $index), \sprintf("b.author LIKE :word%d ESCAPE '!'", $index), \sprintf("b.publisher LIKE :word%d ESCAPE '!'", $index), ]; $builder->setParameter('word' . $index, self::containsPattern($word)); if (\count($words) === 1) { $conditions[] = 'EXISTS (SELECT 1 FROM App\Entity\Label n WHERE n.book = b AND n.type <> :textType AND n.label = :number)'; $builder ->setParameter('textType', LabelType::TEXT->value) ->setParameter('number', LabelType::ISBN->normalize($word)); if (ctype_digit($word) && \strlen($word) <= 9) { $conditions[] = 'b.id = :id'; $builder->setParameter('id', (int)$word); } } $builder->andWhere($builder->expr()->orX(...$conditions)); } return $builder; } /** * @param list $ids * @return list */ public function findForInventorySync(array $ids): array { return $this->createListQueryBuilder() ->andWhere('b.id IN (:ids)') ->setParameter('ids', $ids) ->getQuery() ->getResult(); } /** * Books on the shelves (not lent out) that were not seen during an inventory on or after * $since, including those never seen at all. */ public function createNotSeenSinceQueryBuilder(\DateTimeImmutable $since): QueryBuilder { return $this->createListQueryBuilder() ->andWhere('b.lastSeen IS NULL OR b.lastSeen < :since') ->andWhere('br.book IS NULL') ->setParameter('since', $since, Types::DATETIME_IMMUTABLE); } public function countNotSeenSince(\DateTimeImmutable $since, bool $borrowed = false): int { return (int)$this->createQueryBuilder('b') ->select('COUNT(b.id)') ->leftJoin('b.borrow', 'br') ->andWhere('b.lastSeen IS NULL OR b.lastSeen < :since') ->andWhere($borrowed ? 'br.book IS NOT NULL' : 'br.book IS NULL') ->setParameter('since', $since, Types::DATETIME_IMMUTABLE) ->getQuery() ->getSingleScalarResult(); } /** * A short fingerprint of exactly which books createNotSeenSinceQueryBuilder() selects. */ public function notSeenSinceFingerprint(\DateTimeImmutable $since): string { $ids = $this->createNotSeenSinceQueryBuilder($since) ->select('b.id') ->getQuery() ->getSingleColumnResult(); return hash('sha256', implode(',', $ids)); } /** * Everything the offline inventory page needs, as plain arrays: hydrating thousands of books * with their labels would be far slower. * * @return list, issn: list}> */ public function findInventoryData(): array { $entityManager = $this->getEntityManager(); $numbers = []; $labels = $entityManager->createQuery('SELECT IDENTITY(l.book) AS book, l.type, l.label FROM App\Entity\Label l WHERE l.type IN (:types) ORDER BY l.label') ->setParameter('types', [LabelType::ISBN->value, LabelType::ISSN->value]) ->getArrayResult(); foreach ($labels as $label) { $numbers[(int)$label['book']][$label['type']->value][] = $label['label']; } $rows = $this->createQueryBuilder('b') ->select('b.id, b.title, b.author, b.placement, b.lastSeen, IDENTITY(br.book) AS borrowedBook') ->leftJoin('b.borrow', 'br') ->orderBy('b.id', 'ASC') ->getQuery() ->getArrayResult(); return array_map(static fn(array $row): array => [ 'id' => $row['id'], 'title' => $row['title'], 'author' => $row['author'], 'placement' => $row['placement'], 'lastSeen' => $row['lastSeen']?->format(\DateTimeInterface::ATOM), 'borrowed' => $row['borrowedBook'] !== null, 'isbn' => $numbers[$row['id']][LabelType::ISBN->value] ?? [], 'issn' => $numbers[$row['id']][LabelType::ISSN->value] ?? [], ], $rows); } private static function containsPattern(string $value): string { return '%' . strtr(trim($value), ['!' => '!!', '%' => '!%', '_' => '!_']) . '%'; } }