Symfony Doctrine creating complex queries

Question

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.

Dan Mironis · Accepted Answer · 2015-08-09 11:28:43Z

1

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

edited Aug 9, 2015 at 11:28

answered Aug 9, 2015 at 11:22

Dan Mironis

7316 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

shayster01 Over a year ago

Also not mentioned is to edit parameters.yml to get config.yml variables to work like, dbname: "%database_name2%" parameters.yml: parameters: database_host: 127.0.0.1 database_port: null database_user: myusername database_password: mypassword database_name: db1 mailer_transport: smtp mailer_host: 127.0.0.1 mailer_user: null mailer_password: null secret: fsfaffffafaf database_host2: 127.0.0.1 database_port2: null database_user2: meusername database_password2: password database_name2: db2

Konstantin Pereiaslov · Accepted Answer · 2015-08-08 00:08:13Z

0

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

answered Aug 8, 2015 at 0:08

Konstantin Pereiaslov

1,8241 gold badge20 silver badges29 bronze badges

3 Comments

shayster01 Over a year ago

Yes I have a good reason. The databases are a clients and I do not have the option to combine them.

Konstantin Pereiaslov Over a year ago

@shayster01 In this case everything I wrote still applies, but also read this article: symfony.com/doc/current/cookbook/doctrine/…

shayster01 Over a year ago

This helped more: docs.doctrine-project.org/projects/doctrine-orm/en/latest/…

Collectives™ on Stack Overflow

Symfony Doctrine creating complex queries

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related