php - Symfony2 Doctrine get random product from a category -

i have following database scheme:

table 'products' id category_id 

and of course category table, id.

the data that:

products -------------------- | id | category_id | -------------------- | 0  | 1           | | 1  | 1           | | 2  | 1           | | 3  | 2           | | 4  | 2           | | 5  | 1           | -------------------- 

i select category(for example category 1), select rows category in product-repository class:

return $this     ->createquerybuilder('u')     ->andwhere('u.category = :category')     ->setmaxresults(1)     ->setparameter('category', $category->getid())     ->getquery()     ->getsingleresult() ; 

how can select random product? also: possible solve via relationships?

i have onetomany relationship between entities "category" , "product", products via category->getproducts()...

any useful, thanks

you first have count total number of products, generate random offset select random product.

this should started:

$count = $this->createquerybuilder('u')              ->select('count(u)')              ->getquery()              ->getsinglescalarresult(); 

and can generate random number between 1 , total number of rows.

return $this->createquerybuilder('u')     ->where('u.category = :category')     ->setfirstresult(rand(0, $count - 1))     ->setmaxresults(1)     ->setparameter('category', $category->getid())     ->getquery()     ->getsingleresult() ; 

which translates to:

select * products category_id = ? limit 1, {random offset}