Doctrine createQueryBuilder multiple conditional where statements a bit clunky

Doctrine createQueryBuilder multiple conditional where statements a bit
clunky

I have an itemid and a category id that are both conditional. If none is
given then all items are shown newest fist. If itemid is given then only
items with an id lower than given id are shown (for paging). If category
id is given than only items in a certain category are shown and if both
are given than only items from a certain category where item id smaller
than itemid are shown (items by category next page).
Because the parameters are conditional you get a lot of if statements
depending on the params when building a SQL string (pseudo code I'm
wearing out my dollar sign with php stuff):
if itemid ' where i.iid < :itemid '
if catid
if itemid
' and c.id = :catid'
else
' where c.id = :catid'
end if
end if
If more optional parameters are given this will turn very messy so I
thought I'd give the createQueryBuilder a try. Was hoping for something
like this:
if($itemId!==false){
$qb->where("i.id < :id");
}
if($categoryId!==false){
$qb->where("c.id = :catID");
}
This is sadly not so and the last where will overwrite the first one
What I came up with is this (in Symfony2):
private function getItems($itemId,$categoryId){
$qb=$this->getDoctrine()->getRepository('mrBundle:Item')
->createQueryBuilder('i');
$arr=array();
$qb->innerJoin('i.categories', 'c', null, null);
$itemIdWhere=null;
$categoryIdWhere=null;
if($itemId!==false){
$itemIdWhere=("i.id < :id");
}
if($categoryId!==false){
$categoryIdWhere=("c.id = :catID");
}
if($itemId!==false||$categoryId!==false){
$qb->where($itemIdWhere,$categoryIdWhere);
}
if($itemId!==false){
$qb->setParameter(':id', $itemId);
}
if($categoryId!==false){
$arr[]=("c.id = :catID");
$qb->setParameter(':catID', $categoryId);
}
$qb->add("orderBy", "i.id DESC")
->setFirstResult( 0 )
->setMaxResults( 31 );
I'm not fully trusting the $qb->where(null,null) although it is currently
not creating errors or unexpected results. It looks like these parameters
are ignored. Could not find anything in the documentation but an empty
string would generate an error $qb->where('','').
It also looks a bit clunky to me still, if I could use multiple
$qb->where(condition) then only one if statement per optional would be
needed $qb->where(condition)->setParameter(':name', $val);
So the question is: Is there a better way?
I guess if doctrine had a function to escape strings I can get rid of the
second if statement round (not sure if malicious user could POST something
in a different character set that would allow sql injection):
private function getItems($itemId,$categoryId){
$qb=$this->getDoctrine()->getRepository('mrBundle:Item')
->createQueryBuilder('i');
$arr=array();
$qb->innerJoin('i.categories', 'c', null, null);
$itemIdWhere=null;
$categoryIdWhere=null;
if($itemId!==false){
$itemIdWhere=("i.id < ".
someDoctrineEscapeFunction($id));
}
Thank you for reading this far and hope you can enlighten me.
[UPDATE]
I am currently using a dummy where statement so any additional conditional
statements can be added with andWhere:
$qb->where('1=1');// adding a dummy where
if($itemId!==false){
$qb->andWhere("i.id < :id")
->setParameter(':id',$itemId);
}
if($categoryId!==false){
$qb->andWhere("c.id = :catID")
->setParameter(':catID',$categoryId);
}