Queries

Queries from JavaScript can be written in a SQL-Like syntax.

Simple Query

Queries can be constructed using the Query class. Calling the toReadRequest method returns a ReadRequest, which can be passed to the authRequest.read method to retrieve the result set.

src / pages / index.tsx

import { useAuthRequest, Query } from '@ixeta/xams'
import React, { useCallback, useEffect, useState } from 'react'

type Widget = {
  WidgetId: string
  Name: string
  Price: number
}

const Index = () => {
  const authRequest = useAuthRequest()
  const [widgets, setWidgets] = useState<Widget[]>([])

  const loadWidgets = useCallback(async () => {
    // Create Query
    const readRequest = new Query(['*']).from('Widget').toReadRequest()
    // Pass ReadRequest object to authRequest
    const response = await authRequest.read<Widget>(readRequest)
    if (!response.succeeded) {
      console.log(response.logMessage)
    }

    // Set the results
    setWidgets(response.data.results)
  }, [authRequest])

  // See best practices regarding using Tanstack Query
  // when calling onLoad
  useEffect(() => {
    loadWidgets()
  }, [loadWidgets])

  return (
    <div>
      {widgets.map((widget, index) => (
        <div key={index}>{widget.Name}</div>
      ))}
    </div>
  )
}

export default Index

By default the top 5 records are returned. See the Top section below to change this.

Fields

Select the fields to return by providing a string array to the Query constructor.

Query

const readRequest = new Query(['Name', 'CreatedDate'])
  .from('Widget')
  .toReadRequest()

Select all fields by providing a *.

Query

const readRequest = new Query(['*']).from('Widget').toReadRequest()

Filtering

Results can be filtered using the where method on the Query class.

Query

const readRequest = new Query(['*'])
  .from('Widget')
  .where('Price', '>', 0)
  .toReadRequest()

When filtering on a DateTime field, the value must be in ISO 8601 format. ie: 2025-10-01 or 2025-10-01T00:00:00Z (YYYY-MM-DD or YYYY-MM-DDTHH:MM:SSZ)

Additional criteria can be provided using the and and or methods.

Query

const readRequest = new Query(['*'])
  .from('Widget')
  .where('Price', '>', 0)
  .and('Name', 'Contains', 'XX0')
  .toReadRequest()

const readRequest = new Query(['*'])
  .from('Widget')
  .where('Price', '>', 0)
  .or('Name', 'Contains', 'XX0')
  .toReadRequest()

Filter Operators

The following are valid filter operators. Contains applies to string fields only.

== != > < >= <= Contains

Inner Join

Inner joins can be performed by using the join method.

Query

const readRequest = new Query(['*'])
  .from('Account')
  .join('Account.AccountId', 'Contact.AccountId', 'c', [
    'FirstName',
    'LastName',
  ])
  .toReadRequest()

The parameters for a join are -

From table and field
To table and field
Alias
Fields to include (Can be '*' for all fields)

Left Join

Left joins can be performed using the leftjoin method.

Query

const readRequest = new Query(['*'])
  .from('Account')
  .leftJoin('Account.AccountId', 'Contact.AccountId', 'c', [
    'FirstName',
    'LastName',
  ])
  .toReadRequest()

Filter on Join Fields

Queries can be filtered on their joined tables by referencing their alias in the filters.

Query

const readRequest = new Query(['*'])
  .from('Account')
  .join('Account.AccountId', 'Contact.AccountId', 'c', [
    'FirstName',
    'LastName',
  ])
  .where('c.FirstName', 'Contains', 'Mark')
  .toReadRequest()

Cascading Join

When joining multiple tables, you can reference previously joined tables using their aliases to establish further joins.

Query

const readRequest = new Query(['*'])
  .from('Account')
  .join('Account.AccountId', 'Contact.AccountId', 'c', [
    'FirstName',
    'LastName',
  ])
  // Join on contact using the alias 'c'
  .join('c.AddressId', 'Address.AddressId', 'a', [
    'AddressLine1',
    'AddressLine2',
    'PostalCode',
    'City',
    'State',
    'Country',
  ])
  .toReadRequest()

Top

The number of results returned can be limited by using the top method. In the example below, the first 100 results are returned.

Query

const readRequest = new Query(['*']).top(100).from('Widget').toReadRequest()

Paging

Paging can be performed by using the page method. The below example will return records 21 to 30.

Query

const readRequest = new Query(['*'])
  .top(10)
  .page(2)
  .from('Widget')
  .toReadRequest()

Order

The order of records can be set using the orderBy method.

Query

const readRequest = new Query(['*'])
  .from('Widget')
  .orderBy('Name', 'asc')
  .toReadRequest()

Parameters for orderBy are -

Name of field
Order ("asc", "desc")

There can be multiple orders. In the example below, first the Widgets are ordered by Price, then by Name.

Query

const readRequest = new Query(['*'])
  .from('Widget')
  .orderBy('Price', 'desc')
  .orderBy('Name', 'asc')
  .toReadRequest()

Ordering can also be performed on joined tables.

Query

const readRequest = new Query(['*'])
  .from('Account')
  .leftJoin('Account.AccountId', 'Contact.AccountId', 'c', [
    'FirstName',
    'LastName',
  ])
  .orderBy('c.LastName', 'asc')
  .toReadRequest()

Denormalize

Denormalize is useful when joined tables need to return their data in arrays. Denormalization can be enabled by using the denormalize method.

For example, a query without denormalization like the query below will return the following json.

Query

const readRequest = new Query(['AccountId', 'Name'])
  .from('Account')
  .join('Account.AccountId', 'Contact.AccountId', 'c', ['*'])
  .toReadRequest()

Response - Without Denormalization

{
  "succeeded": true,
  "data": {
    "totalResults": 4,
    "results": [
      {
        "AccountId": "c4284ccd-b393-40bc-8e32-3e031a66055e",
        "Name": "MegaCorp, Inc.",
        "c.ContactId": "d8984649-956c-4844-9db8-8c667b904725",
        "c.FirstName": "John",
        "c.LastName": "Smith"
        // ... (2 more MegaCorp contacts)
      },
      {
        "AccountId": "d846ee11-78c3-49f4-9cc0-0e4470f5bf42",
        "Name": "SuperStore",
        "c.ContactId": "ae454123-d060-4099-9b05-f3a0e49f75ce",
        "c.FirstName": "Sam",
        "c.LastName": "Richards"
        // ... (1 more SuperStore contact)
      }
      // Note: One row per contact (4 total rows for 2 accounts)
    ]
  }
}

When denormalization is enabled as seen in the query below, the following json is returned.

Query

const readRequest = new Query(['AccountId', 'Name'])
  .from('Account')
  .join('Account.AccountId', 'Contact.AccountId', 'c', ['*'])
  .denormalize()
  .toReadRequest()

Response - With Denormalization

{
    "succeeded": true,
    "data": {
        "denormalize": true,
        "totalResults": 2,
        "results": [
            {
                "AccountId": "c4284ccd-b393-40bc-8e32-3e031a66055e",
                "Name": "MegaCorp, Inc.",
                "c": [
                    { "ContactId": "...", "FirstName": "John", "LastName": "Smith" },
                    { "ContactId": "...", "FirstName": "Rick", "LastName": "Stanley" }
                ]
            },
            {
                "AccountId": "d846ee11-78c3-49f4-9cc0-0e4470f5bf42",
                "Name": "SuperStore",
                "c": [
                    { "ContactId": "...", "FirstName": "Sam", "LastName": "Richards" },
                    { "ContactId": "...", "FirstName": "Jack", "LastName": "Frost" }
                ]
            }
            // Note: Contacts grouped in arrays (2 rows, one per account)
        ]
    }
}

Key Difference: Contacts are now grouped into arrays named after the alias (c), resulting in one row per account instead of one row per contact.

Best Practices

When using queries in a React application, it is recommended to use Tanstack Query to manage the server state.

Query

import { Query, useAuthRequest } from '@ixeta/xams'
import { useQuery, useQueryClient } from '@tanstack/react-query'

const CouponsPage = () => {
  const router = useRouter()
  const authRequest = useAuthRequest()
  const queryClient = useQueryClient()
  const query = useQuery({
    queryKey: ['coupons'],
    queryFn: async () => {
      const readRequest = new Query(['*'])
        .top(100)
        .from('Coupon')
        .where('IsActive', '==', 'true')
        .and('ProductName', 'Contains', searchRef.current)
        .toReadRequest()
      const readResp = await authRequest.read<Coupon>(readRequest)
      if (!readResp.succeeded) {
        throw new Error(readResp.friendlyMessage)
      }
      return readResp.data
    },
  })
  const [search, setSearch] = useDebouncedState('', 200)
  const searchRef = useRef(search)
  const searchInputRef = useRef(null)

  if (searchRef.current !== search) {
    searchRef.current = search
    queryClient.invalidateQueries({
      queryKey: ['coupons'],
    })
  }

  if (query.isError || query.data == null) {
    return <ErrorMessage message={query.error?.message} />
  }

  return (
    <div>
      <Text>My Digital Coupons</Text>
      <div>
        <TextInput
          label="Search"
          defaultValue={search}
          onChange={(e) => setSearch(e.currentTarget.value)}
          ref={searchInputRef}
        />
      </div>
      {query.data.results.length === 0 && (
        <div>
          <Text>No Coupons Found</Text>
        </div>
      )}
      <div>
        {query.data?.results.map((coupon) => {
          return (
            <Card key={coupon.CouponId}>
              <div>
                <Text>{coupon.ProductName}</Text>
              </div>
              <Button
                onClick={() =>
                  router.push(
                    addUserIdUrlParam(
                      router.asPath,
                      `/${coupon.RedemptionCode}`,
                    ),
                  )
                }
              >
                Redeem
              </Button>
            </Card>
          )
        })}
      </div>
    </div>
  )
}

export default CouponsPage