Добрый день!

Есть две таблицы связанные отношением многие-ко-многим - User и Role.
Необходимо сделать запрос с данными из этих двух таблиц, например с полями userId, userName, roleId, roleName.

select u.userId, u.userName, r.roleId, r.roleName
from user u, role r, user_role ur
where u.userId = ur.userId
and r.roleId = ur.roleId

При этом нужна разбивка на страницы и сортировка.
Сейчас реализовано в Spring:
- есть два Entity User и Role связанные Many-to-Many
- есть UserRepository и RoleRepository extends PagingAndSortingRepository

Это позволяет легко получить всех User и для каждого User массив Role. Но тогда пейджинг не верный - учитываются только User, а надо как в первом запросе (учитывать и роли). Плюс не работает сортировка по Role.

Есть вариант явно определить промежуточную таблицу и связать через One-To-Many и использовать UserRoleRepository. Пейджинг работает, но там возникает проблема с сортировкой и по User и по Role.

Собственно вопрос такой. Как с помощью JPA получить данные сразу из двух таблиц связанных многие-ко-многим, так чтобы и сортировка динамически задавалась и пейджинг работал.


Спасибо.

Пример данных:

1 | Иванов | 1 | Администратор
1 | Иванов | 2 | Руководитель
2 | Петров | 1 | Администратор
2 | Петров | 3 | Бухгалтерия
3 | Сидоров | 5 | Отдел рекламы

Соответственно пейджинг должен работать так. Если размер страницы 3 и page=0 , то должны вернуться 3 записи

1 | Иванов | 1 | Администратор
1 | Иванов | 2 | Руководитель
2 | Петров | 1 | Администратор

авторДа. Это направление связи со стороны Справочника юзверей. Работает?

Оно работает в том смысле, что возвращает всех user и у каждого user коллекция role:
Page<User> users и у каждого user List<Role>roles.

А надо чтобы возвращало коллекцию userId, userName, roleId, roleName:
List <UserRole>
где UserRole:
int userId,
String userName,
int roleId,
String roleName

Вернее даже не List <UserRole>, а Page<UserRole>

Предыдущее работает если использую связь Many-To-Many, без явного описания отдельной сущности UserRole.
Если явно описываю UserRole и использую репозиторий UserRoleRepository:
interface UserRoleRepository extends PagingAndSortingRepository<UserRoleRepository, String>
, то соответственно возвращаются только два поля: userId и roleId.

И сортировка по полям таблицы User и Role в этом случае не работает. Если сделать что-то типа
Page<UserRole>findAll(pageable) и в pageable.sort = username, то будет ошибка. Потому что сформируется запрос вида
select ur.* from user_role ur order by ur.username

Информация нужна именно в том виде в котором я описал, и соответственно смысл есть.
Если делать отдельно два простых запроса, то придется самостоятельно делать сложную реализацию пейджинга, фильтрации и сортировки. Это намного все усложняет. Сейчас только проблема в сортировке.

В принципе решение может быть, если понять как указать sort в pageable так чтобы он не добавлял алиас в order by.

То есть формируется запрос
select u.userId, u.userName, r.roleId, r.roleName
from user u, role r, user_role ur
where u.userId = ur.userId
and r.roleId = ur.roleId
order by ur. username

А надо без алиаса
select u.userId, u.userName, r.roleId, r.roleName
from user u, role r, user_role ur
where u.userId = ur.userId
and r.roleId = ur.roleId
order by username

Тогда должно заработать. Но как это сделать?

Ну, например, 10 записей. Плюс можно задать фильтры на имя user или имя role. Не совсем понятно, как влияет количество записей на проблему, которую я описал?

Не понятно. Запрос ограничен количеством, равным размеру страницы. Какой миллион? Не говоря о том что максимум 10 * 15 = 150.
select ur.* from ur limit 10.


Может быть кто-то делал выборку из таблиц связанных между собой многие-ко многим. Поделитесь пожалуйста.