Doctrine: DQL query without specifying class fully qualified name (FQN)

1
votes

Is there a way to write DQL queries without specifying the fully qualified name of the class?

Is there a way to create the following query:

return $this->getEntityManager()->createQuery(
    'SELECT i
     FROM Company\AccountingBundle\Entity\Invoice i
     WHERE i.id = :id'
)->setParameter('id', 1)
    ->getResult();

Like this :

use Company\AccountingBundle\Entity\Invoice;
....

return $this->getEntityManager()->createQuery(
    'SELECT i
     FROM Invoice i
     WHERE i.id = :id'
)->setParameter('id', 1)
    ->getResult();

Or something similar. I really would avoid to set the full FQN in the DQL.

But I didn't find any "setClass" or another method to do this.

EDIT FOR CONTEXT

In fact, it is a Symfony project and the query is inside in a Repository class. I have a Doctrine XML definition of the entity, an entity and the entity repository also defined in the XML, but I always need to explicitly define the FQN, Any ideas?

4
phpsymfonydoctrine-ormdql
You can use string concatenation: $work_class="Company\AccountingBundle\Entity\Invoice"; return $this->getEntityManager()->createQuery( 'SELECT i FROM ' . $work_class . ' i WHERE i.id = :id' )->setParameter('id', 1) ->getResult(); – Adam P.
Off the top of my head, build your query string with heredoc or through sprintf and just pass Invoice::class for the placeholder value. – El_Vanja
An alternative would be to use repositories, as a repository is always tied to an entity so you don't need to explicitly state them. But I don't know the state of your application or the requirements, so this would take research on your end. – El_Vanja
@El_Vanja thanks for your comment and you are right, I didn't explain the context! My bad. In fact, it is a Symfony project and the query is inside in a Repository class. I have a Doctrine XML definition of the entity, an entity and the entity repository also defined in the XML, but I always need to explicitly define the FQN, Any ideas? – sdespont
Did you take a look at alias parameter in your Doctrine configuration? An official example here – Liscare

4 Answers

4
votes

Doctrine offers a way to alias entity namespaces to simpler, shorter names to be used in DQL queries or for Repository access.

You can set the alias of the entity namespace inside the Doctrine config of .yaml

doctrine:
  orm:
    auto_generate_proxy_classes: true
    naming_strategy: doctrine.orm.naming_strategy.underscore
    auto_mapping: true
    mappings:
        Company:
            is_bundle: false
            type: annotation
            dir: '%kernel.project_dir%/src/Company/AccountingBundle/Entity'
            prefix: 'App\Company\AccountingBundle\Entity'
            alias: AC

Then in the DQL, you can use "AC:Invoice":

return $this->getEntityManager()->createQuery(
  'SELECT i
  FROM AC:Invoice i
  WHERE i.id = :id'
  )->setParameter('id', 1)
  ->getResult();

References

Symfony bundle Mapping Configuration

Symfony Doctrine ORM Configuration

1
votes

Assuming your repository class extends \Doctrine\ORM\EntityRepository

Have you tried something like?

return $this->getEntityManager()
       ->createQuery('SELECT i FROM '.$this->getEntityName().' i where i.id = :id')
       ->setParameter('id',1)
       ->getResult();
0
votes

The Doctrine way would be to use entity repositories. If your entity is configured so that it's tied to a repository, e.g.:

/**
 * @ORM\Entity(repositoryClass=InvoiceRepository::class)
 */
class Invoice

then in that repository, if you utilize a query builder, that entity is automatically tied to the query:

$qb = $this->createQueryBuilder('i') // 'i' is just an alias here
    ->where('i.id = :id')
    ->setParameter('id', $id);

To see all the options it offers, you can read more on the query builder here.



1. Using sprintf

$q = sprintf('SELECT * FROM %s WHERE id = :id', Invoice::class);

return $this->getEntityManager()
    ->createQuery($q)
    ->setParameter('id', 1)
    ->getResult();

2. Using HEREDOC syntax

$entity = Invoice::class;
$q = <<<SQL
    SELECT *
    FROM {$entity}
    WHERE id = :id
SQL;

return $this->getEntityManager()
    ->createQuery($q)
    ->setParameter('id', 1)
    ->getResult();

In both cases the output of the query variable is SELECT * FROM App\Entity\Invoice WHERE id = :id.

Note that you must initialize a variable for HEREDOC, as it cannot directly evaluate {Invoice::class}. That option would definitely be suitable for longer queries (especially since it highlights SQL keywords), while sprintf can be utilized for short, straightforward ones.

-1
votes

I apologize if you explicitly need DQL, but I haven't seen anyone mention DBAL QueryBuilder.

Here is your query, in Repository class, using DBAL QueryBuilder:

return $this->getEntityManager()
->getConnection() //
->createQueryBuilder() // This is different query builder that doesn't automatically assume Entity
->select('i')
->from('invoice', 'i') // Specify the table
->where('i.id = :id') // Rest of the syntax should be similar to DQL but you need to query against table columns, not properties (like in DQL) - for example, in DQL: ... where('firstName = :firstName') ... in DBAL: where('first_name = :firstName')
->setParameter('id', 1)
->execute()->fetchAllAssociative(); // Double check if you can fetch as stdClass

In any case, DBAL QueryBuilder is different from Doctrine Query Builder by being closer to the database layer.

More about DBAL QueryBuilder: https://www.doctrine-project.org/projects/doctrine-dbal/en/latest/reference/query-builder.html#sql-query-builder