0

I've got a bit of an interesting problem: I have a database where questions and answers are saved, each question/answer combo is saved as a separate row, sharing a participation id between each entry. The data looks like this:

id | participation_id | q | a
--------------------------------
1  | 1                | 1 | q1a1
2  | 1                | 2 | 10
3  | 2                | 1 | q1a2
4  | 2                | 2 | 15
5  | 3                | 1 | q1a1
6  | 3                | 2 | 10

The id is just an auto-increment primary key, the participation_id is the link between question 1 and 2 (only 2 questions), q is question number and a is the given answer for that question. Both q and a are selects, so no custom input.

I'd like to get the sum of all answers (a) where question number (q) = 2 and where the same answer for question 1 has been chosen.

Example from above

Desired result for people who answered the first question with q1a1: 20

Desired result for people who answered the first question with q1a2: 15

So basically a total of all ansers for question 2 who entered the same value on the first question, possibly based on participation_id?

I had the following code, but obviously it didn't work the way I'd like it to:

$select = $read->select()
    ->from('q_and_a', 'SUM(a)')
    ->where('q = 1')
    ->where('a = ?', 'q1a1')
;

$sum = $readConn->fetchOne($select);

This tries to get the sum of all answers where question 1 is the same, not the sum of the answers for question 2 where question 1 is the same (based on participation_id?).

I hope this is somewhat clear, this wasn't an easy one to explain (or figure out). Feel free to improve/correct this question to make more sense, if needed. And if you know the answer, please let me know.

0

Okay, so I didn't get it to work with Magento's built-in query builder, but I did find a solution: just use direct sql and always get all the totals as array and based on an optional parameter return the correct (or all) value(s).

Here's the code that does it:

public function getQandA($question = null)
{
    $readConn = Mage::getSingleton('core/resource')->getConnection('core_read');

    $sql = "SELECT q1.a AS question, SUM(q2.a) as total FROM q_and_a q1 INNER JOIN q_and_a q2 ON q2.participation_id = q1.participation_id AND q2.q = 2 WHERE q1.q = 1 GROUP BY q1.a";

    $rows = $readConn->fetchAll($sql);

    if ($question) {
        $returnValue = false;

        foreach ($rows as $row) {
            if ($row['question'] === $question) {
                $returnValue = $row['total'];
            }
        }

        if ($returnValue) {
            return $returnValue;
        } else {
            return 0;
        }
    }

    return $rows;
}

The only problem is you can only have one participation_id per question-answer combo series (in this case 2 questions and answers: q1-a1 and q2-a2), but the number of times anyone can enter is limited to only 1 entry, so no duplicate participation_id's should be found anyways.

Hopefully anyone stumbling on this (or a similar) problem in the future has some use whith this answer.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.