V
V
Vladislav Startsev2022-04-08 10:41:13
MySQL
Vladislav Startsev, 2022-04-08 10:41:13

How to form the order of requests to get users?

How to generate requests to get users with filtering.
Tables: users, education_session, test_history

structure:
users
624fe0ab8ea10397594627.jpeg
education_session
624fe0f5c1a83709752605.jpeg
test_history
624fe14b8e859869971627.jpeg

filter window:
624fe209d2fc6788519159.jpeg

user output (example):
624fe2bf963e8312506053.jpeg

A simple selection of users, without filters:
select distinct `users`.*, `subdivisions`.`title` as `subdivision_title`, `positions`.`title` as `position_title` 
from `users` 
left join `subdivisions` on `users`.`subdivision` = `subdivisions`.`id` 
left join `positions` on `users`.`position` = `positions`.`id` 
where `users`.`role_id` = 0 
order by `users`.`full_name` asc limit 25 offset 0

And difficulties begin with filtering, since each user can have several sessions to the main programs, a common session, to additional ones, each topic has its own session, data is stored in the testing history including histories from past sessions, when performing a test, data from the user ID is saved, session, topic, and the test itself. When sampling, data from the last sessions is obtained.
How to form the order of requests for displaying users? First, look at the data on the sessions for the selected topics, taking into account that there may be different sessions for different programs. Then look in the testing history to see if there is data for the selected topics for the last sessions, and then make a request to users, according to the data from the testing history.

Answer the question

In order to leave comments, you need to log in

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question