Writing custom Queries in TYPO3 Flow

Want to write a custom query? Doctrine's standard Query class does not provide us with much flexibility when multiple tables are to be queried.

  • One option to write a query where you will have to join tables is use the classic DQL. http://docs.doctrine-project.org/en/latest/reference/dql-doctrine-query-language.html provides very good documentation on how to build a DQL query. 
  • Second option is use the query builder. Query Builder returns a Query Result Interface. 

    Let us see how to use a query builder.
    I have the following 3 domain models. I need a list of all customers who live in India.
class Customer extends \Neos\Party\Domain\Model\Person {

    /**
     * The status
*
     * @var integer
     * @ORM\Column(nullable=true)
     */
    protected $status;

    /**
     * The addresses
*
     * @var \Doctrine\Common\Collections\Collection<\Lelesys\App\Domain\Model\Address>
     * @ORM\ManyToMany(cascade={"persist"})
     */
    protected $addresses;
class Address {

    /**
     * The street
*
     * @var string
     * @Flow\Validate(type="NotEmpty")
     */
    protected $street;

    /**
     * The state
*
     * @var \Lelesys\App\Domain\Model\Country
     * @Flow\Validate(type="NotEmpty")
     * @ORM\ManyToOne
     */
    protected $country;


class Country { 

    /**
     * The name
*
     * @var string
     */
    protected $name;

    /**
     * The country iso code
*
     * @var string
     */
    protected $countryIso;

The concerned repository class should extend “\Neos\Flow\Persistence\Doctrine\Repository”.

class CustomerRepository extends \Neos\Flow\Persistence\Doctrine\Repository {
.
.
.
.
/**
* Search Customers who live in India
*
* @return \Neos\Flow\Persistence\QueryResultInterface
*/
public function searchCustomerAddresses() {

        $queryBuilder = $this->createQueryBuilder();
        $queryBuilder->select('c')->from('\Lelesys\App\Domain\Model\Customer', 'c')
                     ->join('c.addresses', 'a')
                     ->join('a.country', 'c')
                     ->where("c.name like '%India%'");
        return $query->execute();
    }

Method searchCustomerAddresses() in the above snippet will search Customers who live in India.

The result is of type 'QueryResultInterface'.

If you want to debug the query, you can get a var_dump by adding this before the execute statement:

\Neos\Flow\var_dump($queryBuilder->getQuery()->getDQL());

This will give you the generated DQL. You can check the result from command line by executing the doctrine:dql command.

./flow doctrine:dql “generated dql”


Categories: Flow Framework

Copyright © 2019 Lelesys Informatik GmbH, Deutschland. All Rights Reserved.