How create SQL pagination difficult join query with duplicate data?

Question

I have several tables in the database. Users, profiles and user roles. The relationship of profiles and users one to one. The relationship of roles and users many to many.

To select all users, I send the following request:

SELECT A.role_id, A.role_name, A.user_id,B.user_username, B.user_password, B.profile_color_text, B.profile_color_menu, B.profile_color_bg FROM
  (SELECT Roles.role_id, Roles.role_name, UserRoles.user_id 
      FROM Roles INNER JOIN UserRoles ON Roles.role_id = UserRoles.role_id) AS A 
LEFT JOIN 
  (SELECT Users.user_username, Users.user_password, Profiles.profile_color_text, Profiles.profile_color_menu, Profiles.profile_color_bg, Profiles.profile_id 
      FROM Users INNER JOIN Profiles ON Users.user_id = Profiles.profile_id) AS B
ON A.user_id = B.profile_id;

The question is how do I select a pagination?

Define "pagination" in the context of your query/desired results. — 404
– 404, Commented Nov 24, 2018 at 14:20
For example, I want to get entries about 10 users. For example, I need 10 first users, but they have several roles. There may be more than 10 entries in the response, but it must be data about 10 users. — Ilya Zhukau
– Ilya Zhukau, Commented Nov 24, 2018 at 16:06

404 · Accepted Answer · 2018-11-24 16:42:43Z

2

I would get the 10 users first, then perform the joins. Two reasons for this:

Since you don't want specifically 10 results but just the results of 10 users, which could contain any number of rows, you can't get all the data then limit it, otherwise you could be getting 10 rows containing data for 5 users;
Even if point 1 were irrelevant because there was always a 1-1 relationship, and especially if the number of results is small like 10, it's faster to get those results first and then join on that smaller "table", rather than doing all your joins on all the data and then limiting it.

.

SELECT
    u.user_id,
    u.user_username,
    u.user_password,
    r.role_id,
    r.role_name,
    p.profile_id,
    p.profile_color_text, 
    p.profile_color_menu, 
    p.profile_color_bg
FROM (
    SELECT user_id, user_username, user_password
    FROM users
    ORDER BY ???
    OFFSET 10
    LIMIT 10
) AS u
LEFT JOIN profiles AS p
    ON u.user_id = p.profile_id
LEFT JOIN userroles AS ur
    ON u.user_id = ur.user_id
LEFT JOIN roles AS r
    ON ur.role_id = r.role_id

I assume you'll want some order, so I've put an ORDER BY in there - to be completed.

OFFSET added to get the second page of results; first page wouldn't require it, or would be OFFSET 0. Then a LIMIT of course to limit the page size.

I've also restructured the joins in a way that made more sense to me.

answered Nov 24, 2018 at 16:42

404

8,7622 gold badges34 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Dragos Rizescu Over a year ago

What if you want to order by p.profile_id?

Collectives™ on Stack Overflow

How create SQL pagination difficult join query with duplicate data?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related