Repeat query when conditions aren't met

Question

I am making a kindof quiz. The quiz has 15 questions, for the quiz I need 5 questions of quiztype '1', 5 of quizType '2' and 5 of quizType '3'. Right now I'm counting quiztype '1'and quiztype '2' trough a loop and if conditions outside the loop aren't met, I get 15 new entry's and repeat the loop. I'm wondering, is there a better way to do this inside my query instead of using 2 objects? This Is my code:

public function checkVariety($quizType, $data) 
    { 
        $i=0;
        $i2=0;
        foreach($quizType as $type) {   
            if ($type=='1') {
             $i++;
            }
            if ($type=='2') {
             $i2++;
            }
        }
        if($i=='5' AND $i2=='5') {
            $this->startQuiz($data); 
            return true;
        } else {
            $this->getRandom();
            return false;
        }
    }
    
    public function getRandom() 
    {
        $stmt = $this->db->prepare("
        SELECT id, quiz_type
        FROM quiz 
        ORDER BY rand()
        LIMIT 15
        ");
        $stmt->execute();
        
        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
            $quizType[] = $row['quiz_type'];
            $data[] = $row['id'];
        }
        $this->checkVariety($quizType, $data);
        return true; 
    }

I would use a UNION to get the combined result of three queries that select five random questions of a _specific_ quizType each. — CBroe, Aug 27 '21 at 08:09
I haven't heard of union before, I will look into it, but does it still get all the entry's in a random order? Or is it first 5x quiztype=1, then 5x quiztype=2 and the last entry's 5x quiztype=3? — SingingInTheRain, Aug 27 '21 at 08:13
https://stackoverflow.com/questions/4715820/how-to-order-by-with-union-in-sql should answer that. — CBroe, Aug 27 '21 at 08:16
@CBroe I'm struggling with the UNION query. Could you (Or someone else) give me an example of how to do this? — SingingInTheRain, Aug 27 '21 at 09:12

score 0 · Answer 1 · answered Aug 27 '21 at 10:30

0

I got it partially working thanks to the UNION method.

$stmt = $this->db->prepare("
        SELECT *
        FROM  (SELECT * FROM exercises as e1 WHERE e1.form='1' ORDER BY rand() LIMIT 5) as f
        UNION
        SELECT *
        FROM  (SELECT * FROM exercises as e1 WHERE e1.form='2' ORDER BY rand() LIMIT 5) as f2
        UNION
        SELECT *
        FROM  (SELECT * FROM exercises as e1 WHERE e1.form='3' ORDER BY rand() LIMIT 5) as f3
        ORDER BY rand()
        ");
        $stmt->execute();

still having some problems though, but I will try to figure that out on my own first, and if I eventually need to, open another question.

answered Aug 27 '21 at 10:30

SingingInTheRain

5
2

There's no untrusted data in your query. There's no need to use prepared statements to defend against SQl Injection, and since you're only running the query once, there are no performance benefits either. Just use `$this->db->query(...)` – Tangentially Perpendicular Aug 27 '21 at 10:46
Why is ```$this->db->query()``` preferable? Is it faster? – SingingInTheRain Aug 27 '21 at 11:19

Jon Armstrong · Accepted Answer · 2021-08-27T14:27:39.220

You could also combine this way.

The UNION was easily avoided by noting the difference in the SELECT statements was just to pick form values 1, 2, and 3. In SQL, this is easily done with form IN (1, 2, 3).

The problem with this is we can't easily use LIMIT 5, as you originally did, since all 15 rows are now in the same result.

This is where window functions comes into play. We can now process these rows using window specifications to isolate and operate on groups (by partition) of rows.

The example below is ROW_NUMBER() OVER (PARTITION BY form ORDER BY rand()) AS seq.

In short, this derives a new column (see: derived column), the contents of which is the position (row number) of this row within the group of rows with a matching form value (indicated in the PARTITION BY terms) and in the order specified by the ORDER BY terms of the OVER clause.

Your requirement is complicated slightly by the needed random order. It's not as easy to see how this window function use provides this nice row number ordering. You can test this by replacing the rand() term with something more recognizable ORDER BY exercise, which is the column I chose to represent some exercise identifier.

The WITH clause or Common Table Expression - CTE term is like a derived table or view, but provides more capability, like recursion. We can access it similar to any VIEW, Derived Table, base table, etc.

In the following CTE term, we select all the rows matching the 3 forms, and assign / generate a new seq column containing a row number (1 to n, within each partition), so that later we can just take seq <= 5 to limit the result to just the first 5 rows in each partition (form).

WITH cte AS (
       SELECT *
            , ROW_NUMBER() OVER (PARTITION BY form ORDER BY rand()) AS seq
         FROM exercises
        WHERE form IN (1, 2, 3)
     )
SELECT * FROM cte
 WHERE seq <= 5
 ORDER BY form, seq
;

Result with test data:

+----------+------+-----+
| exercise | form | seq |
+----------+------+-----+
|       15 |    1 |   1 |
|        8 |    1 |   2 |
|       10 |    1 |   3 |
|       16 |    1 |   4 |
|        6 |    1 |   5 |
|       29 |    2 |   1 |
|       24 |    2 |   2 |
|       26 |    2 |   3 |
|       20 |    2 |   4 |
|       25 |    2 |   5 |
|       41 |    3 |   1 |
|       46 |    3 |   2 |
|       47 |    3 |   3 |
|       40 |    3 |   4 |
|       51 |    3 |   5 |
+----------+------+-----+

That's awesome! I can't upvote your comment yet bcs I'm new on stackoverflow. Though I would have, if I could :p — SingingInTheRain, Aug 27 '21 at 12:53
@SingingInTheRain You're welcome. If you wish to "Accept" it, you're able to do that via the "checkmark" near the upvote controls. — Jon Armstrong, Aug 27 '21 at 12:56
@SingingInTheRain I'll add some comments about the CTE term and window functions, both of which might be new to you, in a few minutes. — Jon Armstrong, Aug 27 '21 at 13:23

Repeat query when conditions aren't met

2 Answers2