Получение всех ожидающих запросов от одного ко многим сопоставлениям

У меня есть две таблицы (requests и requests_details), как показано ниже. Таблица requests содержит request_id и имя запроса, а requests_details содержит подробную информацию о том, находится ли запрос в ожидании, одобрен или отклонен. Я реализовал приведенные ниже две таблицы в сопоставлении один ко многим, используя Spring JPA. В таблице requests_details, если есть вторая запись, кроме статуса PENDING для запроса, это означает, что из статуса мы можем узнать, одобрен ли запрос или отклонен.

Теперь у меня есть требование получать все ожидающие и утвержденные запросы отдельно.

Я написал запрос hql, как показано ниже.

Для получения всех ожидающих запросов

SELECT request.name, details.* 
FROM requests request JOIN request_details details ON request.request_id=details.request_id 
WHERE 
details.status = "PENDING" AND 
details.status !="APPROVED" OR details.status !="REJECTED"

но для приведенного выше запроса он возвращает все запросы

Для получения всех одобренных запросов

SELECT request.name, details.* 
FROM requests request JOIN request_details details ON request.request_id=details.request_id 
WHERE 
details.status = "PENDING" AND 
details.status ="APPROVED"

но для приведенного выше запроса он не возвращает никаких запросов

введите здесь описание изображения

Мой ожидаемый результат, как показано ниже

введите здесь описание изображения

введите здесь описание изображения

Может ли кто-нибудь помочь мне в этом


java spring-data-jpa hibernate-5.x jpa-2.0
person Alex Man    schedule 06.02.2019    source источник
comment
Я не уверен, что вы пытаетесь получить, но details.status = "PENDING" AND details.status ="APPROVED", очевидно, невозможно удовлетворить, а details.status = "PENDING" AND details.status !="APPROVED" OR details.status !="REJECTED" эквивалентно (details.status = "PENDING" AND details.status !="APPROVED") OR details.status !="REJECTED" эквивалентно details.status = "PENDING" OR details.status !="REJECTED" эквивалентно details.status !="REJECTED", которое удовлетворяется всеми строками в данном примере данных.   -  person Jens Schauder    schedule 06.02.2019

Ответы (1)


arrow_upward
2
arrow_downward

Что происходит в первом запросе, так это то, что вы указываете, что статус должен быть «ОЖИДАНИЕ» и не «УТВЕРЖДЕНО» (это относится к «ОТКЛОНЕНО») или не «ОТКЛОНЕНО» (это относится ко всем другим случаям). ), поэтому вы получаете все.

Если вы пытаетесь получить все "ОЖИДАНИЕ", то просто

SELECT
  request.id,
  details.*
FROM requests request
  JOIN request_details details ON request.request_id = details.request_id
WHERE details.status = "PENDING"
AND (SELECT count(*) FROM requests_details
     WHERE requests_details.request_id = request.request_id) = 1

Подзапрос гарантирует, что для запроса существует только одна запись, что означает, что он находится только в состоянии «ОЖИДАНИЕ» и не был одобрен или отклонен.

Во втором случае вы ничего не получите взамен, потому что вы заявляете, что статус должен быть "ОЖИДАНИЕ" и "УТВЕРЖДЕНО" одновременно, и это всегда будет ложным.

Если вы хотите получить записи, которые являются «ожидающими» и «утвержденными», вы можете сделать что-то вроде этого

SELECT
  request.request_id,
  details.*
FROM requests request
  JOIN requests_details details ON request.request_id = details.request_id
WHERE details.status = 'PENDING'
  AND (SELECT count(*) FROM requests_details
         WHERE requests_details.request_id = request.request_id
           AND requests_details.status = 'APPROVED'
      ) = 1

Что происходит, так это то, что вы находите все «ожидающие» запросы, а затем убеждаетесь, что они также «УТВЕРЖДЕНЫ». Это делается с помощью подзапроса. Там вам нужно только знать, что запись с нужным идентификатором и статусом «УТВЕРЖДЕНО» существует, поэтому вы будете использовать подсчет, данные, которые у вас уже есть из предыдущей части.

ПРИМЕЧАНИЕ. Было бы неплохо обновить статус запросов с «ОЖИДАНИЕ» на «УТВЕРЖДЕНО» или «ОТКЛОНЕНО», а не вставлять новую запись. Это было бы безопаснее, а запросы были бы проще и быстрее (вам не понадобился бы подзапрос). Это также позволит избежать возможных ошибок, когда запросы имеют несколько конфликтующих статусов.

person Norbert Bicsi    schedule 06.02.2019
comment
Спасибо за ответ, на самом деле ваш запрос PENDING возвращает мне полные запросы, включая одобренный. Я обновил свой вопрос с ожидаемым результатом. Не могли бы вы взглянуть - person Alex Man; 06.02.2019
comment
@AlexMan ИТАК, ты имеешь в виду, что это должно быть только ОЖИДАНИЕ и больше ничего, верно? Я неправильно понял это. - person Norbert Bicsi; 06.02.2019
comment
@AlexMan Я обновил ответ, надеюсь, это то, что вы ищете. Не забудьте протестировать его, и, возможно, вам придется его немного подправить, ведь я пишу это без IDE и никогда его не запускал. Также, если это помогло вам, не забудьте проголосовать и принять это :) - person Norbert Bicsi; 06.02.2019