dumb SQL Q: Count from a query

Question

I'm sure this will probably get knocked as a duplicate but I cannot for the life of me figure out what is going on in an SQL query. Currently I am running two separate queries to 1). gather the table content I want and 2). run the same query just instead count...

First thing is first It's postgres. In the query it is paginated by the limit clause so I think I'm off to a bad start but I'm asking a dumb question already might as well commit to it.

SELECT *
        FROM test_case_versions v
            INNER JOIN test_cases t ON v.version_id = t.current_version
            LEFT OUTER JOIN categories_full c ON c.category_id = t.category_id
            INNER JOIN test_case_labels tcl ON tcl.test_case_id = t.test_case_id
            INNER JOIN labels l on l.label_id = tcl.label_id
        WHERE c.product_id = " . $user->product_id . "
            AND label_name ILIKE '$search2'
        ORDER BY t.test_case_id ASC
        LIMIT " . SEARCH_RESULTS_PER_PAGE . " OFFSET $offset";

So that statement gets me a result_set but only hands me back 100 results out of lets say 15000 because of the limit clause below. Is there any way BEFORE I run my limit clause to count the total results or is this not possible?

Any help would be greatly appreciated

What do you mean getting total records count before getting 100 records as result — Rams
– Rams, Commented Sep 8, 2017 at 16:50
So if I run this command as is, the query will stop at the SEARCH_RESULTS_PER_PAGE number which I failed to include is 100. It will never give you a total number of results greater than 100 because it will only return back that many and force you to go to the next page. — baksyl
– baksyl, Commented Sep 8, 2017 at 17:06

Error_2646 · Accepted Answer · 2017-09-08 16:53:16Z

3

Something like this might work.

SELECT *
  FROM (SELECT v.*,
               t.*,
               c.*,
               tcl.*,
               l.*,
               COUNT(*) OVER() AS cnt
          FROM test_case_versions v
         INNER 
          JOIN test_cases t 
            ON v.version_id = t.current_version
          LEFT 
          JOIN categories_full c 
            ON c.category_id = t.category_id
         INNER 
          JOIN test_case_labels tcl 
            ON tcl.test_case_id = t.test_case_id
         INNER 
          JOIN labels l 
            ON l.label_id = tcl.label_id
         WHERE c.product_id = " . $user->product_id . "
           AND label_name ILIKE '$search2'
       ) AS TMP
 ORDER 
    BY test_case_id ASC
 LIMIT " . SEARCH_RESULTS_PER_PAGE . " OFFSET $offset";

answered Sep 8, 2017 at 16:53

Error_2646

3,8991 gold badge17 silver badges26 bronze badges

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

Comments

nico boey · Accepted Answer · 2017-09-08 16:56:38Z

2

I get this question a lot, and my answer is always the same: why would you have to know the EXACT number of rows? Do you EVER look at the number of hits when you look up something in Google, and if you do, do you think it is an exact number? The whole purpose of paging results, is that you limit the burden on the server (and on the network), it kind of defeats that purpose if you do a count of all the records.

answered Sep 8, 2017 at 16:56

nico boey

3891 silver badge5 bronze badges

2 Comments

baksyl Over a year ago

This is how it was originally designed honestly and I've been tasked with changing as little as possible. Although it might not be brilliant, it is functionality that should remain.

nico boey Over a year ago

If you really must... sigh... then two separate queries make more sense; if you run them asynchronously, at least the count query won't bog down the user interface and the real results.

Collectives™ on Stack Overflow

dumb SQL Q: Count from a query

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related