M Zeeshan•4mo ago

Retrieve Total Document Count Before Pagination

I want to retrieve the total number of documents found before pagination takes place. However, I tried the following code and encountered an error. error: Uncaught Error: A query can only be chained once and can't be chained after iteration begins. code:

let queryBuilder = ctx.db.query('skills');

if (title) {
      // @ts-ignore
      queryBuilder = queryBuilder.withSearchIndex('title', (q) =>
        q.search('title', title),
      );
    }

    queryBuilder = queryBuilder.filter((q) =>
      q.eq(q.field('isDeleted'), false),
    );

    // some code //

    if (type !== undefined) {
      queryBuilder = queryBuilder.filter((q) =>
        q.eq(q.field('type'), type),
      );
    }

    const totalFound = await queryBuilder.collect();
    const results = await queryBuilder.paginate(args.paginationOpts);

let queryBuilder = ctx.db.query('skills');

if (title) {
      // @ts-ignore
      queryBuilder = queryBuilder.withSearchIndex('title', (q) =>
        q.search('title', title),
      );
    }

    queryBuilder = queryBuilder.filter((q) =>
      q.eq(q.field('isDeleted'), false),
    );

    // some code //

    if (type !== undefined) {
      queryBuilder = queryBuilder.filter((q) =>
        q.eq(q.field('type'), type),
      );
    }

    const totalFound = await queryBuilder.collect();
    const results = await queryBuilder.paginate(args.paginationOpts);

51 Replies

jamalsoueidan•4mo ago

you cant call collect and paginate on the same querybuilder

M ZeeshanOP•4mo ago

how...?

jamalsoueidan•4mo ago

const giveMeNewVariable = () => { let queryBuilder = ctx.db.query('skills'); queryBuilder = queryBuilder.withSearchIndex('title', (q) => q.search('title', title), ); } queryBuilder = queryBuilder.filter((q) => q.eq(q.field('isDeleted'), false), ); // some code // if (type !== undefined) { queryBuilder = queryBuilder.filter((q) => q.eq(q.field('type'), type), ); } return queryBuilder ; } const totalFound = await giveMeNewVariable ().collect(); const results = await giveMeNewVariable().paginate(args.paginationOpts); try something like this

M ZeeshanOP•4mo ago

imo, same logic...

jamalsoueidan•4mo ago

not you get new variable

M ZeeshanOP•4mo ago

ok... let me try it

jamalsoueidan•4mo ago

what you are doing queryBuilder.collect().paginate() make it also short return ctx.db.query('skills').withSearchIndex('title', (q) => q.search('title', title), ).filter((q) => { return q.eq(q.field('isDeleted'), false), } )

M ZeeshanOP•4mo ago

same error.... Uncaught Error: A query can only be chained once and can't be chained after iteration begins. i only want to search with title if title is truthy

jamalsoueidan•4mo ago

i get it, but you are not allowed to chain the functions like that

M ZeeshanOP•4mo ago

i made it...

jamalsoueidan•4mo ago

perfect

M ZeeshanOP•4mo ago

 const queryBuilder = (query: typeof query1) => {
      if (title) {
        // @ts-ignore
        query = query.withSearchIndex('title', (q) =>
          q.search('title', title),
        );
      }

      query = query.filter((q) => q.eq(q.field('isDeleted'), false));

      if (type !== undefined) {
        query = query.filter((q) => q.eq(q.field('type'), type));
      }

      return query;
    };

    const totalFound = await queryBuilder(ctx.db.query('skills'))
.collect();
    const results = await queryBuilder(ctx.db.query('skills'))
.paginate(args.paginationOpts,);

 const queryBuilder = (query: typeof query1) => {
      if (title) {
        // @ts-ignore
        query = query.withSearchIndex('title', (q) =>
          q.search('title', title),
        );
      }

      query = query.filter((q) => q.eq(q.field('isDeleted'), false));

      if (type !== undefined) {
        query = query.filter((q) => q.eq(q.field('type'), type));
      }

      return query;
    };

    const totalFound = await queryBuilder(ctx.db.query('skills'))
.collect();
    const results = await queryBuilder(ctx.db.query('skills'))
.paginate(args.paginationOpts,);

it works... main point is to use same filters for 2 queries

jamalsoueidan•4mo ago

you can also try something like this and see if it works: query .withSearchIndex("title", (q) => (title ? q.search("title", title) : q)) .filter((q) => q.eq(q.field("isDeleted"), false)) .filter((q) => type !== undefined ? q.eq(q.field('type'), type) : q ); cleaner

M ZeeshanOP•4mo ago

my search filters are conditional

jamalsoueidan•4mo ago

i added the conditional inside the callbacks

M ZeeshanOP•4mo ago

okay... i didn't see well we cannot return q only

jamalsoueidan•4mo ago

did you try?

M ZeeshanOP•4mo ago

yep

jamalsoueidan•4mo ago

M ZeeshanOP•4mo ago

full code....

export const getMultiple = query({
  args: {
    title: v.optional(v.string()),
    isPublished: v.optional(v.boolean()),
    isFeatured: v.optional(v.boolean()),
    isPublicSearch: v.optional(v.boolean()),
    type: v.optional(skills_typeSchema),
    paginationOpts: paginationOptsValidator,
  },
  handler: async (ctx, args) => {
    const isPublicSearch = args.isPublicSearch;
    const title = args.title;
    const isPublished = args.isPublished;
    const isFeatured = args.isFeatured;
    const type = args.type;

    let query1 = ctx.db.query('skills');
    let query2 = ctx.db.query('skills');

    const queryBuilder = (query: typeof query1) => {
      if (title) {
        // @ts-ignore
        query = query.withSearchIndex('title', (q) =>
          q.search('title', title),
        );
      }

      query = query.filter((q) => q.eq(q.field('isDeleted'), false));

      if (!isPublicSearch && isPublished !== undefined) {
        query = query.filter((q) =>
          q.eq(q.field('isPublished'), isPublished),
        );
      }

      if (!isPublicSearch && isFeatured !== undefined) {
        query = query.filter((q) =>
          q.eq(q.field('isFeatured'), isFeatured),
        );
      }

      if (isPublicSearch) {
        query = query.filter((q) =>
          q.eq(q.field('isPublished'), true),
        );
      }

      if (type !== undefined) {
        query = query.filter((q) => q.eq(q.field('type'), type));
      }

      return query;
    };

    const totalFound = (await queryBuilder(query1).collect()).length;
    const results = await queryBuilder(query2).paginate(
      args.paginationOpts,
    );

    return {
      success: true,
      message: 'skills retrieved successfully!',
      ...results,
      totalFound,
    };
  },
});

export const getMultiple = query({
  args: {
    title: v.optional(v.string()),
    isPublished: v.optional(v.boolean()),
    isFeatured: v.optional(v.boolean()),
    isPublicSearch: v.optional(v.boolean()),
    type: v.optional(skills_typeSchema),
    paginationOpts: paginationOptsValidator,
  },
  handler: async (ctx, args) => {
    const isPublicSearch = args.isPublicSearch;
    const title = args.title;
    const isPublished = args.isPublished;
    const isFeatured = args.isFeatured;
    const type = args.type;

    let query1 = ctx.db.query('skills');
    let query2 = ctx.db.query('skills');

    const queryBuilder = (query: typeof query1) => {
      if (title) {
        // @ts-ignore
        query = query.withSearchIndex('title', (q) =>
          q.search('title', title),
        );
      }

      query = query.filter((q) => q.eq(q.field('isDeleted'), false));

      if (!isPublicSearch && isPublished !== undefined) {
        query = query.filter((q) =>
          q.eq(q.field('isPublished'), isPublished),
        );
      }

      if (!isPublicSearch && isFeatured !== undefined) {
        query = query.filter((q) =>
          q.eq(q.field('isFeatured'), isFeatured),
        );
      }

      if (isPublicSearch) {
        query = query.filter((q) =>
          q.eq(q.field('isPublished'), true),
        );
      }

      if (type !== undefined) {
        query = query.filter((q) => q.eq(q.field('type'), type));
      }

      return query;
    };

    const totalFound = (await queryBuilder(query1).collect()).length;
    const results = await queryBuilder(query2).paginate(
      args.paginationOpts,
    );

    return {
      success: true,
      message: 'skills retrieved successfully!',
      ...results,
      totalFound,
    };
  },
});

jamalsoueidan•4mo ago

nice

M ZeeshanOP•4mo ago

with dummy data this is public search https://uxweaver-git-main-uxweaver.vercel.app/skills

jamalsoueidan•4mo ago

looking good

M ZeeshanOP•4mo ago

thx how to return totalFound

    return {
      success: true,
      message: 'skills retrieved successfully!',
      ...results,
      totalFound,
    };

    return {
      success: true,
      message: 'skills retrieved successfully!',
      ...results,
      totalFound,
    };

how to get totalFound from usePaginatedQuery

  const { } = usePaginatedQuery()

  const { } = usePaginatedQuery()

@jamalsoueidan do you have any idea?

jamalsoueidan•4mo ago

in the frontend you need two different queries one special for pagination and one for the other const { results, status, loadMore } = usePaginatedQuery( api.message.paginate, { conversation: conversationId as Id<"conversation"> }, { initialNumItems: 25 } ); api.message.paginate cannot return other fields...

M ZeeshanOP•4mo ago

then i dont need totalFound I'm looking forward to the Convex team's response.

jamalsoueidan•4mo ago

I think they are going to sleep in few hours 😄

ballingt•4mo ago

@M Zeeshan Sounds like you want a paginated query and an unpaginated one? just catching up Paginated queries run the same query function multiple times, oncer per page

jamalsoueidan•4mo ago

export const paginate = query({
  args: {
..
  },
  handler: async (ctx, args) => {
    const paginate = await ctx.db
      .query("message")
      .paginate(args.paginationOpts);

const total = await ctx.db.query().count();

    return {
      ...total,
      page,
    };
  },
});

export const paginate = query({
  args: {
..
  },
  handler: async (ctx, args) => {
    const paginate = await ctx.db
      .query("message")
      .paginate(args.paginationOpts);

const total = await ctx.db.query().count();

    return {
      ...total,
      page,
    };
  },
});

or you can do this...

M ZeeshanOP•4mo ago

there is no count method

jamalsoueidan•4mo ago

its hiding from the documentation, you can just use length...

M ZeeshanOP•4mo ago

i want total number of docs found before pagination so i can tell user how many total docs were found with your applied filter

jamalsoueidan•4mo ago

you use exactly same query just not paginate...

M ZeeshanOP•4mo ago

to use .length i need to use collect first then .paginate and still same problem i cannot send totalFound to front-end as usePaginatedQuery only gives results and if i modify results it throw error

jamalsoueidan•4mo ago

you modify the return value just like i did

M ZeeshanOP•4mo ago

i know you did... but usePaginatedQuery does not give it back

jamalsoueidan•4mo ago

ahhh true so you have to use 2 different queries

M ZeeshanOP•4mo ago

maybe convex team need to modify this behaviour so we can pass more data along paginated results

ballingt•4mo ago

Sorry to be late on this, yeah I think you want to use two queries. Paginated queries work quite differently: they run multiple times, once per page. It's fine to use many conex queries in a web page or even the same React component because they all update at the same time: Convex provides a "consistent client view" so all these queries will update together.

M ZeeshanOP•4mo ago

you mean i need pagination conditionally with same filters

ballingt•4mo ago

If you want a count of how many items there are in a paginated query, the client is the easiest place to do that (just the length of the list). If you want the number of elements in the entire paginated query that can requires accessing a lot of data; might need to do that in an action or with a denormalized counter to avoid needing to read every single row. yeah if you use two queries, you'd want to pass the same arguments to both queries.

M ZeeshanOP•4mo ago

is this possible for convex team to modify usePaginatedQuery so it returns all else internal pagination data

ballingt•4mo ago

usePaginatedQuery is returning all the relevant data it has already a paginated query doesn't know the total number of elements if a full query would return 1 million elements, and the first page contained 200, the usePaginatedQuery hook doesn't know that there are 1 million elements if it did it would have to traverse at minimum a million DB records, or have some denormalized state to keep track of how many there are elsewhere

M ZeeshanOP•4mo ago

i mean .... if i return from function

return {
  ...results,
  totalFound,
  ...moreFields
}

return {
  ...results,
  totalFound,
  ...moreFields
}

usePaginatedQuery must return all data but i only returns ( results, loadMore, status and isLoading ) does it make sense if i request for this feature?

ballingt•4mo ago

So a paginated query runs many times, and the various pages of results get combined. If you're returning more information, we'd have to figure out how to combined those fields between every run of that function We could return the results from just the last page?

M ZeeshanOP•4mo ago

lets say there is unique prop e.g. results you can merge it everytime

ballingt•4mo ago

I think you want a separate query here, that eliminates this merging logic. And it'll be less wasteful, instead of calculating it once per page you'll just be calculating it once Consider factoring out your filter logic into a helper function that you can call from both the paginated query and this other query

M ZeeshanOP•4mo ago

sure i will explain my point of view in another feature request thread

ballingt•4mo ago

You can also write your own hook similar to usePaginatedQuery if there's something different you want here

M ZeeshanOP•4mo ago

its helpful

ballingt•4mo ago

I wouldn't want to add this extra merging logic to the default usePaginatedQuery hook because when people use it they'll be doing the less efficient thing than writing a separate query, since every page of the query will independently calculate these values.

Retrieve Total Document Count Before Pagination

Did you find this page helpful?