CODEWITHSUNDEEP
phpmysqlsqllaravellaravel-5
SVM
SVM

Reputation: 433

Single Query in Laravel

How to calculate (score/no of question) in laravel/mysql/php single query

+=============+     +=============+    +===============+
|    User     |     |    Taken    |    |    Answers    |
+=============+     +=============+    +===============+
|     id      |     |   taken_id  |    |   answer_id   |
+-------------+     +-------------+    +---------------+
|     name    |     | user_id(FK) |    |  taken_id(FK) |
+-------------+     +-------------+    +---------------+
                    |  taken_date |    |  question_id  |
                    +-------------+    +---------------+
                                       |     choice    |
                                       +---------------+
                                       |      score    |
                                       +---------------+

I tried following:(I got scores of user_id from 2 tables Taken and answer)

SELECT a.user_id, answers.score FROM (
    SELECT MAX(taken.taken_id) AS taken_id, taken.user_id FROM taken 
    GROUP BY taken.user_id ) a, answers 
WHERE a.taken_id = answers.taken_id 
AND answers.score IS NOT null

But how to use User table details in this query

Upvotes: 1

Views: 100

Answers (2)

M Khalid Junaid
M Khalid Junaid

Reputation: 64476

You can add a join to user table to get the details of user

select u.id,
       u.name,
       a.score 
from (
    select max(taken_id) as taken_id, 
    user_id 
    from taken 
    group by user_id
) t
join answers a on t.taken_id = a.taken_id 
join user u on u.id = t.user_id
where a.score is not null

To get the sum of score for each user you can use following 

select u.id,
       u.name,
       sum(a.score) score
from (
    select max(taken_id) as taken_id, 
    user_id 
    from taken 
    group by user_id
) t
join answers a on t.taken_id = a.taken_id 
join user u on u.id = t.user_id
where a.score is not null
group by u.id,u.name

Upvotes: 1

Sachin Vairagi
Sachin Vairagi

Reputation: 5344

You can try - 

DB::raw('select a.user_id, answers.score from (select max(taken.taken_id) as taken_id, taken.user_id from taken group by taken.user_id) a, answers where a.taken_id = answers.taken_id and answers.score is not null
');

Make sure you have imported DB class.

Upvotes: 0

Related Questions