Doctrine DQL查询 - 在外键为null时获取行

Question

I wonder if anyone can help with this Doctrine query.
Basically, My query does not return rows where the foreign key is not set or NULL. And I would like to return all rows.

Here are 2 schemas

Items

class Items{
  /**
   * @var integer $id
   *
   * @Column(name="id", type="integer", nullable=false)
   * @Id
   * @GeneratedValue(strategy="AUTO")
   */
    private $id;
  /**
   * @var string $name
   *
   * @Column(name="name", type="string", length=255, nullable=false)
   */
   private $name;
  /**
   * @var integer $type
   *
   * @ManyToOne(targetEntity="Types")
   *
   */
   private $type;
}

Types

class Types{
  /**
   * @var integer $id
   *
   * @Column(name="id", type="integer", nullable=false)
   * @Id
   * @GeneratedValue(strategy="AUTO")
   */
    private $id;
  /**
   * @var string $name
   *
   * @Column(name="name", type="string", length=255, nullable=false)
   */
   private $name;
}

And the following DQL query

SELECT i.id, i.name, t.name as type FROM entity\Items i, entity\Types t WHERE i.type=t.id (OTHER CONDITIONS...)

That above query does not return the rows that does not have a value in the type foreign key.

Is it possible to return those rows?

Thank you in advance...

Answer 1

Try a LEFT JOIN:

SELECT i.id, i.name, t.name as type FROM entity\Items i LEFT JOIN i.type t

That will return everything on the left (Items) regardless if there is a matching Type.