Doctrine Query - Ignoring Spaces

Doctrine query - ignoring spaces

Ok I write a replace DQL Function.

<?php

namespace Acme\UserBundle\DQL;

use Doctrine\ORM\Query\Lexer; 
use Doctrine\ORM\Query\AST\Functions\FunctionNode; 

/**
 * "REPLACE" "(" StringPrimary "," StringSecondary "," StringThird ")"
 */
class replaceFunction extends FunctionNode{

    public $stringFirst; 
    public $stringSecond; 
    public $stringThird; 

    public function getSql(\Doctrine\ORM\Query\SqlWalker $sqlWalker) {
        return  'replace('.$this->stringFirst->dispatch($sqlWalker) .','
                . $this->stringSecond->dispatch($sqlWalker) . ',' 
                .$this->stringThird->dispatch($sqlWalker) . ')';
    }

    public function parse(\Doctrine\ORM\Query\Parser $parser) {

        $parser->match(Lexer::T_IDENTIFIER);
        $parser->match(Lexer::T_OPEN_PARENTHESIS);
        $this->stringFirst = $parser->StringPrimary();
        $parser->match(Lexer::T_COMMA);
        $this->stringSecond = $parser->StringPrimary();
        $parser->match(Lexer::T_COMMA);
        $this->stringThird = $parser->StringPrimary();
        $parser->match(Lexer::T_CLOSE_PARENTHESIS);
    }

}

Next in app/config.yml I add:

doctrine:
    orm:
        auto_generate_proxy_classes: "%kernel.debug%"
        auto_mapping: true
        dql:
            string_functions:
                replace: Acme\UserBundle\DQL\replaceFunction

And finally I create a DQL query in my Controller:

$em = $this->getDoctrine()->getManager();

    $query = $em->createQueryBuilder();

    $query->select('u')
            ->from('Acme\UserBundle\Entity\User', 'u')

            ->where("replace(u.username,' ','') LIKE replace(:username,' ','') ")
            ->setParameter('username', '%' . $usernameForm . '%')
            ->orderBy('u.username', 'asc');

    $result = $query->getQuery()->getResult();

The most funny thing is that "quotes" are very important. It means that you can see that in select, from, setParameter and orderBy I use '' but in where I use "" and space ''. The opposite is not working. I don`t know why.

Use advanced select in doctrine2/ symfony2

so i couldn't use

 $qb->select('a.id,' . $expr->quot(':counter', ':denominator') . 'as count')

instead i've changed name from 'count' to 'field' nad removed 'as'

$qb->select('a.id id,' . $expr->quot($counter, $denominator . ' field'))

i used: replace class and regexp

And it works. PROBLEM SOLVED

Using an Entity's methods inside a query in Doctrine

Short answer - no, and you don't really want to. You're conflating PHP logic with SQL logic. Your address() function is a pure PHP function. Even though it is using relationships within your entity, Doctrine itself has no way of knowing about it. Your function is literally returning a string, so how would it know how to convert that to SQL for your WHERE clause?

Just change your original query to this:

$this->getEntityManager()->createQuery('
    SELECT c
    FROM AppBundle:Customer c
    ORDER BY c.name ASC, c.addressLine1 ASC, c.addressLine2 ASC, c.postcode ASC
')->getResult();

I suppose you could pseudo-do what you want like this:

Customer Entity:

public static function addressSort()
{
    return ' c.addressLine1 ASC, c.addressLine2 ASC, c.postcode ';
}

and then do

$this->getEntityManager()->createQuery('
    SELECT c
    FROM AppBundle:Customer c
    ORDER BY c.name ASC, ' . Customer::addressSort()
)->getResult();

However, now you're mixing PHP and SQL even further and I very highly recommend that you do NOT do this.

Doctrine2 Symfony2 how to ignore NULL in query builder?

Use array_filter to remove NULL values from your array:

->findBy(array_filter(array(
      'productType' => $productType,
      'region' => $region,
      'town' => $town,            
      'road' => $road               
), 'is_null'),

Doctrine Query doesn't work as expected

Don't ever use strings for storing dates. Especially not like this. If your query worked, it still would result in unexpected stuff, because when matching strings it works in an alphabetic order. So in your case you would get all dates after the 23rd of any month and any year. So you need to store them as DATETIME in order to solve this. The other problem is that you need to put $start_date between apostrophes, because now it calculates 23-5-2013, which is -1995. So:

1. Convert the dates to DATETIME;
2. Use \DateTime objects when inserting and querying (and YYYY-MM-DD format for strings)

Oh, and one more thing: use IN expressions in your where condition, it's much more practical.

Doctrine adds extra queries during hydration causing n+1 problem with normal one to one and self-referenced relations

You have a couple inverse OneToOne relationships in that entity.

Inverse OneToOne relationships cannot be lazy-loaded by Doctrine, and can easily become a performance problem.

If you really need to have those relationships mapped on the inverse side (and not only on the owning side) make sure to make the appropriate joins explicitly, or mark those associations as FETCH=EAGER so that Doctrine makes the joins for you.

E.g. a query that would avoid the dreaded "n+1" problem would be:

SELECT n, g, e
    FROM App\Entity\News n
    LEFT JOIN n.gallery g
    LEFT JOIN n.event e
WHERE n.parent = :id

You can read more about the N+1 problem in Doctrine here or here.

SQL query to ignore space

You can use RTRIM() to remove spaces from the right and LTRIM() to remove spaces from the left hence left and right spaces removed as follows:

SELECT * FROM mytable 
    WHERE LTRIM(RTRIM(username)) = LTRIM(RTRIM("John Bob Jones"))

Doctrine query crashing

Updated link: https://www.doctrine-project.org/projects/doctrine-orm/en/2.7/reference/inheritance-mapping.html

Read up a bit on mapped super classes: http://docs.doctrine-project.org/en/latest/reference/inheritance-mapping.html. Basically, your abstract base user class cannot itself be an entity.

So take the @ORM\Entity line out of your User class. That is where the table 0 (t0) is coming from.

insert ignore on duplicate entries in Doctrine2/Symfony2

That's one of the nuisances of Doctrine, it's not possible to do INSERT/UPDATE Ignore, there are workaround like creating a methods that checks if the row exists, and if it does then just skip it.

You can catch the exception, so that your script doesn't end in an exception. However, the entity manager will be closed and you will not be able to use it anymore. You can still use PDO, though and you can insert a record in the database indicating that your batch failed because X and it needs to be restarted (that's what I usually do).

If none of the options above work for you, ultimately I end up writing raw SQL to do batch processing and I don't use Doctrine at all, it ends up being faster and the ability of doing INSERT/UPDATE Ignore makes it a no brainer.

---

Related Topics