0

I have problem with QueryBuilder, i have three tables:

"counter" "cost_tp" "counter_cost_tp" (relation many to many between counter and cost_tp)

In raw SQL i can create query:

SELECT 
  `counter`.`name`, 
  `cost_tp`.`name` 
FROM 
  `counter` 
  RIGHT JOIN `counter_cost_tp` ON `counter`.`id` = `counter_cost_tp`.`counter_id` 
  JOIN `cost_tp` ON `cost_tp`.`id` = `counter_cost_tp`.`cost_tp_id`

I tried to do the same in QueryBuilder in Symfony:

$builder
    ->select(['c', 'ct'])
    ->from(CostTp::class, 'ct')
    ->innerJoin('ct.counters', 'c')
    ->distinct();

But it not works fine, return only results from CostTp and first counter. When i tried to run getQuery()->getSQL() the raw sql works fine, but getQuery->getResult() return bad results.

Do you have any fine method to right join in QueryBuilder?

Mahesh Thorat
  • 1
  • 4
  • 11
  • 22
Szal1k
  • 1
  • 1
  • Inner Join is different then a Right Join ( also called just JOIN ) Right Join, and Join are the same thing. Inner join only returns if there are records in both tables. Right return all records from first table that match the query, regardless if they have a record in second table. try `join` or `right join` in other words. InnerJoin and RightJoin are not the same thing, this is why your 2 queries behave differently, because they are. – ArtisticPhoenix May 11 '23 at 13:02
  • please see https://stackoverflow.com/questions/5706437/whats-the-difference-between-inner-join-left-join-right-join-and-full-join#:~:text=INNER%20JOIN%3A%20returns%20rows%20when,matches%20in%20the%20left%20table. – ArtisticPhoenix May 11 '23 at 13:04
  • Thanks for your answer. Problem is with Doctrine Hydration, i use OBJECT_HYDRATION so Doctrine merge results in objects. When switch to HYDRATION_ARRAY or SCALAR it works fine. – Szal1k May 11 '23 at 15:20
  • Personally I am not a big fan of Doctrine. They are fine but for my uses they worry to much about compatibility with other Databases, and that complicates things. That's my Opinion it's a little heavy if you only need to work with MySql for example. I've used it in the past and it works fine but some things are overly difficult. – ArtisticPhoenix May 11 '23 at 15:52

0 Answers0