phpcodeigniterdoctrine-ormleft-join

left join in Codeigniter using Doctrine 2 ORM library

How do I left join two tables in Codeigniter using the Doctrine 2 ORM library?

Solution

  • The Doctrine entity model doesn't have associations for one-to-many and vice-versa. To allow these you have to update your entities with valid associations. Below is an example where table users contains user information and table UserFavoriteMeals has user favorite meals.

    In entity user add a OneToMany association to UserFavoriteMeals where mappedby is the key on the basis of which the association happens in the DB:

    /**
 * @var \Entity\UserFavoriteMeals
 *
 * @OneToMany(targetEntity="Entity\UserFavoriteMeals", mappedBy="userid" )
 *  @JoinColumns({
 *   @JoinColumn(name="id", referencedColumnName="userId", nullable=true)
 * })
 */
 private $UserFavoriteMeals;

    Put a manyToOne association in entitiy UserFavoriteMeals:

    /**
 * @var \Entity\Users
 *
 * @ManyToOne(targetEntity="Entity\Users")
 * @JoinColumns({
 *   @JoinColumn(name="userId", referencedColumnName="id", nullable=true)
 * })
 */
private $userid;

    A LEFT JOIN:

    $left_query = $this->em->createQuery("SELECT fm,u FROM Entity\userFavoriteMeals fm LEFT JOIN fm.userid u WHERE fm.userId = 231 ")->getArrayResult();
print_r($left_query);

$left_query_inverse = $this->em->createQuery("SELECT u,fm FROM Entity\Users u LEFT JOIN u.UserFavoriteMeals fm WHERE u.id = 4")->getArrayResult();
print_r($left_query_inverse);