CODEWITHSUNDEEP
mysqlsymfonydoctrine-orm
shayster01
shayster01

Reputation: 214

Symfony Doctrine creating complex queries

I have a mysql query and it's not really complex but I can't seem to wrap my head on how I should approach writing this in Symfony.

First here is the query:

select
   db1.table1.acronym
 , db1.table1.name
 , db2.table1.name
from db1.table1, db2.table1 
where db1.table1.acronym like concat('%', @t1, '%') 
or db1.table1.name like concat('%', @t1,'%') 
or db2.table1.name like concat('%', @t1,'%');

I am basically trying to see if t1 is part of any word in those columns. Assume @t1 is a variable being passed. I am not sure if I should write this in a Repository and if so how. I am looking for the best practice method.

Thanks.

Upvotes: 0

Views: 548

Answers (2)

Dan Mironis
Dan Mironis

Reputation: 721

First of all, you would want to configurate two Doctrine DB connections for first database and second. It would look like this (I assume that you don't have created Entities for the tables yet):

//in config.yml

doctrine:
    dbal:
        default_connection: db1
        connections:
            db1:
                driver:   "%database_driver%"
                host:     "%database_host%"
                port:     "%database_port%"
                dbname:   "%database_name%"
                user:     "%database_user%"
                password: "%database_password%"
                charset:  UTF8
            db2:
                driver:   "%database_driver%"
                host:     "%database_host%"
                port:     "%database_port%"
                dbname:   "%database_name2%"
                user:     "%database_user%"
                password: "%database_password%"
                charset:  UTF8

Note here we use parameter database_name2 in 'dbname' for db2 connection, other parameters most likely will be the same, unless you have the other database on another server, so you should create parameters accordingly in parameters.yml.

Then, you can get your connections to separate databases in, for example, controller action, like this:

//SomeController.php

$connection1 = $this->getDoctrine()->getConnection('db1');
$connection2 = $this->getDoctrine()->getConnection('db2');


$results1 = $connection1->fetchAll('SELECT acronym, name FROM table1 ...');
$results2 = $connection2->fetchAll('SELECT name FROM table2 ...')

NOTE, that this is not best practice. Best practice would be create entities for the tables, and then create separate entity managers along connections that I created above.

How to properly setup multiple connections with entity managers

Upvotes: 1

Konstantin Pereiaslov
Konstantin Pereiaslov

Reputation: 1814

Your query doesn't have a join, so I'm not sure what you're trying to achieve here, but if you just want to find all the entities in db1.table1 and db2.table1, these really should be 2 separate queries.

Also with Doctrine, it's hard to use multiple databases, so unless you have a good reason for that, I suggest you moving both tables to the same database and renaming db2.table1 to db1.table2.

After this, the QueryBuilder in Table1Repository to find entities in table1 would like this:

public function findByNameOrAcronymLike($nameOrAcronym) {
        $qb = $this->createQueryBuilder('table1')
            ->where('table1.acronym LIKE :t1')
            ->or('table1.name LIKE :nameOrAcronym')
            ->setParameter('nameOrAcronym', addcslashes($nameOrAcronym, '_%'))
            ->getQuery()
            ->execute();
}

And the QueryBuilder in Table2Repository:

public function findByNameLike($name) {
        $qb = $this->createQueryBuilder('table2')
            ->where('table2.name LIKE :name')
            ->setParameter('name', addcslashes($name, '_%')
            ->getQuery()
            ->execute();
}

addcslashes call is added to escape % and _ - those are considered special characters in LIKE specifier

Upvotes: 0

Related Questions