I have itemid and id categories that are conditional. If none is set, all items are displayed with the last fist. If itemid is set, only items with an identifier below the specified identifier are displayed (for swapping). If a category identifier is specified, only those items in a certain category are displayed, and if both values are indicated, and not just objects from a certain category, where the item identifier is displayed less than itemid (position on the category next page).
Since the parameters are conditional, you get a lot of if statements depending on the parameters when building the SQL string (the pseudocode from which I carry my dollar sign with the php file):
if itemid ' where i.iid < :itemid ' if catid if itemid ' and c.id = :catid' else ' where c.id = :catid' end if end if
If you are given additional parameters, this will become very dirty, so I decided to try createQueryBuilder. Hope for something like this:
if($itemId!==false){ $qb->where("i.id < :id"); } if($categoryId!==false){ $qb->where("c.id = :catID"); }
This is sadly wrong, and the last is where to rewrite the first
What I came up with is (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 do not fully trust $qb->where(null,null) , although it currently does not produce errors or unexpected results. It appears that these options are being ignored. Could not find anything in the documentation, but an empty line would create the error $qb->where('','') .
It also looks a bit awkward for me, if I can use several $qb->where(condition) then only one if statement is needed for the optional $qb->where(condition)->setParameter(':name', $val);
So the question is: is there a better way?
I assume that if the doctrine has a function to avoid strings, I can get rid of the second round of if (not sure if the malicious user could POST something in another character set that allows 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 so long and hoping you can enlighten me.
[UPDATE]
I am currently using a dummy where statement, so any additional conditional statements can be added using 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); }