Xata•13mo ago

Different results using summarize vs filtering

Hi team (it's me, again), I'm seeing that, at least in the playground, there is a difference of results when using summaries vs using filtering query (I'll add examples). To make it short, summaries are only returning 20 results, whereas it should be returning more (22, in our current data set). Is there a limit of the number of results provided by a summarize call? It is returning only 20 results. I would like you guys to tell me that there is something very wrong in our code. Like we need to call summarize with something different. Note to Kostas: The workspace and the table and the branch are the same as yesterday 😉 Thanks!

6 Replies

Eusebio TrigoOP•13mo ago

import { getXataClient } from "./xata";
import { ge, le } from "@xata.io/client";

const xata = getXataClient();

const records = await xata.db.transactions
  .filter(
    "accounting_document_creation_date",
    ge(new Date("2022-01-01T00:00:00.000Z"))
  )
  .filter(
    "accounting_document_creation_date",
    le(new Date("2023-01-01T00:00:00.000Z"))
  )
  .filter(
    "credential_id",  "Some record Id"
  )
  .sort("order.bill_to_country_iso_code", "asc")
  .select([
    "order.bill_to_country_iso_code",
  ])
  .getAll();

console.log(records.map(record => record.order.bill_to_country_iso_code).filter((value, index, array) => array.indexOf(value) === index));

import { getXataClient } from "./xata";
import { ge, le } from "@xata.io/client";

const xata = getXataClient();

const records = await xata.db.transactions
  .filter(
    "accounting_document_creation_date",
    ge(new Date("2022-01-01T00:00:00.000Z"))
  )
  .filter(
    "accounting_document_creation_date",
    le(new Date("2023-01-01T00:00:00.000Z"))
  )
  .filter(
    "credential_id",  "Some record Id"
  )
  .sort("order.bill_to_country_iso_code", "asc")
  .select([
    "order.bill_to_country_iso_code",
  ])
  .getAll();

console.log(records.map(record => record.order.bill_to_country_iso_code).filter((value, index, array) => array.indexOf(value) === index));

Displays (with our current case 22 results). But using summaries:

import { getXataClient } from "./xata";
const xata = getXataClient();

const envId = "Some Record Id";
const year = 2022;

const summarizeResult = await xata.db.transactions.summarize({
  columns: ["order.bill_to_country_iso_code"],
  summaries: {},
  filter: {
    $exists: "order.bill_to_country_iso_code",
    credential_id: envId,
    accounting_document_creation_date: {
      $ge: new Date(`${year}-01-01T00:00:00Z`),
      $lt: new Date(`${year + 1}-01-01T00:00:00Z`),
    },
  },
});
const country_codes = summarizeResult.summaries.map(
  (item: any) => item.order.bill_to_country_iso_code
);
console.log("Country Codes", country_codes);

import { getXataClient } from "./xata";
const xata = getXataClient();

const envId = "Some Record Id";
const year = 2022;

const summarizeResult = await xata.db.transactions.summarize({
  columns: ["order.bill_to_country_iso_code"],
  summaries: {},
  filter: {
    $exists: "order.bill_to_country_iso_code",
    credential_id: envId,
    accounting_document_creation_date: {
      $ge: new Date(`${year}-01-01T00:00:00Z`),
      $lt: new Date(`${year + 1}-01-01T00:00:00Z`),
    },
  },
});
const country_codes = summarizeResult.summaries.map(
  (item: any) => item.order.bill_to_country_iso_code
);
console.log("Country Codes", country_codes);

(Apologies, i was hitting the 2k character limit)

kostas•13mo ago

Hey @Eusebio Trigo try this for summarize:

const summarizeResult = await xata.db.transactions.summarize({
  columns: ["order.bill_to_country_iso_code"],
  summaries: {},
  filter: {
    $exists: "order.bill_to_country_iso_code",
    credential_id: envId,
    accounting_document_creation_date: {
      $ge: new Date(`${year}-01-01T00:00:00Z`),
      $lt: new Date(`${year + 1}-01-01T00:00:00Z`),
    },
  },
  pagination: {
    size: 1000
}
});

const summarizeResult = await xata.db.transactions.summarize({
  columns: ["order.bill_to_country_iso_code"],
  summaries: {},
  filter: {
    $exists: "order.bill_to_country_iso_code",
    credential_id: envId,
    accounting_document_creation_date: {
      $ge: new Date(`${year}-01-01T00:00:00Z`),
      $lt: new Date(`${year + 1}-01-01T00:00:00Z`),
    },
  },
  pagination: {
    size: 1000
}
});

It just adds the pagination.size parameter. By default summarize returns up to 20 results, but can be adjusted up to 1000: https://xata.io/docs/sdk/summarize#page Let me know if the numbers align with this?

Eusebio TrigoOP•13mo ago

Thanks! I'll try right now. Doing more tests on other sides. This is why the constraint violation from yesterday. RTFM > The default setting for size , if not specified in the request, is 20 results. We allow you to set this between 1 and 1000. 😉

kostas•13mo ago

I see, sorry this ended up requiring extended troubleshooting but I'm glad we probably got to the bottom of it. Let me know how it goes though.

Eusebio TrigoOP•13mo ago

Don't be sorry. This was on us. And we are getting there! We got to the why. Just tested it in the playground, it displays the 22 iscodes, so i'm happy. Summarize > query and filter Like a charm! Thank you!

kostas•13mo ago

That's great news! Thanks for circling back

Gaming

Programming

Different results using summarize vs filtering