I have several tables I need help to generate a query string. I am currently querying each of the tables and combining the results in the frontend which is making too many requests to the backend
Below is the structure of my tables (See this Sample sqlfiddle )
users
id | first_name | last_name |
---|---|---|
1 | John | Doe |
2 | Peter | Parker |
I guess eachid
column in each of your tables is unique, that is each table has no repeatedid
values. That's definitely the case in your fiddle.
And, I guess no user makes repeated attempts to answer a particular question in a particular event.
Here's a pure SQL query to get what you want. (https://www.db-fiddle.com/f/kRKsYAg954msn7bebtgUpg/2)
SELECT question_attempts.event_id,
users.id user_id, users.first_name, users.last_name,
SUM(questions.points) points
FROM question_attempts
JOIN questions ON question_attempts.question_id = questions.id
JOIN answers ON question_attempts.answer_id = answers.id
JOIN users ON question_attempts.user_id = users.id
WHERE answers.is_correct = 1
GROUP BY question_attempts.event_id,
users.id, users.first_name, users.last_name
Why does this work? Yourquestion_attempts
table ties all the other tables together.
JOIN questions ON question_attempts.question_id = questions.id
pulls in the question for each answer attempt. We need that because it says how manypoints
the question is worth.JOIN answers ON question_attempts.answer_id = answers.id
pulls in the answer for each attempt. We need that because we need to know whether the particular answeris_correct
.JOIN users ON question_attempts.user_id = users.id
gets us the users' names.Then, the SUM / GROUP BY adds up the points for correct answer for each event and user.
This query gets back all your events and users. You can add
WHERE ... AND question_attempts.event_id = 6
or some such thing to get the results for just one event.
And you can use
ORDER BY question_attempts.event_id,
SUM(questions.points) DESC,
users.id
to get your output in descending order of score (highest first) for each event.
This is example where triggers would come in handy. Use them to calculate Points directly into a table column. One you will have milions of rows and each time you gonna calculate this... over time it will get worst and worst.
Everytime you update answers you will update user row to update points.
Because your problem seems to be in houw to use ranking, I created an example on your questions table, and added a RANK on the points:
It is an example how to do RANKing (actually DENSE_RANK) in MySQL 5.6. When you are using MySQL 8.0 then you should user RANK or DENSE_RANK
SET @R:=0;
SET @P:=0;
SELECT
id,
description,
points,
CASE WHEN @P=points THEN @R ELSE @R:=@R+1 END as `RANK`,
@P:=points
FROM questions
ORDER BY points DESC
For output, see fiddle, or:
id | description | points | RANK | @P:=points |
---|---|---|---|---|
5 | What was the first disease to be discovered | 3.5 | 1 | 3.5 |
10 | A child has more bones than an adult | 3 | 2 | 3 |
8 | Average time to complete a Marathon is 2hrs | 3 | 2 | 3 |
7 | An earthworm can grow back when cut | 2 | 3 | 2 |
6 | The statement a swarm of butterfly is valid | 2 | 3 | 2 |
4 | Sudan is the largest country in Africa | 2 | 3 | 2 |
3 | A cat dies 9 times | 2 | 3 | 2 |
2 | Blood color changes | 2 | 3 | 2 |
1 | Water is blue | 2 | 3 | 2 |
9 | What is the value of one plus one divide two? | 1 | 4 | 1 |
Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.
Find the answer in similar questions on our website.
Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.
PHP (from the English Hypertext Preprocessor - hypertext preprocessor) is a scripting programming language for developing web applications. Supported by most hosting providers, it is one of the most popular tools for creating dynamic websites.
The PHP scripting language has gained wide popularity due to its processing speed, simplicity, cross-platform, functionality and distribution of source codes under its own license.
https://www.php.net/
DBMS is a database management system. It is designed to change, search, add and delete information in the database. There are many DBMSs designed for similar purposes with different features. One of the most popular is MySQL.
It is a software tool designed to work with relational SQL databases. It is easy to learn even for site owners who are not professional programmers or administrators. MySQL DBMS also allows you to export and import data, which is convenient when moving large amounts of information.
https://www.mysql.com/
Welcome to the Q&A site for web developers. Here you can ask a question about the problem you are facing and get answers from other experts. We have created a user-friendly interface so that you can quickly and free of charge ask a question about a web programming problem. We also invite other experts to join our community and help other members who ask questions. In addition, you can use our search for questions with a solution.
Ask about the real problem you are facing. Describe in detail what you are doing and what you want to achieve.
Our goal is to create a strong community in which everyone will support each other. If you find a question and know the answer to it, help others with your knowledge.