Laravel add calculate the sum where the value is in another table

Basically i have two tables. answers and choices . In my choices table, I got a column choices.value which is has values 0-4. My current query like this

 $answers = DB::table('answers')
                    ->join('choices','answers.choice_id','=','choices.choice_id')
                    ->select('answers.user_id','choices.choice_id','choices.choice','choices.value')
                    ->where('answers.user_id',Auth::user()->id)
                    //->groupBy('answers.user_id')
                    ->get();

My current response is like this

    "user_id": 2,
        "choice_id": 2,
        "choice": "I feel discourated about the future",
        "value": 1
    },
    {
        "user_id": 2,
        "choice_id": 2,
        "choice": "I don't enjoy things the way I used to",
        "value": 1
    },
    {
        "user_id": 2,
        "choice_id": 2,
        "choice": "I feel guilty a good part of time",
        "value": 1

how do i add the values so that my result will be like this

"user_id":2,
"total_score":3

I tried doing DB::raw(SUM(choices.values)as score) but i get a large amount. I guess its adding all the choices values from the choices table and not in the answers.

My answers db which i only select the answers of the user = 2. I limit to 5

+---------+-------------+-----------+
| user_id | question_id | choice_id |
+---------+-------------+-----------+
|       2 |           1 |         2 |
|       2 |           2 |         2 |
|       2 |           3 |         2 |
|       2 |           4 |         2 |
|       2 |           5 |         2 |
+---------+-------------+-----------+

My choices table, I only select questions 1 and 2 and their choices.

+-----------+-------------+--------------------------------------------------------------+-------+
| choice_id | question_id | choice                                                       | value |
+-----------+-------------+--------------------------------------------------------------+-------+
|         1 |           1 | I do not feel sad                                            |     0 |
|         2 |           1 | I feel sad                                                   |     1 |
|         3 |           1 | I am sad all the time and I can't snap out of it             |     2 |
|         4 |           1 | I am so sad and unhappy that I can't stand it                |     3 |
|         1 |           2 | I am not particularly discouraged about the future           |     0 |
|         2 |           2 | I feel discourated about the future                          |     1 |
|         3 |           2 | I feel I have nothing to look forward to                     |     2 |
|         4 |           2 | I feel the future is hopeless and that things cannot improve |     3 |
+-----------+-------------+--------------------------------------------------------------+-------+

Also i want to make a new table named scores and the columns will be the result of what i want. I want to add the choices.values in the answer in every answers.user_id so that when i view the scores table, it will display the total score in every user or when the user finished answering all 21 questions because i only got 21 items it will automatically add in the scores table. Could I possibly do that?. Is it okay to add a value in the answers table based in the choice_id? That is what im thinking but I think its redundant since the choice_id is there already. Thanks in advance.

PS: Tried writing these queries but always get 441 which is the total value of all the choices in the choices table

SELECT  answers.user_id,choices.choice_id,choices.value,COALESCE(sum(choices.value),0) as score  FROM 
`answers`  JOIN `choices` ON  choices.choice_id = answers.choice_id where 
answers.user_id = 2

SELECT answers.user_id,choices.choice_id,choices.value,SUM(choices.value),0 as score FROM `answers`
join choices on choices.choice_id = answers.choice_id
where answers.user_id = 2

SELECT answers.user_id,choices.choice_id, sum(choices.value) from answers 
JOIN `choices` ON  choices.choice_id = answers.choice_id
group by answers.user_id

Answer

Currently you are only matching to a choice but you need to match the choice as well as the question.

I found this after seeng each choices.question_id had choices 1, 2, 3 and 4 in a slack conversation. I didn’t know until I actually saw a screenshot of the choices table.

$answers = DB::table('answers')
                    ->join('choices', function ($q) {
                    $q->on('answers.choice_id','=','choices.choice_id')
                    ->on('answers.question_id', '=', 'choices.question_id');
                    })
                    ->select('answers.user_id',DB::raw('sum(choices.value) as total'))
                    ->groupBy('answers.user_id')
                    ->get();

Advertisement

Answer