CODEWITHSUNDEEP
sqlpostgresql
baksyl
baksyl

Reputation: 67

dumb SQL Q: Count from a query

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

Upvotes: 1

Views: 280

Answers (2)

nico boey
nico boey

Reputation: 389

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.

Upvotes: 2

Error_2646
Error_2646

Reputation: 3791

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";

Upvotes: 3

Related Questions