DDC-3959: Option to use LEFT JOIN instead of INNER JOIN with findAll() method. #4841

New Issue

admin · 2026-01-22T14:50:23+01:00

admin commented

2026-01-22 14:50:23 +01:00

Originally created by @doctrinebot on GitHub (Oct 23, 2015).

Originally assigned to: @beberlei on GitHub.

Jira issue originally created by user greekatrina:

I have a User and Company entity. Each User belongs to a Company. Here are my entities (shortened):

<?php

namespace App\Model\Entity;

use Doctrine\ORM\Mapping as ORM;
use Doctrine\Common\Collections\ArrayCollection;

/****  
 * @ORM\Table(name="user")
 */
class User
{
    /**** 
     * @ORM\Id
     * @ORM\Column(type="integer", name="user_id")
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $id;

    /****
     * @ORM\Column(length=200)
     */
    private $email;

    /****
     * @ORM\ManyToOne(targetEntity="Company", inversedBy="users", fetch="EAGER")
     * @ORM\JoinColumn(name="company*id", referencedColumnName="company*id", nullable=false)
     */
    private $company;

    // And a bunch of getters/setters

<?php

namespace App\Model\Entity;

use Doctrine\ORM\Mapping as ORM;
use Doctrine\Common\Collections\ArrayCollection;

/**** 
 * @ORM\Table(name="company")
 */
class Company
{
    /**** 
     * @var int
     *
     * @ORM\Id
     * @ORM\Column(type="integer", name="company_id", options={"unsigned":1})
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $id;

    /****
     * @var string
     *
     * @ORM\Column(length=100, unique=true)
     */
    private $name = "";

    /****
     * @var Doctrine\Common\Collections\ArrayCollection
     *
     * @ORM\OneToMany(targetEntity="User", mappedBy="company", cascade={"persist", "remove"}, fetch="LAZY")
     */
    private $users;

    // getters/setters

When I was testing my production code, I forgot to add a company to the company table. So, when I did a basic $entityManager->getRepository('App/Model/Entity/User')->findAll(); to make sure my database was connecting correctly, it returned an empty array, even though I had a user in there.

I dug a little bit to find the SQL it was outputting, which was this:

SELECT 
    t0.user*id AS user*id1, 
    t0.email AS email2, 
    t0.company*id AS company*id3, 
    t10.company*id AS company*id5, 
    t10.name AS name6 FROM user t0 
INNER JOIN company t10 
ON t0.company*id = t10.company*id

I realized the INNER JOIN was the reason I wasn't getting any users. Now I'm curious what causes Doctrine to use the INNER JOIN? Is it due to nullable=false for the company attribute in the User entity, or something else?

Is there another way to get all of the users, even if they don't belong to a company?

Originally created by @doctrinebot on GitHub (Oct 23, 2015). Originally assigned to: @beberlei on GitHub. Jira issue originally created by user greekatrina: I have a User and Company entity. Each User belongs to a Company. Here are my entities (shortened): ``` none <?php namespace App\Model\Entity; use Doctrine\ORM\Mapping as ORM; use Doctrine\Common\Collections\ArrayCollection; /**** * @ORM\Table(name="user") */ class User { /**** * @ORM\Id * @ORM\Column(type="integer", name="user_id") * @ORM\GeneratedValue(strategy="AUTO") */ private $id; /**** * @ORM\Column(length=200) */ private $email; /**** * @ORM\ManyToOne(targetEntity="Company", inversedBy="users", fetch="EAGER") * @ORM\JoinColumn(name="company*id", referencedColumnName="company*id", nullable=false) */ private $company; // And a bunch of getters/setters <?php namespace App\Model\Entity; use Doctrine\ORM\Mapping as ORM; use Doctrine\Common\Collections\ArrayCollection; /**** * @ORM\Table(name="company") */ class Company { /**** * @var int * * @ORM\Id * @ORM\Column(type="integer", name="company_id", options={"unsigned":1}) * @ORM\GeneratedValue(strategy="AUTO") */ private $id; /**** * @var string * * @ORM\Column(length=100, unique=true) */ private $name = ""; /**** * @var Doctrine\Common\Collections\ArrayCollection * * @ORM\OneToMany(targetEntity="User", mappedBy="company", cascade={"persist", "remove"}, fetch="LAZY") */ private $users; // getters/setters ``` When I was testing my production code, I forgot to add a company to the company table. So, when I did a basic $entityManager->getRepository('App/Model/Entity/User')->findAll(); to make sure my database was connecting correctly, it returned an empty array, even though I had a user in there. I dug a little bit to find the SQL it was outputting, which was this: ``` sql SELECT t0.user*id AS user*id1, t0.email AS email2, t0.company*id AS company*id3, t10.company*id AS company*id5, t10.name AS name6 FROM user t0 INNER JOIN company t10 ON t0.company*id = t10.company*id ``` I realized the INNER JOIN was the reason I wasn't getting any users. Now I'm curious what causes Doctrine to use the INNER JOIN? Is it due to nullable=false for the company attribute in the User entity, or something else? Is there another way to get all of the users, even if they don't belong to a company?

admin added the Improvement label 2026-01-22 14:50:23 +01:00

admin closed this issue

2026-01-22 14:50:23 +01:00

admin commented

2026-01-22 14:50:23 +01:00

@beberlei commented on GitHub (Feb 16, 2020):

Yes, this is because of the nullable=false. If you have nullable=true then it will use a LEFT JOIN.

@beberlei commented on GitHub (Feb 16, 2020): Yes, this is because of the `nullable=false`. If you have `nullable=true` then it will use a `LEFT JOIN`.

Sign in to join this conversation.

Branches Tags

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: doctrine/archived-orm#4841