Пользовательский алгоритм сортировки для большого количества данных

У меня есть большой объем данных, которые мне нужно сортировать определенным образом на основе поискового запроса, но я не уверен в лучшем подходе.

Данные, которые я пытаюсь сортировать, – это список курсов, сгруппированных по школам. Каждый курс преподается одной школой. Каждая школа может принадлежать к любому числу «партнерств», что представляет собой взаимосвязь между несколькими школами. Пользователь может искать любое количество курсов по названию курса.

Мне нужно отсортировать данные следующим образом:

Курсы сгруппированы по школе, при этом на страницу появляются 10 школ.
Школы, которые могут предоставить каждый курс, который пользователь искал, должны отображаться первыми в списке.
После этих результатов школы, принадлежащие к партнерству, которые могут вместить все курсы, которые пользователь искал, должны появляться рядом друг с другом.

Вот пример:

Преподает курсы истории, французского и английского языков.
B учит французскому языку и математике.
C учит истории.
B и C находятся в партнерстве вместе.
D учит истории.
Пользователь выполняет поиск «История» и «Французский».
A должен появиться первым в результатах, с его историей и курсами французского языка, поскольку он может обеспечить оба курса, которые пользователь ищет.
B , а затем C появляется следующий, с соответствующим курсом, который он преподает в списке после него, поскольку партнерство может предоставить оба требуемых курса пользователя.
D Появляется дальше, поскольку он обеспечивает только 1 соответствующий курс.

Данные хранятся в базе данных Microsoft SQL Server в нескольких таблицах. Вот упрощенная схема:

Курс:

int id
имя varchar
int schoolId

Школа:

int id
имя varchar

Партнерство:

int id
varchar partnerName

SchoolPartnership:

int id
int schoolId
int associationId

Есть более 100000 курсов и около 300 школ. Я не знаю, как сортировать курсы, как указано в SQL, что я считаю самой большой проблемой. Мне нужно отображать только 10 результатов на странице, но поскольку я не могу выполнить сортировку в SQL-запросе, мне нужно извлечь весь набор результатов и отсортировать его вручную на PHP, прежде чем я могу сократить результат до 10 результатов.

В настоящее время я извлекаю данные, которые мне нужны, в одном запросе с несколькими объединениями, используя Doctrine 2, усугубляя результаты в виде массива. Затем план состоит в том, чтобы манипулировать этим большим массивом записей в PHP, чтобы получить его в правильном порядке. Из-за размера этого массива я опасаюсь, что этот процесс сортировки будет очень медленным, поэтому я ищу советы о том, как сделать это быстрее, либо:

Обработка сортировки в SQL-запросе.
Предлагая, как алгоритм, такой как описанный, может быть реализован в поисковой системе, такой как Solr (у меня мало опыта в основах этого, но не при выполнении сложной сортировки).
Предложения о том, как лучше всего выполнять сортировку в PHP, если два других варианта не являются жизнеспособными.

РЕДАКТИРОВАТЬ:

Я сделал хороший прогресс в этом, спасибо (особенно @Neil). Я открыл отдельный вопрос ( Groupwise MAX () в подзапросе ), который до сих пор содержит некоторые мои успехи.

Related of "Пользовательский алгоритм сортировки для большого количества данных"

Найти школы по количеству подходящих курсов просто:

SELECT schoolId, COUNT(*) AS schoolCount FROM Courses WHERE name IN ('History', 'French') GROUP BY schoolId

Если это все, что вам нужно, вы можете ORDER BY schoolCount DESC чтобы получить их в том порядке, в котором вы хотите.

Чтобы найти партнерские отношения с соответствующими курсами, вам сначала нужно найти партнерские отношения, которые имеют курс, по крайней мере, в одной школе:

 SELECT partnershipId, COUNT(DISTINCT name) AS partnershipCount FROM SchoolPartnership INNER JOIN Courses ON Course.schoolId = SchoolPartnership.schoolId WHERE name IN ('History', 'French') GROUP BY partnershipId

Обратите внимание, что DISTINCT необходимо, потому что нам все равно, сколько школ в партнерстве имеет этот курс. Если у вас нет DISTINCT вы можете использовать подзаголовок:

 SELECT partnershipId, COUNT(*) AS partnershipCount FROM ( SELECT DISTINCT partnershipId, name FROM SchoolPartnership INNER JOIN Courses ON Course.schoolId = SchoolPartnership.schoolId WHERE name IN ('History', 'French')) GROUP BY partnershipId

Затем вы можете использовать первый и последний запрос выше в качестве подзапросов в соединении с SchoolPartnership, чтобы заказать школы в порядке убывания партнерстваMatches и schoolMatches. (Заметим, что я предполагаю, что все школы находятся в партнерстве по крайней мере с одной школой.) Я думаю, что окончательный запрос будет выглядеть так:

 SELECT SchoolMatches.schoolID FROM ( SELECT schoolId, COUNT(*) AS schoolCount FROM Courses WHERE name IN ('History', 'French') GROUP BY schoolId ) SchoolMatches JOIN SchoolPartnership ON SchoolMatches.schoolID = SchoolPartnership.schoolID JOIN ( SELECT partnershipId, COUNT(DISTINCT name) AS partnershipCount FROM SchoolPartnership INNER JOIN Courses ON Course.schoolId = SchoolPartnership.schoolId WHERE name IN ('History', 'French') GROUP BY partnershipId ) PartnershipMatches ON SchoolPartnership.schoolId = PartnershipMatches.schoolId ORDER BY PartnershipMatches.partnershipCount DESC, SchoolMatches.SchoolCount DESC

У нас была аналогичная проблема с страницами сайта. Мы создали специальную denormilized таблицу поиска со всеми параметрами для выполнения поиска без подзапросов или объединений. Все данные дублировались, поэтому, когда что-то меняется, мы обновляем все данные denormalizar. Мы использовали фоновые задачи для синхронизации данных, поэтому результаты поиска могут быть неактуальными в течение небольшого времени.

Может быть, это кажется сложным, но только в том случае, если ваши данные и запрос будут расти.

 filter_var('sgamgee@example.com', FILTER_VALIDATE_EMAIL); // Returns "sgamgee@example.com"

Это действительный адрес электронной почты.