intaro/native-query-builder-bundle

Native query builder for doctrine

IntaroNativeQueryBuilderBundle

About

Extension for doctrine entity manager - adds createNativeQueryBuilder method. A NativeQueryBuilder provides an API that is designed for conditionally constructing a SQL query in several steps., _(*1)

Installation

Require the bundle in your composer.json file:, _(*2)

```` { "require": { "intaro/native-query-builder-bundle": "dev-master", } }, <sub>(*3)</sub>

Register the bundle:

```php
// app/AppKernel.php

public function registerBundles()
{
    $bundles = array(
        new Intaro\NativeQueryBuilderBundle\IntaroNativeQueryBuilderBundle(),
    );
}

Install the bundle:, <sub>(*4)</sub>

$ composer update intaro/native-query-builder-bundle

Usage

Create a NativeQueryBuilder instance:, <sub>(*5)</sub>

    $builder = $this->getDoctrine()->getManager()->createNativeQueryBuilder();

Simple select:, <sub>(*6)</sub>

    $builder->select('user.*')
        ->from('user user')
        ->join('JOIN article article', 'article.user_id = user.id')
        ->where('article.date <= ?', new DateTime())
        ->orderBy('user.name', 'DESC')
        ->limit(20)
        ->page(2);

Create resultSetMapping and get results:, <sub>(*7)</sub>

    $rsm = new ResultSetMappingBuilder($this->getDoctrine()->getManager());
    $rsm->addRootEntityFromClassMetadata('AsozdGdDataBundle:Planning\PD', 'pd');

    $users = $builder->getQuery($rsm)->getResult();

Methods list

class NativeQueryBuilder
{
    // Example $builder->select('user.*');
    // Example $builder->select('user.*, article.id');
    public function select($select)

    // Clears select statement
    public function clearSelect()

    // Example $builder->from('user user');
    public function from($from)

    // Example $builder->join('JOIN article article', 'article.user_id = user.id');
    public function join($table, $joinOn)

    // Example $builder->where('article.date <= ?', new DateTime())
    // Example $builder->where('article.active = TRUE')
    //
    // Example $builder->where('article.active = TRUE', null, true)
    //      ->where('article.date <= ?', new DateTime(), true)
    //      ->where('article.published = TRUE')
    // Result query: WHERE (article.active = TRUE OR article.date <= NOW) AND 'article.published = TRUE'
    public function where($where, $parameter = null, $or = false)

    // Example $builder->orderBy('article.date', 'DESC')
    //      ->orderBy('article.publish_date', 'DESC')
    public function orderBy($field, $direction = 'DESC')

    public function limit($limit)

    public function page($page)

    // If cacheTime = 0 cache is disabled
    // ResetParameters if true - after getQuery all statements (select, from, join, where ...) will be cleared
    public function getQuery(ResultSetMapping $rsm, $cacheTime = self::CACHE_TIME, $resetParameters = true)

More examples

Complex example with getting entities count:, <sub>(*8)</sub>

    $builder = $this->getDoctrine()->getManager()->createNativeQueryBuilder();
    $builder->from('document document')
        ->join('JOIN action action', 'action.document_id = document.id')
        ->join('JOIN document_type documentType', 'document.type_id = documentType.id');

        if ($type == 'protocol')
        {
            $builder->join('LEFT JOIN protocol protocol', 'protocol.id = action.protocol_id')
                ->where('EXISTS (SELECT 1 FROM protocol_item protocol_item
                        WHERE protocol_item.protocol_id = protocol.id)', null, true)
                ->where('EXISTS (SELECT 1 FROM action_document action_document
                        WHERE action_document.action_id = action.id AND
                            documentType.id = ?)', DocumentType::PROTOCOL, true);
        }

        $rsm = new ResultSetMappingBuilder($this->getDoctrine()->getManager());
        $rsm->addScalarResult('cnt', 'count');
        $itemsCount = $builder->select('count(document.id) as cnt')->getQuery($rsm, 3600, false)->getSingleScalarResult();

        $rsm = new ResultSetMappingBuilder($this->getDoctrine()->getManager());
        $rsm->addRootEntityFromClassMetadata('AsozdGdDataBundle:Planning\document', 'document');
        $items = $builder->clearSelect()->select('document.*')
            ->orderBy('document.start_date', 'DESC')
            ->limit(20)->page(1)->getQuery($rsm, 3600)->getResult();