0

I have table Question. Question belongs to questioncategory:

Question:

----------------------------
| id | questioncategory_id |
----------------------------
| 1  | 1                   |
| 2  | 1                   |
| 3  | 1                   |
| 4  | 2                   |
| 5  | 2                   |
| 6  | 1                   |
----------------------------

I want to build a query with QueryBuilder or DQL, to retrieve random row for each questioncategory. Questioncategories are stored in array ($ids).

Current query (returns first question for each category):

$query = $repository->createQueryBuilder('p')
                ->where('p.questioncategory IN (:questionCat)')
                ->groupBy('p.questioncategory')
                ->setParameter('questionCat', $ids)
                ->getQuery();

Any ideas? I know how to retrieve random row for all questions, it's described here. I have to retrieve random row for each category, so I have to somehow count number of questions for each category and then select random row for that category.

Community
  • 1
  • 1
repincln
  • 2,029
  • 5
  • 24
  • 34
  • try with : order by RAND() – Pranay Bhardwaj Feb 25 '14 at 12:20
  • RAND() is not working in doctrine. If I add ->orderBy('RAND()') then I've got an error: [Syntax Error] line 0, col 132: Error: Expected end of string, got '(' – repincln Feb 25 '14 at 12:24
  • may be this will help you http://stackoverflow.com/questions/10762538/how-to-select-randomly-with-doctrine – Pranay Bhardwaj Feb 25 '14 at 12:26
  • also this http://docs.doctrine-project.org/en/2.1/reference/faq.html#can-i-sort-by-a-function-for-example-order-by-rand-in-dql – Pranay Bhardwaj Feb 25 '14 at 12:27
  • Note that, in general SQL is **really bad** at getting "random" rows (it has enough trouble getting _single_ rows, too, usually). SQL works over set data, essentially saying "everything that matches" – Clockwork-Muse Feb 25 '14 at 13:34

0 Answers0