How to count results from a query

[andregoldstein]

Hey guys I'm perhaps missing something simple here but how can I go about counting the results of a query?

I can grab a range of documents from a query using range(0,10) but I'd also like/need to be able to know how many total results matched in order to properly display pagination.

Thanks!

[soedirgo]

Hey @andregoldstein! There's a PR here that adds this feature. I'll be updating here when we update the client (should be sometime soon).

[soedirgo]

OK, the client has been updated, so now you should be able to do this:

const { error, data, count } = await supabase
    .from('table')
    .select('*', { count: 'exact' })

Let me know if it works!

[andregoldstein]

Thanks! Will give it a go as soon as I can!

[himeshp]

This does not seem to work on the dart library?

[soedirgo]

@himeshp for Dart you can try it like the "query data" example here: https://pub.dev/packages/supabase/example

[himeshp]

yes, thanks

[saltcod]

Hummm I think I might be missing something. I just want the count of a query:

This works, but I don't think it's efficient?

let { data, error, status } = await supabase
	.from("users")
	.select("*", { count: "exact" }) // if you don't want to return any rows, you can use { count: 'exact', head: true }
	.eq("role", "developer");
	console.log(data.length);

Seem like this is the recommended solution, but doesn't work for me (returns null):

let { data, error, status } = await supabase
	.from("users")
	.select("*", { count: "exact", head: true })
	.eq("role", "developer");
	console.log(data); // null

Any nudges appreciated!

[GaryAustin1]

Count comes back in count. Head returns no data so null.

[saltcod]

Right right. { data, error, status, count }

duh! thanks for this!

[ogrotten]

I'm getting null from count here as well... so how is this done to get just a row count and no data?

I have:

const { data, error, status, count } = await supabase
	.from('users`)
	.select('*', { count: 'exact', head: true })
console.log(count) // null

What am I missing here?

Is there a doc on it that I'm missing? I'm going from this doc page here and it doesn't mention the status parameter.

[GaryAustin1]

@ogrotten except for your typo on users which I doubt is in your real code, I don't see anything wrong with your call.
Count comes back 0 if RLS is on and user does not have access, BUT it is null if table does not exist (you are not trying to access auth.users are you as you can't from the API).
Are you getting an error?
Status reflects the http status (200,404).

[max-programming]

@GaryAustin1 I have the same code but I also get null
Except when I remove the head property, I get the array

const { data } = await supabase
  .from("properties")
  .select("*", { count: "exact", head: true })
console.log(data) // null

const { data } = await supabase
  .from("properties")
  .select("*", { count: "exact" })
console.log(data) // [{ ... }, { ... }]

UPDATE: I thought the data property contained the count, But there is a count property

const { count } = await supabase
  .from("properties")
  .select("*", { count: "exact", head: true })
console.log(count) // 745

[michael-sumner]

The count property helped me with obtaining the row count, thanks @max-programming !

const { count, error } = await supabase
  .from("properties")
  .select("*", { count: "exact", head: true })

if (error) throw error;

console.log(count) // 745

return count;

[tjmcdonough]

How can you do this via an API call instead of using the supabase libary?

[tjmcdonough]

Do not worry, found it

Request
  GET /rest/v1/{table-name}
    ?id=gt.10
    &order=id.asc
    &limit=10
  apikey ...supabase access key...
  Prefer count=exact
Response
  Content-Range: 0-9/100

[antonio-bluco]

This doesn't seem to be available when selecting from a rpc results, i.e.

supabase.rpc('my_function').select('*', {count: 'exact'})

The typing system complains that 'count' does not exist, and the count returned is in fact always null.
Any ideas on what to do here?

[elyobo]

rpc seems to require that you pass { count: 'exact' } in the rpc call instead, e.g.

supabase.rpc('my_function',  {count: 'exact'}).select('*')

The returned type from .rpc().select() is different too, not sure whether it should be or not.

[BorisBarzotto]

rpc count always return null. Some idea?, no problem with data array but count always null.
using:
@supabase/supabase-js": "^2.42.7"
@supabase/ssr": "0.5.1"

[GaryAustin1]

Do you have a table returning function?
https://docs.postgrest.org/en/v12/references/api/functions.html