Doctrine filter in WHERE clause instead of LEFT JOIN

Question

I have an application with several tenants and I use Doctrine Filters to filter my SQL client.

So, when I need a list of my client projects, I just need to do "getAll" and the filter will automatically add SQL to the WHERE clause, for example:

SELECT * FROM projects p WHERE p.client_id = 1 #(appended by the filter) 

My problem is when I want, for example, ProjectMembers. The filter will add SQL to the LEFT JOIN, not to the WHERE clause, creating an unnecessary filter because it will return all ProjectMembers objects, even if they do not belong to client 1.

 SELECT * FROM projects p LEFT JOIN project_members pm ON pm.project_id = p.id AND p.client_id = 1 #(appended by the filter) 

This is my addFilterConstrait

 public function addFilterConstraint(ClassMetaData $targetEntity, $targetTableAlias) { $class = $targetEntity->getName(); if (array_key_exists($class, $this->disabled) && $this->disabled[$class] === true) { return ''; } elseif (array_key_exists($targetEntity->rootEntityName, $this->disabled) && $this->disabled[$targetEntity->rootEntityName] === true) { return ''; } $config = $this->getFilterConfig($targetEntity->getReflectionClass()); if (!isset($config['clientFilter']) || !$config['clientFilter']) { return ''; } return $targetTableAlias. '.' . $config['columnName'] . ' = ' . $this->getParameter('client'); // getParameter applies quoting automatically } 

Any ideas how I can solve this by adding a filter to WHERE instead of LEFT JOIN?

Answer 1

Try the following:

 SELECT * FROM table1, table2 WHERE table1.id = table2.id 

It may solve your problem.