How to get records from multiple tables?

Question

I have 3 MySQL tables with the following columns:

table1: uid, name

table2: uid, color

table3: uid, car

The values of the column uid are all the same. I would like to get the values from the columns name, color and car, where the uid is = 5.

Well, I could use:

$this->getDoctrine()->getRepository(Table1::class)->findOneBy(array('uid' => 5));
$this->getDoctrine()->getRepository(Table2::class)->findOneBy(array('uid' => 5));
$this->getDoctrine()->getRepository(Table3::class)->findOneBy(array('uid' => 5));

Since there will be multiple queries, this would be bad for performance. Is there a way I could get all the records with only 1 query?

score 1 · Accepted Answer · answered Sep 24 '19 at 14:58

You can join your tables with queryBuilder, and select expected result:

$queryBuilder
    ->select('t1.uid', 't1.name', 't2.color', 't3.car')
    ->from('table1', 't1')
    ->innerJoin('t1', 'table2', 't2', 't1.uid = t2.uid')
    ->innerJoin('t1', 'table3', 't3', 't1.uid = t3.uid')

you can find more information here: https://www.doctrine-project.org/projects/doctrine-dbal/en/2.9/reference/query-builder.html

How to get records from multiple tables?

1 Answers1