Cacher is the code snippet organizer for pro developers

<?php

declare(strict_types=1);

/**
 * Created by PhpStorm.
 * User: Ivan Kucheriavenko
 * Date: 3/4/18
 * Time: 7:14 AM.
 */

namespace Crawler\Doctrine\DBAL;

/**
 * Class ConnectionHelper.
 */
class ConnectionHelper
{
    /**
     * Extract array of types for values on insert/update.
     *
     * @param array $values
     *
     * @return array
     */
    public function extractTypeValues(array $values): array
    {
        $types = array();

        foreach ($values as $value) {
            $types[] = $this->guessType($value);
        }

        return $types;
    }

    /**
     * @param mixed $columnValue
     *
     * @return mixed
     */
    public function guessType($columnValue)
    {
        return is_string($columnValue) ? \PDO::PARAM_STR
                    : (is_integer($columnValue) ? \PDO::PARAM_INT
                        : (is_bool($columnValue) ? \PDO::PARAM_BOOL
                            : (is_null($columnValue) ? \PDO::PARAM_NULL : null)));
    }
}

<?php

declare(strict_types=1);

/**
 * Created by PhpStorm.
 * User: ivan kucheriavenko
 * Date: 02.03.18
 * Time: 18:50.
 */

namespace Crawler\Repository;

use Crawler\Doctrine\DBAL\ConnectionHelper;
use Crawler\DTO\ParsedEntryArray;
use Crawler\Entity\ParsedEntry;
use Crawler\Entity\Product;
use DateTime;
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use Doctrine\Common\Collections\ArrayCollection;
use Doctrine\Common\Inflector\Inflector;
use Doctrine\Common\Persistence\ManagerRegistry;

/**
 * Class ParsedEntryRepository.
 */
class ParsedEntryRepository extends ServiceEntityRepository
{
    /** @var ConnectionHelper */
    private $connectionHelper;

    /**
     * {@inheritdoc}
     */
    public function __construct(ManagerRegistry $registry, ConnectionHelper $connectionHelper)
    {
        parent::__construct($registry, ParsedEntry::class);

        $this->connectionHelper = $connectionHelper;
    }

    /**
     * @param ArrayCollection $entries
     *
     * @return int
     */
    public function addOrIgnoreMultiple(ArrayCollection $entries)
    {
        $affectedCount = 0;
        if ($entries->isEmpty()) {
            return $affectedCount;
        }

        $conn = $this->_em->getConnection();
        $tableName = $this->_em->getClassMetadata(ParsedEntry::class)->getTableName();

        $entries = $entries
            ->filter(function ($entry) {
                return $entry instanceof ParsedEntry;
            })
            ->map(function ($entry) {
                /** @var ParsedEntry $entry */
                $entryArr = $entry->toArray();
                $keys = array_map(function ($key) {
                    return Inflector::tableize($key);
                }, array_keys($entryArr));
                return array_combine($keys, array_values($entryArr));
            })
            ->toArray()
        ;

        $set = [];
        $values = [];
        foreach ($entries as $index => $array) {
            $set[] = '('.implode(', ', array_fill(0, count($array), '?')).')';
            $values = array_merge($values, array_values($array));
        }

        $first = reset($entries);
        $columns = array_keys($first);

        $sql = 'INSERT IGNORE INTO '.$tableName.' ('.implode(', ', $columns).') VALUES '
            .implode(', ', $set);

        $affectedCount = $conn->executeUpdate(
            $sql,
            $values,
            $this->connectionHelper->extractTypeValues($values)
        );

        return $affectedCount;
    }

    /**
     * @param Product $product
     *
     * @return mixed
     */
    public function findAllByProductAndCreatedBetween(Product $product, DateTime $fromDate, DateTime $toDate)
    {
        $qb = $this->createQueryBuilder('pe');

        return $qb
            ->select('partial pe.{id}')
            ->where('pe.product = :product')
            ->andWhere(
                $qb->expr()->between('pe.createdAt', ':fromDate', ':toDate')
            )
            ->orderBy('pe.createdAt', 'DESC')
            ->setParameter('product', $product)
            ->setParameter('fromDate', $fromDate)
            ->setParameter('toDate', $toDate)
            ->getQuery()
            ->getResult();
    }
}