php - Doctrine query with nullable/optional join -
i got method on repository class me article
join (category, image ...) , not every article
have categories or image, don't give me expected result, cause current repository function return article
have categories
, image
not null , ignore having null value.
my article
entity have following relationship.
/** * @orm\manytomany(targetentity="app\articlebundle\entity\category", cascade={"persist"}) */ private $categories; /** * @orm\manytoone(targetentity="app\articlebundle\entity\image", cascade={"persist"}) * */ private $image;
and repository function
public function getarticle($id) { $qb = $this->createquerybuilder('a') ->where('a.id = :theid') ->setparameter('theid', $id) ->join('a.author', 'auth') ->addselect('auth') ->join('a.categories', 'cat') ->addselect('cat') ->join('a.image', 'img') ->addselect('img'); return $qb->getquery()->getoneornullresult(); }
now want know if can article
have categories
, image
or not in 1 query join. want when using doctrine lazy loading (by avoid join in query) expected result.
use ->leftjoin()
article
have categories
, image
or not in 1 query:
public function getarticle($id) { $qb = $this ->createquerybuilder('a') ->addselect('auth', 'cat', 'img') ->join('a.author', 'auth') ->leftjoin('a.categories', 'cat') ->leftjoin('a.image', 'img') ->where('a.id = :theid') ->setparameter('theid', $id) ; return $qb->getquery()->getoneornullresult(); }
thus, avoids queries when doctrine try load related properties in lazy way.
explanation:
using ->join()
or ->innerjoin()
:
this simplest, understood join , common. query return of records in left table (table a) have matching record in right table (table b).
using ->leftjoin()
:
this query return of records in left table (table a) regardless if of records have match in right table (table b). return matching records right table.
source: visual-representation-of-sql-joins explained in detail c.l. moffatt
Comments
Post a Comment