PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How can I write the two queries in one query?

I use infinite scroll at my site and I have to return two variables.

The first one is data like 10 users.
The second one is total. Total all users from my whole table.

Now my query is this:

const total= await pg.query('SELECT COUNT(*) as total FROM users;');
const users = await pg.query('SELECT * FROM users LIMIT 50 OFFSET $1', [offset]);

I do not fetch all users directly I set a limit, but I have to tell my infinite scroll how many users I have in my table. So I wrote two queries. Is it possible to merge both into a single query?

Tags:

2

Answers


  1. 0

    You could write a subquery

    const users = await pg.query('SELECT *,(SELECT COUNT(*) as total FROM users) AS Total_count FROM users LIMIT 50 OFFSET $1', [offset]);

    Every row would have the total user count as further row, which you can exract.

    FYI SELECT * is bad style.

    Login or Signup to reply.
  2. 0

    If there is the possibility (even a remote one) that OFFSET might equal or exceed the total row count (or you add filters that return no row), you also get no total count from a nested, uncorrelated subquery like has been suggested.
    Use an outer join instead – LEFT JOIN in the example:

    SELECT *
FROM  (SELECT count(*) AS total_count FROM users) c  -- always returns 1 row
LEFT  JOIN (SELECT * FROM users LIMIT 50 OFFSET 10) u ON true

    Same cost, but you always get a least one row with the total count – other columns filled with null values, if no rows from u qualify.

    The best query also depends on the actual list of columns you need to return (SELECT * is typically wasteful), the undisclosed ORDER BY and WHERE clauses you typically add to the subquery u in such queries, available indexes, and (possibly) your Postgres version.

    Related:

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search