Apigility + Doctrine2 QueryProvider - Can't use created function on query builder

Question

I'm using Apigility to create a rest application, where the back-end and front-end are pretty much independent applications.

Ok, on the Back-end I'm using 'zf-apigility-doctrine-query-provider' to create queries depending on the parameters sent via url (i.e localhost?instancia=10), but I need to process information using a MS SQL database stored function, something like this:

function createQuery(ResourceEvent $event, $entityClass, $parameters){

    /* @var $queryBuilder \Doctrine\ORM\QueryBuilder */
    $queryBuilder = parent::createQuery($event,$entityClass, $parameters);

    if (!empty($parameters['instancia'])) {

        $queryBuilder->andWhere($queryBuilder->expr()->eq('chapa.instancia', 'dbo.isItSpecial(:instancia)'))
        ->setParameter('instancia', $parameters['instancia']);
    }

    return $queryBuilder;        
}

However it simply won't work, it won't accept the 'dbo.isItSpecial' and seems like I can't access the ServiceLocator, nor the EntityManager or anything but the Querybuilder.

I thought about creating a native query to get the result and the using it on the main query but seems like I can't create it.

Any ideas?

Possible duplicate of [How to execute Stored Procedures with Doctrine2 and MySQL](http://stackoverflow.com/questions/10705953/how-to-execute-stored-procedures-with-doctrine2-and-mysql) — Wilt, Oct 16 '15 at 11:16
Duplicate of [this question](http://stackoverflow.com/questions/10705953/how-to-execute-stored-procedures-with-doctrine2-and-mysql). You'll find your answer there... — Wilt, Oct 16 '15 at 11:16
I`ve seen the solution on that link, it`s similar but I`m using the apigility query provider so I won`t be able to use native query (well, at least I don`t know how to do it.) — user3320407, Oct 17 '15 at 00:08

score 0 · Answer 1 · answered Oct 20 '15 at 07:32

0

In what class is this method? Add some context to your question.

You do parent::createQuery which suggests that you are in a DoctrineResource instance. If this is true it means that both the ServiceLocator and the ObjectManager are simply available in the class.

You can read on doing native queries in Doctrine here in the Documentation:

$rsm = new ResultSetMapping();

$query = $entityManager->createNativeQuery(
    'SELECT id, name, discr FROM users WHERE name = ?', 
    $rsm
);
$query->setParameter(1, 'romanb');
$users = $query->getResult();

answered Oct 20 '15 at 07:32

Wilt

41,477
12
152
203

Oh thanks, turns out I've found some solutions to this. I'll post then soon to help others. – user3320407 Oct 20 '15 at 17:36
By the way, do you know how to I mark a question as solved? – user3320407 Oct 20 '15 at 18:12
@user3320407 you can accept one of the answers and that will close the question. – Wilt Oct 20 '15 at 18:13

score 0 · Accepted Answer · answered Oct 20 '15 at 18:07

Turns out I've found some ways to do this.

The class that the method was, extends this class

ZF\Apigility\Doctrine\Server\Query\Provider\DefaultOrm

That means I have access to the ObjectManager. The Documentation doesn't help much, but the ObjectManager is actually an EntityManager (ObjectManager is just the interface), to discover this I had to use the get_class PHP command.

With the entity manager I could have done what Wilt sugested, something like this:

        $sqlNativa = $this->getObjectManager()->createNativeQuery("Select dbo.isItSpecial(:codInstancia) as codEleicao", $rsm);

However, I created a service that execute this function (it will be used in many places), so I also made a factory that set this service to the query provider class, on the configuration file it's something like this.

    'zf-apigility-doctrine-query-provider' => array(
    'factories' => array(
        'instanciaDefaultQuery' => 'Api\Instancia\instanciaQueryFactory',
    ),
),

And the factory looks like something like this (the service is executing the NativeQuery like the response from Wilt):

use Zend\ServiceManager\FactoryInterface;
use Zend\ServiceManager\ServiceLocatorInterface;
use Api\EleitoChapaOrgao\EleitoChapaOrgaoQuery;

class InstanciaQueryFactory implements FactoryInterface
{
    public function createService(ServiceLocatorInterface $serviceManager){
        $instanciaService = $serviceManager->getServiceLocator()->get('Application\Service\Instancia');

        $query = new InstanciaQuery($instanciaService);

        return $query;
    }
}

Finally just add the constructor to the QueryProvider and the service will be avaliable there:

class InstanciaQuery extends DefaultOrm
{    
    protected $instanciaService;

    public function __construct(Instancia $instanciaService)
    {
        $this->instanciaService = $instanciaService;
    }

    public function createQuery(ResourceEvent $event, $entityClass, $parameters)    
    { /* The rest of the code goes here*/

Apigility + Doctrine2 QueryProvider - Can't use created function on query builder

2 Answers2