Help please with the compilation of SQL query!

Two parameters are passed to the PHP script:

  • subject_id - the topic ID of the test;
  • user_id - ID of the user who passed the test and in relation to which it is necessary to calculate the relevance;

Users who have been tested will be many, for this request it is very desirable to make it as optimized as possible.

As a result, you need to get a sample of users sorted by relevance of responses to the topic of testing.

The table with test subjects (subjects):

enter image description here

Table with tests (tests):

enter image description here

Test response table (options):

enter image description here

The table with the answers to each of the tests (answers):

enter image description here

  • one
    And what is the relevance and what kind of query has you managed to make up - Mike
  • @Mike so far no one managed to make up :) did not have to deal with such requests. If to speak bluntly, then even there are no intelligent thoughts. Relevance - in this case, I represent as an integer the determining number of identical answers on the test topic for each user in the sample. Those. For example, for a topic there are 3 tests with multiple choice answers for each of the tests, the user "A" in the tests chose the options [1,3,2], the user "B" chose [2,3,2] - in this case the value relevance for user "B" will be "2", because the last 2 answers are the same, but the first is not. - ragmon
  • And to make a request that for a given user simply selects all his answers to a given topic can? And then something like a picture to make a request without having a database is uncomfortable. - Mike

1 answer 1

The solution to the problem turned out to be not as difficult as I expected. It was enough to move a little brains and everything was resolved.

 # ID пользователя, по отношению к которому нужно вычислять релевантность SET @USER_ID = 1; # ID темы тестирования SET @SUBJECT_ID = 1; select u.*, /* подсчёт релевантности */ ( select count(*) from answers as sa where sa.subject_id = @SUBJECT_ID and sa.option_id = ame.option_id and sa.user_id = u.id ) as rel from users as u /* получаем ответы пользователя с которыми нужно сравнивать */ join answers as ame on ame.subject_id = @SUBJECT_ID and ame.user_id = @USER_ID /* получаем записи ответов всех пользователей по теме тестирования */ join answers as a on a.subject_id = @SUBJECT_ID and a.user_id = u.id /* исключаем пользователя из выборки, с которым нужно сравнивать */ and a.user_id != @USER_ID group by u.id # сортировка по релевантности order by rel desc