<br />

Firebase SQL Connect lets you create connectors for your PostgreSQL
instances managed with Cloud SQL. These connectors are
combinations of a
queries and mutations for using your data from your schema.

> [!NOTE]
>
> **Note:** Follow the complete series on building SQL Connect
> schemas and connectors, which covers:
>
> - [Schema development](https://firebase.google.com/docs/sql-connect/schemas-guide.md.txt)
> - Query development (this guide)
> - [Mutation development](https://firebase.google.com/docs/sql-connect/mutations-guide.md.txt)

The [Get started guide](https://firebase.google.com/docs/sql-connect/quickstart.md.txt) introduced a movie
review app schema for PostgreSQL.

That guide also introduced both deployable and ad hoc administrative
operations, including queries.

- **Deployable queries** are those you implement to call from client apps, with API endpoints you define. You bundle them into a **connector** deployed to the server. SQL Connect tooling generates client SDKs based on your API. Deployed queries aren't protected by IAM policy, so be sure to secure them using the SQL Connect `@auth` directive.
- **Ad hoc administrative queries** are run from privileged environments to read data. You can create and execute them in the Firebase console or in local development environments using our SQL Connect VS Code extension.

This guide takes a deeper look at **deployable queries**.

## Features of SQL Connect queries

SQL Connect lets you perform basic queries in all the ways
you'd expect given a PostgreSQL database.

But with SQL Connect's extensions to GraphQL, you can implement
advanced queries for faster, more efficient apps:

- Use **key scalars** returned by many operations to simplify repeated operations on records
- Perform queries in the course of a multi-step mutation operations to look up data, saving lines of code and round trips to the server.

> [!NOTE]
> **Note:** Before using this guide, review the movie review schema.
>
> #### Movie review app schema
>
> ```graphql
>       type User @table {
>       id: String! @default(expr: "auth.uid")
>       username: String! @col(dataType: "varchar(50)")
>       }
>
>       type Review @table(name: "Reviews", key: ["movie", "user"]) {
>       user: User!
>       movie: Movie!
>       rating: Int
>       reviewText: String
>       reviewDate: Date! @default(expr: "request.time")
>       }
>
>       type Movie
>       @table(name: "Movies", singular: "movie", plural: "movies", key: ["id"]) {
>       id: UUID! @col(name: "movie_id") @default(expr: "uuidV4()")
>       title: String!
>       releaseYear: Int @col(name: "release_year")
>       genre: String
>       rating: Int @col(name: "rating")
>       description: String @col(name: "description")
>       tags: [String] @col(name: "tags")
>       }
>
>       type MovieMetadata
>       @table(
>         name: "MovieMetadata"
>       ) {
>       movie: Movie! @ref
>       director: String @col(name: "director")
>       }
>
>       extend type Movie {
>       movieMetadata: MovieMetadata
>       movieMetadatas_on_movie: MovieMetadata
>       }
>
>       type MovieActors @table(
>       key: ["movie", "actor"]
>       ) {
>       movie: Movie!
>       actor: Actor!
>       }
>
>       extend type MovieActors {
>       movieId: UUID!
>       actorId: UUID!
>       }
>
>       extend type Movie {
>       movieActors: [MovieActors!]!
>       actors: [Actor!]!
>       actors_via_MovieActors: [Actor!]!
>       }
>
>       extend type Actor {
>       movieActors: [MovieActors!]!
>       movies: [Movie!]!
>       movies_via_MovieActors: [Movie!]!
>       }
>     
> ```

## Use generated fields to build queries

Your SQL Connect operations will extend a set of fields
automatically generated by SQL Connect based on the types and type
relationships in your schema. These fields are generated by local tooling
whenever you edit your schema.

> [!TIP]
> **Tip:** To discover the generated fields while building operations, use the query editor in the Firebase console, or our SQL Connect VS Code extension

You can use generated fields to implement increasingly complex queries, from retrieving individual records or multiple records from single tables to multiple records from related tables.

<br />

Assume your schema contains a `Movie` type and an associated `Actor` type.
SQL Connect generates `movie`, `movies`,
`actors_on_movies` fields, and more.

> [!NOTE]
> **Note:** These query fields let you pass a *key scalar* type, here a `Movie_Key`, to identify records. These are created based on your [schema](https://firebase.google.com/docs/sql-connect/queries-guide.md.txt#key-scalars).

### Query with the
`movie` field

|---|---|
| The `movie` field represents a single record in the `Movie` table. | Use this field to query a single movie by its key. ```graphql query GetMovie($myKey: Movie_Key!) { movie(key: $myKey) { title } } ``` |

### Query with the
`movies` field

|---|---|
| The `movies` field represents a list of records in the `Movie` table. | Use this field to query multiple movies, for example, all movies with a given year. ```graphql query GetMovies($myYear: Int!) { movies(where: { year: { eq: $myYear } }) { title } } ``` |

### Query with the
`actors_on_movies` field

|---|---|
| The `actors_on_movies` field represents a list of records that connect `Actor` and `Movie` tables. Use this field to query all actors associated with a given movie. | Use this field to query all actors associated with a given movie. ```graphql query GetActorsOnMovie($myKey: Movie_Key!) { actors_on_movies(where: { movie: { key: { eq: $myKey } } }) { actor { name } } } ``` |

> [!TIP]
> **Tip:** The schema definition language also lets you explicitly control how names are generated for fields using [`singular` and `plural` arguments for the `@table` directive](https://firebase.google.com/docs/reference/data-connect/gql/directive#customizations_2).

## Essential elements of a query

SQL Connect queries are GraphQL queries with SQL Connect extensions. Just
as with a regular GraphQL query, you can define an operation name and a list
of GraphQL variables.

SQL Connect extends GraphQL queries with customized **directives** like
`@auth`.

> [!TIP]
> **Tip:** You can use Gemini in Firebase to help you create and test GraphQL queries in the Firebase console. Learn more at [Use AI assistance for
> queries and mutations](https://firebase.google.com/docs/sql-connect/ai-assistance).

So the following query has:

- A `query` type definition
- A `ListMoviesByGenre` operation (query) name
- A single query argument, here a `$genre` variable of `String` type
- A single directive, `@auth`.
- A single field, `movies`.

```graphql
query ListMoviesByGenre($genre: String!) @auth(level: PUBLIC) {
  movies(where: { genre: { eq: $genre } }) {
    id
    title
  }
}
```

Every query argument requires a type declaration, a built-in like `String`, or a
custom, schema-defined type like `Movie`.

This guide will look at the signature of increasingly complex queries. You'll
end by introducing powerful, concise relationship expressions you can use to
build your deployable queries.

### Key scalars in queries

But first, a note about key scalars.

SQL Connect defines a special *key scalar* to represent primary
keys of each table, identified by {TableType}_Key. It is a JSON object of
primary key values.

You retrieve key scalars as a response returned by most auto-generated read
fields, or of course from queries where you have retrieved all the fields
needed to build the scalar key.

Singular automatic queries, like `movie` in our running example, support a key
argument that accepts a key scalar.

You might pass a key scalar as a literal. But, you can define variables to pass
key scalars as input.

### Query

```graphql
query GetMovie($myKey: Movie_Key!) {
  movie(key: $myKey) { title }
}
    
```

### Response

```json
{
  "data": {
    "movie": {
      "title": "Example Movie Title"
    }
  }
}
    
```

These can be provided in request JSON like this (or other serialization
formats):

```json
{
  # ...
  "variables": {
    "myKey": {"id": "xxxxxxxx-xxxx-4xxx-yxxx-xxxxxxxxxxxx"}
  }
}
```

Thanks to custom scalar parsing, a `Movie_Key` can also be constructed using the
object syntax, which may contain variables. This is mostly useful when you want
to break individual components into different variables for some reason.

## Write basic queries

You can start writing queries to get individual records from your database, or
list records from a table with the option to limit and order results.

> [!TIP]
> **Tip:** You can use Gemini in Firebase to help you create and run GraphQL queries in the Firebase console. Learn more at [Use AI assistance for
> queries and mutations](https://firebase.google.com/docs/sql-connect/ai-assistance).

### Retrieve individual records

The simplest query gets a single record by ID. Your query will use the
auto-generated `movie` field.

### Query

```graphql
query GetMovieById($id: UUID!) @auth(level: PUBLIC) {
  movie(id: $id) {
    id
    title
    imageUrl
    genre
  }
}
    
```

### Response

```json
{
  "data": {
    "movie": {
      "id": "some-uuid",
      "title": "Example Movie Title",
      "imageUrl": "https://example.com/movie.jpg",
      "genre": "Action"
    }
  }
}
    
```

### Retrieve all records in a table

To retrieve a subset of fields for the full list of movies from the `Movies`
table, your query will make use of the auto-generated `movies` field, and your
implementation might look like the following.

### Query

```graphql
query ListMovies @auth(level: PUBLIC) {
  movies {
    id
    title
    imageUrl
    genre
  }
}
    
```

### Response

```json
{
  "data": {
    "movies": [
      {
        "id": "some-uuid",
        "title": "Example Movie Title",
        "imageUrl": "https://example.com/movie.jpg",
        "genre": "Action"
      },
      {
        "id": "another-uuid",
        "title": "Another Movie Title",
        "imageUrl": "https://example.com/another-movie.jpg",
        "genre": "Comedy"
      }
    ]
  }
}
    
```

### Use `orderBy`, `limit` and `offset` operators

Naturally, listing all records from a table has limited usefulness.

You can order and perform pagination on results. These arguments are accepted
but not returned in results.

Here, the query gets the titles of the top 10 movies by rating.

### Query

```graphql
query MoviesTop10 {
  movies(orderBy: [{ rating: DESC }], limit: 10) {
    # graphql: list the fields from the results to return
    title
  }
}
    
```

### Response

```json
{
  "data": {
    "movies": [
      { "title": "Top Movie 1" },
      { "title": "Top Movie 2" },
      { "title": "Top Movie 3" }
      // ... other 7 movies
    ]
  }
}
    
```

You might have a use case for fetching rows from an offset, like movies 11-20
ordered by rating.

### Query

```graphql
query Movies11to20 {
  movies(orderBy: [{ rating: DESC }], limit: 10, offset: 10) {
    # graphql: list the fields from the results to return
    title
  }
}
    
```

### Response

```json
{
  "data": {
    "movies": [
      { "title": "Movie 11" },
      { "title": "Movie 12" },
      { "title": "Movie 13" }
      // ... other 7 movies
    ]
  }
}
    
```

### Use aliases in queries

SQL Connect supports GraphQL aliasing in queries. With aliases, you
rename the data that is returned in a query's results. A single
SQL Connect query can apply multiple filters or other query
operations in one efficient request to the server, effectively issuing several
"sub-queries" at once. To avoid name collisions in the returned dataset, you
use aliases to distinguish the sub-queries.

Here is a query where an expression uses the aliases `mostPopular` and
`leastPopular`.

### Query

```graphql
query ReviewPopularitySpread($genre: String) {
  mostPopular: review(
    first: {
      where: {genre: {eq: $genre}},
      orderBy: {popularity: DESC}
    }
  ),
  leastPopular: review(
    last: {
      where: {genre: {eq: $genre}},
      orderBy: {popularity: DESC}
    }
  )
}
    
```

### Response

```json
{
  "data": {
    "mostPopular": [
      { "popularity": 9 }
    ],
    "leastPopular": [
      { "popularity": 1 }
    ]
  }
}
    
```

## Use query filters

SQL Connect queries map to all common SQL filters and order
operations.

### Filter with `where` with `orderBy` operators

Returns all matched rows from the table (and nested associations). Returns an
empty array if no records match the filter.

### Query

```graphql
query MovieByTopRating($genre: String) {
  mostPopular: movies(
    where: { genre: { eq: $genre } },
    orderBy: { rating: DESC }
  ) {
    # graphql: list the fields from the results to return
    id
    title
    genre
    description
  }
}
    
```

### Response

```json
{
  "data": {
    "mostPopular": [
      {
        "id": "some-uuid",
        "title": "Example Movie Title",
        "genre": "Action",
        "description": "A great movie"
      }
    ]
  }
}
    
```

### Filter by testing for null values

You can test for `null` values using the `isNull` operator.

### Query

```graphql
query ListMoviesWithoutDescription {
  movies(where: { description: { isNull: true }}) {
    id
    title
  }
}
    
```

### Response

```json
{
  "data": {
    "movies": [
      {
        "id": "some-uuid",
        "title": "Example Movie Title"
      },
      {
        "id": "another-uuid",
        "title": "Another Movie Title"
      }
    ]
  }
}
    
```

For more operators, see the [input objects types reference guide](https://firebase.google.com/docs/reference/data-connect/gql/input_object).

### Filter with value comparisons

You can use operators like `lt` (less than) and `ge` (greater than or equal)
to compare values in your queries.

### Query

```graphql
query ListMoviesByRating($minRating: Int!, $maxRating: Int!) {
  movies(where: { rating: { ge: $minRating, lt: $maxRating }}) {
    id
    title
  }
}
    
```

### Response

```json
{
  "data": {
    "movies": [
      {
        "id": "some-uuid",
        "title": "Example Movie Title"
      },
      {
        "id": "another-uuid",
        "title": "Another Movie Title"
      }
    ]
  }
}
    
```

### Filter with `includes` and `excludes` operators for array fields

You can test that an array field includes a specified item.

The following example illustrates the `includes` operator.

SQL Connect supports `includesAll`, `excludes`, `excludesAll` and
more. Review all such operators for integers, strings, dates and other data
types in the [`_ListFilter` headings of the reference documentation](https://firebase.google.com/docs/reference/data-connect/gql/input_object).

### Query

```graphql
query ListMoviesByTag($tag: String!) {
  movies(where: { tags: { includes: $tag }}) {
    # graphql: list the fields from the results to return
    id
    title
  }
}
    
```

### Response

```json
{
  "data": {
    "movies": [
      {
        "id": "some-uuid",
        "title": "Example Movie Title"
      }
    ]
  }
}
    
```

### Filter with string operations and regular expressions

Your queries can use typical string search and comparison operations, including
regular expressions. Note for efficiency you are bundling several operations
here and disambiguating them with aliases.

```graphql
query MoviesTitleSearch($prefix: String, $suffix: String, $contained: String, $regex: String) {
  prefixed: movies(where: {title: {startsWith: $prefix}}) {...}
  suffixed: movies(where: {title: {endsWith: $suffix}}) {...}
  contained: movies(where: {title: {contains: $contained}}) {...}
}
```

### Filter with `_or`, `_and`, `_not` operator logic

Use `_or` for more complex logic. SQL Connect also supports `_and`
and `_not` operators.

### Query

```graphql
query ListMoviesByGenreAndGenre($minRating: Int!, $genre: String) {
  movies(
    where: { _or: [{ rating: { ge: $minRating } }, { genre: { eq: $genre } }] }
  ) {
    # graphql: list the fields from the results to return
    title
  }
}
    
```

### Response

```json
{
  "data": {
    "movies": [
      { "title": "Movie Title 1" },
      { "title": "Movie Title 2" }
    ]
  }
}
    
```

> [!TIP]
> **Tip:** The complete set of GraphQL language extensions for SQL Connect is [documented in the language reference guide](https://firebase.google.com/docs/reference/data-connect).

## Write relational queries

SQL Connect queries can access data based on the relationships among
tables. You can use the object (one-to-one) or array (one-to-many) relationships
defined in your schema to make nested queries, that is, fetch data for one type
along with data from a nested or related type.

Such queries use magic SQL Connect `_on_` and `_via` syntax in
generated read fields.

Remember to review the [sample schema](https://firebase.google.com/docs/sql-connect/queries-guide.md.txt#movie-schema-intro).

### Many to one

Now look at a query to illustrate `_on_` syntax.

### Query

```graphql
query MyReviews @auth(level: USER) {
  user(key: {id_expr: "auth.uid"}) {
    reviews: reviews_on_user {
      movie { name }
      rating
    }
  }
}
    
```

### Response

```json
{
  "data": {
    "user": {
      "reviews": [
        {
          "movie": { "name": "Movie Title" },
          "rating": 5
        }
      ]
    }
  }
}
    
```

### One to one

You can write a one-to-one query using `_on_` syntax.

### Query

```graphql
query GetMovieMetadata($id: UUID!) @auth(level: PUBLIC) {
  movie(id: $id) {
    movieMetadatas_on_movie {
      director
    }
  }
}
    
```

### Response

```json
{
  "data": {
    "movie": {
      "movieMetadatas_on_movie": {
        "director": "Some Director"
      }
    }
  }
}
    
```

### Many to many

Many-to-many queries use `_via_` syntax. A many-to-many query might
retrieve actors for a specified movie.

### Query

```graphql
query MoviesActors($id: UUID!) @auth(level: USER) {
  movie(id: $id) {
     actors: actors_via_MovieActors {
        name
     }
  }
}
    
```

### Response

```json
{
  "data": {
    "movie": {
      "actors": [
        {
          "name": "Actor Name"
        }
      ]
    }
  }
}
    
```

But we can write a more complex query, using aliases, to filter based on `role`
to retrieve actors and associated movies in `mainActors` and
`supportingActors` results. Since this is many-to-many, `_via_` syntax is used.

### Query

```graphql
query GetMovieCast($movieId: UUID!, $actorId: UUID!) @auth(level: PUBLIC) {
  movie(id: $movieId) {
    mainActors: actors_via_MovieActor(where: { role: { eq: "main" } }) {
      name
    }
    supportingActors: actors_via_MovieActor(
      where: { role: { eq: "supporting" } }
    ) {
      name
    }
  }
  actor(id: $actorId) {
    mainRoles: movies_via_MovieActor(where: { role: { eq: "main" } }) {
      title
    }
    supportingRoles: movies_via_MovieActor(
      where: { role: { eq: "supporting" } }
    ) {
      title
    }
  }
}
    
```

### Response

```json
{
  "data": {
    "movie": {
      "mainActors": [
        {
          "name": "Main Actor Name"
        }
      ],
      "supportingActors": [
        {
          "name": "Supporting Actor Name"
        }
      ]
    },
    "actor": {
      "mainRoles": [
        {
          "title": "Main Role Movie Title"
        }
      ],
      "supportingRoles": [
        {
          "title": "Supporting Role Movie Title"
        }
      ]
    }
  }
}
    
```

> [!TIP]
> **Tip:** The complete set of GraphQL language extensions for SQL Connect is [documented in the language reference guide](https://firebase.google.com/docs/reference/data-connect).

## Aggregation queries

#### What are aggregates, and why use them?

Aggregate fields let you perform calculations on a list of results. With
aggregate fields, you can do things like:

- Find the average score of a review
- Find the total cost of items in a shopping cart
- Find the highest- or lowest-rated product
- Count the number of products in your store

Aggregates are performed on the server, which offers a number of benefits over
calculating them client side:

- Faster app performance (since you avoid client side calculations)
- Reduced data egress costs (since you send just the aggregated results instead of all of the inputs)
- Improved security (since you can give clients access to aggregated data instead of the entire data set)

> [!NOTE]
> **Note:** Refer to the SQL Connect GraphQL reference documentation for the complete list of aggregation fields.

#### Example schema for aggregates

In this section, we'll switch to a storefront example schema, which is a good
for explaining how to use aggregates:

```graphql
  type Product @table {
    name: String!
    manufacturer: String!
    quantityInStock: Int!
    price: Float!
    expirationDate: Date
  }
```

#### Simple aggregates

##### _count for all fields

The simplest aggregate field is `_count`: it returns how many rows match your
query. For each field in your type, SQL Connect
generates corresponding aggregate fields depending on the field type.

### Query

```graphql
query CountProducts {
  products {
    _count
  }
}
```

### Response
```
one
```

For example, if you have 5 products in your database, the result would be:

```json
{
  "products": [
    {
    "_count": 5
    }
  ]
}
```

All fields have a `<field>_count` field, which counts how many rows have a
non-null value in that field.

### Query

```graphql
query CountProductsWithExpirationDate {
  products {
    expirationDate_count
  }
}
```

### Response
```
field_count
```


For example, if you have 3 products with an expiration date, the result would
be:

```json
{
  "products": [
    {
    "expirationDate_count": 3
    }
  ]
}
```

##### _min, _max, _sum, and _avg for numeric fields

Numeric fields (int, float, int64) also have `<field>_min`, `<field>_max`,
`<field>_sum`, and `<field>_avg`.

### Query

```graphql
query NumericAggregates {
  products {
  quantityInStock_max
  price_min
  price_avg
  quantityInStock_sum
  }
}
```

### Response
```
_min _max _sum _avg
```


For example, if you have the following products:

- Product A: `quantityInStock: 10`, `price: 2.99`
- Product B: `quantityInStock: 5`, `price: 5.99`
- Product C: `quantityInStock: 20`, `price: 1.99`

The result would be:

```json
{
  "products": [
    {
    "quantityInStock_max": 20,
    "price_min": 1.99,
    "price_avg": 3.6566666666666666,
    "quantityInStock_sum": 35
    }
  ]
}
```

##### _min and _max for dates and timestamps

Date and timestamp fields have `<field>_min` and `<field>_max`.

### Query

```graphql
query DateAndTimeAggregates {
  products {
  expirationDate_max
  expirationDate_min
  }
}
```

### Response
```
_min _maxdatetime
```


For example, if you have the following expiration dates:

- Product A: `2024-01-01`
- Product B: `2024-03-01`
- Product C: `2024-02-01`

The result would be:

```json
{
  "products": [
    {
    "expirationDate_max": "2024-03-01",
    "expirationDate_min": "2024-01-01"
    }
  ]
}
```

#### Distinct

The `distinct` argument lets you get all unique values for a field
(or combination of fields). For example:

### Query

```graphql
query ListDistinctManufacturers {
  products(distinct: true) {
    manufacturer
  }
}
```

### Response
```
distinct
```


For example, if you have the following manufacturers:

- Product A: `manufacturer: "Acme"`
- Product B: `manufacturer: "Beta"`
- Product C: `manufacturer: "Acme"`

The result would be:

```json
{
  "products": [
    { "manufacturer": "Acme" },
    { "manufacturer": "Beta" }
  ]
}
```

You can also use the `distinct` argument on aggregate fields to instead
aggregate the distinct values. For example:

### Query

```graphql
query CountDistinctManufacturers {
  products {
    manufacturer_count(distinct: true)
  }
}
```

### Response
```
distinctonaggregate
```

For example, if you have the following manufacturers:

- Product A: `manufacturer: "Acme"`
- Product B: `manufacturer: "Beta"`
- Product C: `manufacturer: "Acme"`

The result would be:

```json
{
  "products": [
    {
    "manufacturer_count": 2
    }
  ]
}
```

#### Grouped aggregates

You perform a grouped aggregate by selecting a mix of aggregate and
non-aggregate fields on a type. This groups together all matching rows that have
the same value for the non-aggregate fields, and calculate the aggregate fields
for that group. For example:

### Query

```graphql
query MostExpensiveProductByManufacturer {
  products {
  manufacturer
  price_max
  }
}
```

### Response
```
groupedaggregates
```


For example, if you have the following products:

- Product A: `manufacturer: "Acme"`, `price: 2.99`
- Product B: `manufacturer: "Beta"`, `price: 5.99`
- Product C: `manufacturer: "Acme"`, `price: 1.99`

The result would be:

```json
{
  "products": [
    { "manufacturer": "Acme", "price_max": 2.99 },
    { "manufacturer": "Beta", "price_max": 5.99 }
  ]
}
```

##### `having` and `where` with grouped aggregates

You can also use the `having` and `where` argument to only return groups that
meet a provided criteria.

- `having` lets you filter groups by their aggregate fields
- `where` lets you filter the rows based on non-aggregate fields.

### Query

```graphql
query FilteredMostExpensiveProductByManufacturer {
  products(having: {price_max: {ge: 2.99}}) {
  manufacturer
  price_max
  }
}
```

### Response
```
havingwhere
```


For example, if you have the following products:

- Product A: `manufacturer: "Acme"`, `price: 2.99`
- Product B: `manufacturer: "Beta"`, `price: 5.99`
- Product C: `manufacturer: "Acme"`, `price: 1.99`

The result would be:

```json
{
  "products": [
    { "manufacturer": "Acme", "price_max": 2.99 },
    { "manufacturer": "Beta", "price_max": 5.99 }
  ]
}
```

#### Aggregates across tables

Aggregate fields can be used in concert with generated one-to-many relationship
fields to answer complex questions about your data. Here is a
modified schema, with separate table, `Manufacturer`, we can use in examples:

```graphql
  type Product @table {
    name: String!
    manufacturer: Manufacturer!
    quantityInStock: Int!
    price: Float!
    expirationDate: Date
  }

  type Manufacturer @table {
    name: String!
    headquartersCountry: String!
  }
```

Now we can use aggregate fields to do things like find how many products a
manufacturer makes:

### Query

```graphql
query GetProductCount($id: UUID) {
  manufacturers {
    name
    products_on_manufacturer {
      _count
    }
  }
}
```

### Response
```
aggregatesacrosstables
```


For example, if you have the following manufacturers:

- Manufacturer A: `name: "Acme"`, `products_on_manufacturer: 2`
- Manufacturer B: `name: "Beta"`, `products_on_manufacturer: 1`

The result would be:

```json
{
  "manufacturers": [
    { "name": "Acme", "products_on_manufacturer": { "_count": 2 } },
    { "name": "Beta", "products_on_manufacturer": { "_count": 1 } }
  ]
}
```

## Query enumeration fields

You can query [enumeration fields in your schema](https://firebase.google.com/docs/sql-connect/schemas-guide.md.txt#enumeration-fields).

Assume our enumeration is implemented as:

```graphql
enum AspectRatio {
   ACADEMY
   WIDESCREEN
   ANAMORPHIC
   IMAX
   "No information available on aspect ratio"
   UNAVAILABLE
}
```

For example, you can list all movies formatted in your preferred aspect ratio.

```graphql
query ListMoviesByAspectRatio($aspectRatio: AspectRatio!) @auth(level: PUBLIC) {
  movies(where:
    {originalAspectRatio: {eq: $aspectRatio}}
) {
    id
    title
    imageUrl
    releaseYear
    genre
    rating
    tags
    description
    otherAspectRatios
  }
}
```

### Filter enumeration fields

Enums have the standard filtering actions available: `eq`, `ne`, `ge`, `gt`,
`lt`, `le`, `in` `nin`, as well as `includes`, `excludes`, `includesAll` and
`excludesAll` for lists.

In `order`, the greater and less than operators use the order of the enum
values for meaningful execution. Values at the beginning of the enum
are smaller than values at the end of the enum definition.

For example, the following `where` clause would return all movies with
`ratio` less than the input aspect ratio.

- In the first example, if `$aspectRatio` were IMAX, then movies with `ratio` matching ACADEMY, WIDESCREEN, and ANAMORPHIC would be selected.
- In the second example, only movies available in both ACADEMY and WIDESCREEN would be returned.

```graphql
  movies(where: {originalAspectRatio: {lt: $aspectRatio}})

  movies(where: {otherAspectRatios: {includesAll: [ACADEMY, WIDESCREEN]}})
```

### Perform aggregation queries on enumeration fields

Only `_count` is supported for enums.

The following query counts the total number of movies of each aspect ratio.

```graphql
query MovieCountByOriginalAspectRatio @auth(level: PUBLIC) {
  movies {
    _count
    originalAspectRatio
  }
}
```

### Call enumeration queries from client code

Learn how to use enumeration queries from clients, and how to design client
calls to maximize your flexibility in managing enumerations, in the SDK guides
for [iOS](https://firebase.google.com/docs/sql-connect/ios-sdk.md.txt#enum-queries),
[Android](https://firebase.google.com/docs/sql-connect/android-sdk.md.txt#enum-queries),
[web](https://firebase.google.com/docs/sql-connect/web-sdk.md.txt#enum-queries)
and [Flutter](https://firebase.google.com/docs/sql-connect/flutter-sdk.md.txt#enum-queries).

## Write advanced queries: use `query` fields to read data in multi-step operations

There are many situations in which you might want to read your database
during execution of a mutation to lookup and verify existing data before
performing, for example, inserts or updates. These options save round trip
operations and hence costs.

SQL Connect supports this functionality.
See [multi-step operations](https://firebase.google.com/docs/sql-connect/mutations-guide.md.txt#multi-step-operations).

## Next steps

> [!NOTE]
>
> **Note:** Follow the complete series on building SQL Connect
> schemas and connectors, which covers:
>
> - [Schema development](https://firebase.google.com/docs/sql-connect/schemas-guide.md.txt)
> - Query development (this guide)
> - [Mutation development](https://firebase.google.com/docs/sql-connect/mutations-guide.md.txt)

You may be interested in:

- Generating queries for your apps using [AI assistance tools](https://firebase.google.com/docs/sql-connect/ai-assistance.md.txt)
- Authorizing your queries per the [authorization guide](https://firebase.google.com/docs/sql-connect/authorization-and-security.md.txt)
- Calling queries from your client code for [web](https://firebase.google.com/docs/sql-connect/web-sdk.md.txt), [iOS](https://firebase.google.com/docs/sql-connect/ios-sdk.md.txt), [Android](https://firebase.google.com/docs/sql-connect/android-sdk.md.txt) and [Flutter](https://firebase.google.com/docs/sql-connect/flutter-sdk.md.txt).