Ent Queries Predicates Eager Loading and Aggregation

An Ent query is built from generated pieces: a query object per entity, a predicate function per field and operator, and methods for ordering, limiting, loading edges and aggregating. Because it is all Go, you can compose queries from functions, and the compiler checks every name. What it does not tell you is the SQL, and that is where surprises live. This article runs nine query shapes on PostgreSQL 18.6, prints the statements Ent sent for each, and reads them, including one result that looks like a bug and is not.

It builds on the Ent tutorial and the schema article. The N plus one problem it avoids with eager loading is the one measured for GORM in Preload and Joins. For the same ideas in plain SQL, see the sqlc tutorial.

Seeing the SQL

Ent can log every statement. The client option ent.Debug() wraps the driver in a logger, and ent.Log(fn) sets the function that receives the lines. The lab captures them into a slice and prints them under each query, so the output is stable and the SQL is next to the code that produced it. The helper is small:

package main

import (
	"context"
	"fmt"
	"strings"

	"entgo.io/ent/dialect/sql"
	"example.test/lab/ent"
	"example.test/lab/ent/author"
	"example.test/lab/ent/book"
	"example.test/lab/ent/predicate"
)

func capture() (ent.Option, ent.Option, *[]string) {
	var seen []string
	log := ent.Log(func(args ...any) {
		s := strings.TrimSpace(fmt.Sprint(args...))
		s = strings.TrimPrefix(s, "driver.")
		seen = append(seen, s)
	})
	return log, ent.Debug(), &seen
}

func main() {
	ctx := context.Background()
	logOpt, debugOpt, seen := capture()
	client := open("l0739", logOpt, debugOpt)
	defer client.Close()
	must(client.Schema.Create(ctx))
	*seen = nil

	for i, name := range []string{"Ada", "Bob", "Cy"} {
		a := client.Author.Create().SetName(name).SetEmail(strings.ToLower(name) + "@example.com").SaveX(ctx)
		for j := 1; j <= i+1; j++ {
			client.Book.Create().SetTitle(fmt.Sprintf("%s book %d", name, j)).SetPages(100*j + 10*i).SetPublished(j%2 == 1).SetAuthor(a).SaveX(ctx)
		}
	}
	show := func(label string) {
		fmt.Printf("%s\n", label)
		for _, s := range *seen {
			fmt.Println("   ", s)
		}
		*seen = nil
	}
	*seen = nil

	// 1. Predicates compose with And, Or and Not.
	n := client.Book.Query().Where(book.Or(book.PagesGT(250), book.And(book.Published(true), book.Not(book.PagesLT(120))))).CountX(ctx)
	show("1. Or(PagesGT(250), And(Published, Not(PagesLT(120)))) -> " + fmt.Sprint(n) + " rows")

	// 2. A predicate over an edge becomes an EXISTS subquery.
	names := client.Author.Query().Where(author.HasBooksWith(book.PagesGT(300))).Order(ent.Asc(author.FieldName)).Select(author.FieldName).StringsX(ctx)
	show("2. HasBooksWith(PagesGT(300)) -> " + fmt.Sprint(names))

	// 3. Eager loading is one extra query for the whole set, not one per author.
	as := client.Author.Query().WithBooks(func(q *ent.BookQuery) { q.Order(ent.Desc(book.FieldPages)).Limit(1) }).Order(ent.Asc(author.FieldName)).AllX(ctx)
	for _, a := range as {
		fmt.Println("   ", a.Name, "has", len(a.Edges.Books), "book loaded")
	}
	show("3. WithBooks with a limit of 1 per query")

	// 4. Aggregates and group by.
	var rows []struct {
		AuthorBooks int `json:"author_books"`
		Count       int `json:"count"`
		Sum         int `json:"sum"`
	}
	client.Book.Query().GroupBy(book.AuthorColumn).Aggregate(ent.Count(), ent.Sum(book.FieldPages)).ScanX(ctx, &rows)
	show(fmt.Sprintf("4. GroupBy author -> %v", rows))
	var totals []struct {
		Sum int `json:"sum"`
		Max int `json:"max"`
	}
	client.Book.Query().Aggregate(ent.Sum(book.FieldPages), ent.Max(book.FieldPages)).ScanX(ctx, &totals)
	show("   Sum and Max of pages -> " + fmt.Sprint(totals))

	// 5. Select only some columns, and read a single value.
	titles := client.Book.Query().Where(book.Published(true)).Order(ent.Asc(book.FieldTitle)).Limit(2).Select(book.FieldTitle).StringsX(ctx)
	show("5. Select(title) Limit(2) -> " + fmt.Sprint(titles))

	// 6. Only is strict about "exactly one".
	_, err := client.Book.Query().Where(book.PagesGT(50)).Only(ctx)
	fmt.Println("6. Only with many matches:", ent.IsNotSingular(err))
	_, err = client.Book.Query().Where(book.PagesGT(5000)).Only(ctx)
	fmt.Println("   Only with none:", ent.IsNotFound(err))
	fmt.Println("   First with none is also NotFound:", ent.IsNotFound(func() error { _, e := client.Book.Query().Where(book.PagesGT(5000)).First(ctx); return e }()))
	*seen = nil

	// 7. Exist and IDs avoid loading rows.
	fmt.Println("7. Exist:", client.Book.Query().Where(book.TitleContains("Cy")).ExistX(ctx), "IDs:", client.Book.Query().Where(book.PagesGT(250)).Order(ent.Asc(book.FieldID)).IDsX(ctx))
	*seen = nil

	// 8. A custom predicate with the SQL builder, for anything the generated ones lack.
	long := predicate.Book(func(s *sql.Selector) {
		s.Where(sql.P(func(b *sql.Builder) { b.Ident(book.FieldTitle).WriteString(" ~* ").Arg("book 2$") }))
	})
	n = client.Book.Query().Where(long).CountX(ctx)
	show("8. custom regex predicate -> " + fmt.Sprint(n) + " rows")

	// 9. Offset paging.
	page := client.Book.Query().Order(ent.Asc(book.FieldID)).Offset(2).Limit(2).IDsX(ctx)
	show("9. Offset(2).Limit(2) -> " + fmt.Sprint(page))
}

package main

import (
	"database/sql"
	"log"

	"entgo.io/ent/dialect"
	entsql "entgo.io/ent/dialect/sql"
	"example.test/lab/ent"
	_ "github.com/jackc/pgx/v5/stdlib"
)

const base = "postgres://postgres:[email protected]:55432/lab?sslmode=disable"

// open recreates a schema and returns a client whose connections use it.
func open(schema string, opts ...ent.Option) *ent.Client {
	admin, err := sql.Open("pgx", base)
	if err != nil {
		log.Fatal(err)
	}
	admin.Exec("drop schema if exists " + schema + " cascade")
	admin.Exec("create schema " + schema)
	admin.Close()
	db, err := sql.Open("pgx", base+"&search_path="+schema)
	if err != nil {
		log.Fatal(err)
	}
	drv := entsql.OpenDB(dialect.Postgres, db)
	return ent.NewClient(append([]ent.Option{ent.Driver(drv)}, opts...)...)
}

func must(err error) {
	if err != nil {
		log.SetFlags(0)
		log.Fatal(err)
	}
}

package ent

//go:generate go run -mod=mod entgo.io/ent/cmd/ent generate  ./schema

package schema

import (
	"time"

	"entgo.io/ent"
	"entgo.io/ent/schema/edge"
	"entgo.io/ent/schema/field"
)

type Author struct{ ent.Schema }

func (Author) Fields() []ent.Field {
	return []ent.Field{
		field.String("name").NotEmpty(),
		field.String("email").Unique(),
		field.Time("created_at").Default(time.Now).Immutable(),
	}
}

func (Author) Edges() []ent.Edge { return []ent.Edge{edge.To("books", Book.Type)} }

package schema

import (
	"entgo.io/ent"
	"entgo.io/ent/schema/edge"
	"entgo.io/ent/schema/field"
	"entgo.io/ent/schema/index"
)

type Book struct{ ent.Schema }

func (Book) Fields() []ent.Field {
	return []ent.Field{
		field.String("title").NotEmpty(),
		field.Int("pages").Positive(),
		field.Bool("published").Default(false),
	}
}

func (Book) Edges() []ent.Edge {
	return []ent.Edge{edge.From("author", Author.Type).Ref("books").Unique().Required()}
}

func (Book) Indexes() []ent.Index { return []ent.Index{index.Fields("title").Edges("author").Unique()} }

set -e
GOFLAGS=-mod=mod go generate ./ent
go mod tidy > /dev/null 2>&1
go run .

1. Or(PagesGT(250), And(Published, Not(PagesLT(120)))) -> 2 rows
    Query: query=SELECT COUNT("books"."id") FROM "books" WHERE "books"."pages" > $1 OR ("books"."published" AND (NOT ("books"."pages" < $2))) args=[250 120]
2. HasBooksWith(PagesGT(300)) -> [Cy]
    Query: query=SELECT "authors"."name" FROM "authors" WHERE EXISTS (SELECT "books"."author_books" FROM "books" WHERE "authors"."id" = "books"."author_books" AND "books"."pages" > $1) ORDER BY "authors"."name" ASC args=[300]
    Ada has 0 book loaded
    Bob has 0 book loaded
    Cy has 1 book loaded
3. WithBooks with a limit of 1 per query
    Query: query=SELECT "authors"."id", "authors"."name", "authors"."email", "authors"."created_at" FROM "authors" ORDER BY "authors"."name" ASC args=[]
    Query: query=SELECT "books"."id", "books"."title", "books"."pages", "books"."published", "books"."author_books" FROM "books" WHERE "books"."author_books" IN ($1, $2, $3) ORDER BY "books"."pages" DESC LIMIT 1 args=[1 2 3]
4. GroupBy author -> [{3 3 660} {2 2 320} {1 1 100}]
    Query: query=SELECT "books"."author_books", COUNT(*), SUM("books"."pages") FROM "books" GROUP BY "books"."author_books" args=[]
   Sum and Max of pages -> [{1080 320}]
    Query: query=SELECT SUM("books"."pages"), MAX("books"."pages") FROM "books" args=[]
5. Select(title) Limit(2) -> [Ada book 1 Bob book 1]
    Query: query=SELECT "books"."title" FROM "books" WHERE "books"."published" ORDER BY "books"."title" ASC LIMIT 2 args=[]
6. Only with many matches: true
   Only with none: true
   First with none is also NotFound: true
7. Exist: true IDs: [6]
8. custom regex predicate -> 2 rows
    Query: query=SELECT COUNT("books"."id") FROM "books" WHERE "title" ~* $1 args=[book 2$]
9. Offset(2).Limit(2) -> [3 4]
    Query: query=SELECT "books"."id" FROM "books" ORDER BY "books"."id" ASC LIMIT 2 OFFSET 2 args=[]

The data is three authors with one, two and three books, so the numbers below are small enough to check by eye.

1. Predicates compose

book.Or(book.PagesGT(250), book.And(book.Published(true), book.Not(book.PagesLT(120)))) counted 2 rows, and the SQL was:

SELECT COUNT("books"."id") FROM "books" WHERE "books"."pages" > $1 OR ("books"."published" AND (NOT ("books"."pages" < $2))) args=[250 120]

Ent adds the parentheses and numbers the bind parameters. Multiple predicates in one Where(...) are ANDed, and Or, And and Not nest them. Because predicates are values (predicate.Book), you can build them in a loop or return them from a function, which is how optional filters are done:

var preds []predicate.Book
if onlyPublished {
	preds = append(preds, book.Published(true))
}
client.Book.Query().Where(preds...).All(ctx)

An empty slice adds no condition. That pattern replaces the string concatenation that dynamic SQL needs elsewhere, and it is safe because every value is bound.

2. Filtering through an edge

author.HasBooksWith(book.PagesGT(300)) returned [Cy], and the SQL is an EXISTS subquery:

SELECT "authors"."name" FROM "authors" WHERE EXISTS (SELECT "books"."author_books" FROM "books" WHERE "authors"."id" = "books"."author_books" AND "books"."pages" > $1) ORDER BY "authors"."name" ASC

This is the right SQL for "authors that have such a book". A join would repeat an author once for each matching book and need a DISTINCT. HasBooks() with no argument means "has any book", and HasBooksWith takes any predicates of the other entity, including its own edge filters, so it nests through the graph. For many to many edges it generates the join table subquery.

3. Eager loading, and a surprise

client.Author.Query().WithBooks(...) loads each author's books. Two statements ran:

• SELECT ... FROM "authors" ORDER BY "authors"."name" ASC
• SELECT ... FROM "books" WHERE "books"."author_books" IN ($1, $2, $3) ORDER BY "books"."pages" DESC LIMIT 1

That is the whole eager load: the parents, then all their children with an IN list of the parent ids. It is constant in the number of parents.

The lab passed a callback to WithBooks that applied Order(ent.Desc(book.FieldPages)).Limit(1), intending "the longest book of each author". The output:

Ada has 0 book loaded, Bob has 0 book loaded, Cy has 1 book loaded.

Only one author got a book. The LIMIT 1 is part of the single child statement, so it limits the total rows across all authors, not rows per author. The query returned the longest book in the table, which belongs to Cy. This is how SQL works and how most ORMs' preloads work (GORM has the same property, shown in its Preload article), and it surprises people who read the code as "per author".

Ways to get "the top N per parent": order and limit in Go after loading all children (fine for small sets), use a window function in a custom query (row_number() over (partition by author_books order by pages desc)), or run one query per parent when there are few. Do not trust a limit inside an eager load to mean per parent.

Filtering inside the callback is fine: Where(book.Published(true)) limits which children load without any of this trouble.

4. Group by and aggregates

client.Book.Query().GroupBy(book.AuthorColumn).Aggregate(ent.Count(), ent.Sum(book.FieldPages)).ScanX(ctx, &rows) scanned into a struct slice and printed [{3 3 660} {2 2 320} {1 1 100}]: for each author column value, the count and the sum of pages. The SQL:

SELECT "books"."author_books", COUNT(*), SUM("books"."pages") FROM "books" GROUP BY "books"."author_books"

The struct fields map to the selected columns by their JSON tag names (author_books, count, sum), which is a place where a typo is silent. Ent provides ent.Count(), ent.Sum(field), ent.Avg, ent.Min and ent.Max.

A whole table aggregate has no group. The lab's first attempt called Aggregate(Sum, Max) followed by IntsX, which panicked with sql/scan: columns do not match (2 > 1). The typed single column finishers (Ints, Strings, Float64s) read exactly one column. For two aggregates use Scan into a struct. The corrected call returned [{1080 320}] from SELECT SUM("books"."pages"), MAX("books"."pages") FROM "books": 1080 total pages and a maximum of 320.

5. Selecting columns

Select(book.FieldTitle) followed by Strings(ctx) loads only titles: SELECT "books"."title" FROM "books" WHERE "books"."published" ORDER BY "books"."title" ASC LIMIT 2. It returned [Ada book 1 Bob book 1]. Use it for list views and to avoid loading wide columns. Selecting several columns into a struct works with Select(...).Scan. Ent entities loaded with Select have the other fields at their zero values, as in any partial load.

6. Only, First and the contract

Three finishers that sound alike:

The lab: Only with 8 matches gave ent.IsNotSingular(err) true, Only with none gave IsNotFound, and First with none gave IsNotFound too. Use Only when exactly one row is a business rule (a lookup by a unique key), because it turns a violated assumption into an error instead of an arbitrary row. Use First when any row will do.

Exist() and IDs() avoid loading entities: Exist returned true, IDs returned [6] for the books over 250 pages (the third author's third book, 330 pages in the data). Prefer them when you need only the answer.

7. A custom predicate

When the generated predicates lack an operator, a predicate.Book is just a function over the SQL selector. The lab wrote a regular expression match:

SELECT COUNT("books"."id") FROM "books" WHERE "title" ~* $1 args=[book 2$]

and counted 2 books whose title ends in book 2. The value is bound. This is the escape hatch, and it keeps the rest of the query typed. For a whole statement in SQL, the sql/execquery feature adds raw QueryContext on the client.

8. Offset paging

Offset(2).Limit(2) returned ids [3 4] with LIMIT 2 OFFSET 2. Offset has the cost described in GORM pagination: the database reads and discards the skipped rows. For deep pages use a keyset predicate, book.IDGT(lastID) with Order(ent.Asc(book.FieldID)) and a limit.

More patterns, from the documentation

The lab covers the shapes above. A few more come up in real services, and I describe them from Ent's documentation without having run them here, so check each against your version.

Reusing a query. A query object is mutated by each builder call, so using one query for both a count and a page can leak state. Clone() returns an independent copy: build the filtered query once, Clone().Count(ctx) for the total, then Clone().Limit(...).Offset(...).All(ctx) for the page. This is the same lesson as Session in GORM's scopes (scopes), and the habit is the same: count on the filter, paginate on a copy.

Nested eager loading. The callback given to WithBooks receives the child query, so it can load that entity's own edges: WithBooks(func(q *ent.BookQuery) { q.WithTags() }). Each level is one more statement with an IN list, so the cost is the sum of the levels, not the product. Count the statements in a test to hold the line, since a few nested loads quietly make a list endpoint cost six or seven queries.

Ordering by a relation. Ordering authors by how many books they have, or by a field of a related row, uses the edge ordering options generated with the sql/... order features (author.ByBooksCount() and similar). They generate a subquery or join in the ORDER BY. The syntax depends on the feature set you generate with, which is another reason to pin the Ent version.

Interceptors. A query interceptor wraps every query on an entity, the read side counterpart of a mutation hook. Soft delete filters and tenant filters are usually written this way, so the condition is added in one place (Ent mixins and multi tenancy). The interceptor sees the query before it runs and can add a Where, which is why a forgotten filter in application code cannot leak. Like GORM callbacks, it does not cover raw SQL.

Raw SQL. With the sql/execquery feature, the client has ExecContext and QueryContext for statements the builder cannot express. Use them for reports with window functions and let Ent handle the CRUD around them.

Reading the cost of a query

The printed SQL is enough to estimate cost without running anything:

• A WHERE on a column with no index is a sequential scan. The schema creates indexes for unique fields and for the indexes you declare; foreign key columns get none by default on PostgreSQL, so the author_books column used in every join and IN above needs an index of its own on a large table.
• An EXISTS subquery with an indexed join column is cheap. Without the index it repeats a scan per author.
• Eager loading with IN ($1, $2, ...) grows with the number of parents. With a limit on the parent query that is bounded; without one, a million parents is a million parameters, which exceeds PostgreSQL's 65535 parameter limit (see the batch insert article for the same ceiling on writes).
• COUNT over a large table scans it. Estimate or cap it, as in the pagination article.

Run EXPLAIN on the statements that matter (the guide); the debug log gives you the exact text to paste.

Testing queries

A query test needs real data and a real database: seed a few rows, run the query and assert the rows and the number of statements. The statement count is the cheap guard against a regression to N plus one, and the log capture in this lab is all it takes. Assert the SQL text only for the few queries where the shape matters, because exact SQL assertions break on every Ent upgrade.

Compared with a GORM chain

The queries are equal in power and different in how much the compiler can see. The Ent versions fail at build time when a name is wrong; the GORM versions fail when the query runs.

Habits that keep Ent queries healthy

• Turn on SQL logging in development and read it for any new query shape.
• Count statements per request in a test, as with GORM; eager loading is two statements per relation.
• Use HasXWith for existence filters instead of loading and filtering in Go.
• Do not put a Limit inside WithX expecting a per parent limit.
• Pick Only or First on purpose.
• Use Scan into a struct for more than one aggregate.

Checklist

• Predicates are values; build lists of them for optional filters.
• Edge predicates become EXISTS subqueries.
• Eager loading costs one extra query per relation, with an IN list of parents.
• Group and aggregate with GroupBy and Aggregate, scanning into structs.
• Read the printed SQL before trusting a new query.

The query API is documented in the Ent predicates and eager loading pages.