ENT Query Comparison Guide

Complete mapping of SQL queries to ENT equivalents

📑 Table of Contents

Basic Queries - 10 queries
Filtering & Predicates - 13 queries
String Operations - 7 queries
Sorting & Pagination - 8 queries
Aggregation & Counting - 9 queries
Logical Operators - 7 queries
Relationships & Joins - 14 queries
Grouping & Having - 6 queries
CRUD Operations - 17 queries
Transactions - 4 queries
Advanced Queries - 11 queries
Edge Operations - 6 queries
Special Queries - 8 queries

Total: 120 SQL ↔ ENT Comparisons

1. Basic Queries

1.1 Select all users

SQL:


SELECT * FROM users;

ENT:


client.User.Query().All(ctx)

1.2 Select specific columns

SQL:


SELECT name, email FROM users;

ENT:


client.User.Query().
  Select(
    user.FieldName,
    user.FieldEmail
  ).All(ctx)

1.3 Get by primary key

SQL:


SELECT * FROM users WHERE id=10;

ENT:


client.User.Get(ctx, 10)

1.4 Get single row (error if 0 or 2+)

SQL:


SELECT * FROM users
WHERE email='a@b.com'
LIMIT 2;

ENT:


client.User.Query().
  Where(user.EmailEQ("a@b.com")).
  Only(ctx)

1.5 Get first row

SQL:


SELECT * FROM users
ORDER BY id ASC
LIMIT 1;

ENT:


client.User.Query().
  Order(ent.Asc(user.FieldID)).
  First(ctx)

1.6 Check existence

SQL:


SELECT EXISTS(
  SELECT 1 FROM users WHERE age>50
);

ENT:


client.User.Query().
  Where(user.AgeGT(50)).
  Exist(ctx)

1.7 Count all records

SQL:


SELECT COUNT(*) FROM users;

ENT:


client.User.Query().Count(ctx)

1.8 Count with condition

SQL:


SELECT COUNT(*) FROM users
WHERE age>25;

ENT:


client.User.Query().
  Where(user.AgeGT(25)).
  Count(ctx)

1.9 Get only IDs

SQL:


SELECT id FROM users WHERE age>30;

ENT:


client.User.Query().
  Where(user.AgeGT(30)).
  IDs(ctx)

1.10 Get single field values

SQL:


SELECT email FROM users;

ENT:


client.User.Query().
  Select(user.FieldEmail).
  Strings(ctx)

2. Filtering & Predicates

2.1 Where equals

SQL:


SELECT * FROM users
WHERE email='a@b.com';

ENT:


client.User.Query().
  Where(user.EmailEQ("a@b.com")).
  All(ctx)

2.2 Where not equals

SQL:


SELECT * FROM users
WHERE email!='spam@test.com';

ENT:


client.User.Query().
  Where(user.EmailNEQ("spam@test.com")).
  All(ctx)

2.3 Where IN

SQL:


SELECT * FROM users
WHERE id IN (1,2,3);

ENT:


client.User.Query().
  Where(user.IDIn(1,2,3)).
  All(ctx)

2.4 Where NOT IN

SQL:


SELECT * FROM users
WHERE id NOT IN (1,2,3);

ENT:


client.User.Query().
  Where(user.IDNotIn(1,2,3)).
  All(ctx)

2.5 Where IS NULL

SQL:


SELECT * FROM users
WHERE age IS NULL;

ENT:


client.User.Query().
  Where(user.AgeIsNil()).
  All(ctx)

2.6 Where IS NOT NULL

SQL:


SELECT * FROM users
WHERE age IS NOT NULL;

ENT:


client.User.Query().
  Where(user.AgeNotNil()).
  All(ctx)

2.7 Greater than

SQL:


SELECT * FROM users
WHERE age>25;

ENT:


client.User.Query().
  Where(user.AgeGT(25)).
  All(ctx)

2.8 Greater than or equal

SQL:


SELECT * FROM users
WHERE age>=18;

ENT:


client.User.Query().
  Where(user.AgeGTE(18)).
  All(ctx)

2.9 Less than

SQL:


SELECT * FROM users
WHERE age<30;

ENT:


client.User.Query().
  Where(user.AgeLT(30)).
  All(ctx)

2.10 Less than or equal

SQL:


SELECT * FROM users
WHERE age<=65;

ENT:


client.User.Query().
  Where(user.AgeLTE(65)).
  All(ctx)

2.11 Range (BETWEEN)

SQL:


SELECT * FROM users
WHERE age BETWEEN 18 AND 25;

ENT:


client.User.Query().
  Where(
    user.AgeGTE(18),
    user.AgeLTE(25)
  ).All(ctx)

2.12 Multiple conditions (implicit AND)

SQL:


SELECT * FROM users
WHERE age>18 AND name='John';

ENT:


client.User.Query().
  Where(
    user.AgeGT(18),
    user.NameEQ("John")
  ).All(ctx)

2.13 Combined IN with other filters

SQL:


SELECT * FROM users
WHERE id IN (1,2,3) AND age>25;

ENT:


client.User.Query().
  Where(
    user.IDIn(1,2,3),
    user.AgeGT(25)
  ).All(ctx)

3. String Operations

3.1 LIKE / Contains

SQL:


SELECT * FROM users
WHERE name ILIKE '%ton%';

ENT:


client.User.Query().
  Where(user.NameContains("ton")).
  All(ctx)

3.2 Case-insensitive contains

SQL:


SELECT * FROM users
WHERE LOWER(name) LIKE '%ton%';

ENT:


client.User.Query().
  Where(user.NameContainsFold("TON")).
  All(ctx)

3.3 Has prefix (starts with)

SQL:


SELECT * FROM users
WHERE name ILIKE 'To%';

ENT:


client.User.Query().
  Where(user.NameHasPrefix("To")).
  All(ctx)

3.4 Has suffix (ends with)

SQL:


SELECT * FROM users
WHERE email ILIKE '%@gmail.com';

ENT:


client.User.Query().
  Where(user.EmailHasSuffix("@gmail.com")).
  All(ctx)

3.5 Equals ignore case

SQL:


SELECT * FROM users
WHERE LOWER(email)='test@example.com';

ENT:


client.User.Query().
  Where(
    user.EmailEqualFold("TEST@example.com")
  ).All(ctx)

3.6 Case-sensitive prefix

SQL:


SELECT * FROM users
WHERE name LIKE 'John%';

ENT:


client.User.Query().
  Where(user.NameHasPrefix("John")).
  All(ctx)

3.7 NOT LIKE / Does not contain

SQL:


SELECT * FROM users
WHERE name NOT ILIKE '%test%';

ENT:


client.User.Query().
  Where(
    user.Not(user.NameContains("test"))
  ).All(ctx)

4. Sorting & Pagination

4.1 Order by ascending

SQL:


SELECT * FROM users
ORDER BY age ASC;

ENT:


client.User.Query().
  Order(ent.Asc(user.FieldAge)).
  All(ctx)

4.2 Order by descending

SQL:


SELECT * FROM users
ORDER BY id DESC;

ENT:


client.User.Query().
  Order(ent.Desc(user.FieldID)).
  All(ctx)

4.3 Multiple order by

SQL:


SELECT * FROM users
ORDER BY age DESC, name ASC;

ENT:


client.User.Query().
  Order(
    ent.Desc(user.FieldAge),
    ent.Asc(user.FieldName)
  ).All(ctx)

4.4 Limit

SQL:


SELECT * FROM users LIMIT 10;

ENT:


client.User.Query().Limit(10).All(ctx)

4.5 Offset

SQL:


SELECT * FROM users OFFSET 20;

ENT:


client.User.Query().Offset(20).All(ctx)

4.6 Limit + Offset (pagination)

SQL:


SELECT * FROM users
ORDER BY id ASC
LIMIT 10 OFFSET 20;

ENT:


client.User.Query().
  Order(ent.Asc(user.FieldID)).
  Limit(10).Offset(20).
  All(ctx)

4.7 Cursor-based pagination (next page)

SQL:


SELECT * FROM users
WHERE id<10
ORDER BY id DESC
LIMIT 5;

ENT:


client.User.Query().
  Where(user.IDLT(10)).
  Order(ent.Desc(user.FieldID)).
  Limit(5).All(ctx)

4.8 Get last N records

SQL:


SELECT * FROM users
ORDER BY id DESC
LIMIT 5;

ENT:


client.User.Query().
  Order(ent.Desc(user.FieldID)).
  Limit(5).All(ctx)

5. Aggregation & Counting

5.1 Count all

SQL:


SELECT COUNT(*) FROM users;

ENT:


client.User.Query().Count(ctx)

5.2 Count with condition

SQL:


SELECT COUNT(*) FROM users
WHERE age>25;

ENT:


client.User.Query().
  Where(user.AgeGT(25)).
  Count(ctx)

5.3 Sum

SQL:


SELECT SUM(age) FROM users;

ENT:


client.User.Query().
  Aggregate(ent.Sum(user.FieldAge)).
  Int(ctx)

5.4 Average

SQL:


SELECT AVG(age) FROM users
WHERE age IS NOT NULL;

ENT:


client.User.Query().
  Where(user.AgeNotNil()).
  Aggregate(ent.Avg(user.FieldAge)).
  Float64(ctx)

5.5 Min

SQL:


SELECT MIN(age) FROM users
WHERE age IS NOT NULL;

ENT:


client.User.Query().
  Where(user.AgeNotNil()).
  Aggregate(ent.Min(user.FieldAge)).
  Int(ctx)

5.6 Max

SQL:


SELECT MAX(age) FROM users
WHERE age IS NOT NULL;

ENT:


client.User.Query().
  Where(user.AgeNotNil()).
  Aggregate(ent.Max(user.FieldAge)).
  Int(ctx)

5.7 Multiple aggregates

SQL:


SELECT MIN(age), MAX(age)
FROM users
WHERE age IS NOT NULL;

ENT:


client.User.Query().
  Where(user.AgeNotNil()).
  Aggregate(
    ent.Min(user.FieldAge),
    ent.Max(user.FieldAge)
  ).Scan(ctx, &out)

5.8 Distinct values

SQL:


SELECT DISTINCT email FROM users;

ENT:


client.User.Query().
  Select(user.FieldEmail).
  Unique(true).
  Strings(ctx)

5.9 Count distinct

SQL:


SELECT COUNT(DISTINCT email)
FROM users;

ENT:


len(
  client.User.Query().
    Select(user.FieldEmail).
    Unique(true).
    StringsX(ctx)
)

6. Logical Operators

6.1 NOT

SQL:


SELECT * FROM users
WHERE NOT (age<18);

ENT:


client.User.Query().
  Where(user.Not(user.AgeLT(18))).
  All(ctx)

6.2 AND (implicit)

SQL:


SELECT * FROM users
WHERE age>18 AND age<30;

ENT:


client.User.Query().
  Where(
    user.AgeGT(18),
    user.AgeLT(30)
  ).All(ctx)

6.3 AND (explicit)

SQL:


SELECT * FROM users
WHERE (age>20 AND age<30);

ENT:


client.User.Query().
  Where(
    user.And(
      user.AgeGT(20),
      user.AgeLT(30)
    )
  ).All(ctx)

6.4 OR

SQL:


SELECT * FROM users
WHERE name ILIKE '%a%' OR age>30;

ENT:


client.User.Query().
  Where(
    user.Or(
      user.NameContains("a"),
      user.AgeGT(30)
    )
  ).All(ctx)

6.5 (A AND B) OR (C AND D)

SQL:


WHERE (age>25 AND email ILIKE '%@gmail%')
  OR (name ILIKE '%ton%' AND age IS NULL)

ENT:


client.User.Query().
  Where(
    user.Or(
      user.And(
        user.AgeGT(25),
        user.EmailContains("gmail")
      ),
      user.And(
        user.NameContains("ton"),
        user.AgeIsNil()
      )
    )
  ).All(ctx)

6.6 A AND (B OR C)

SQL:


WHERE age>18 AND
  (name ILIKE '%a%' OR email ILIKE '%@yahoo%')

ENT:


client.User.Query().
  Where(
    user.And(
      user.AgeGT(18),
      user.Or(
        user.NameContains("a"),
        user.EmailContains("yahoo")
      )
    )
  ).All(ctx)

6.7 NOT (A OR B)

SQL:


WHERE NOT
  (email ILIKE '%spam%' OR name ILIKE '%bot%')

ENT:


client.User.Query().
  Where(
    user.Not(
      user.Or(
        user.EmailContains("spam"),
        user.NameContains("bot")
      )
    )
  ).All(ctx)

7. Relationships & Joins

7.1 Load posts with author (eager load)

SQL:


SELECT p.*, u.* FROM posts p
JOIN users u ON u.id=p.author_id;

ENT:


client.Post.Query().WithAuthor().All(ctx)

7.2 Load user with posts (eager load)

SQL:


SELECT u.*, p.* FROM users u
LEFT JOIN posts p ON p.author_id=u.id;

ENT:


client.User.Query().WithPosts().All(ctx)

7.3 Load multiple edges

SQL:


SELECT u.*, p.*, c.* FROM users u
LEFT JOIN posts p ...
LEFT JOIN comments c ...

ENT:


client.User.Query().
  WithPosts().
  WithComments().
  All(ctx)

7.4 Posts by author email

SQL:


SELECT p.* FROM posts p
JOIN users u ON u.id=p.author_id
WHERE u.email='a@b.com';

ENT:


client.Post.Query().
  Where(
    post.HasAuthorWith(
      user.EmailEQ("a@b.com")
    )
  ).All(ctx)

7.5 Posts: published + author age>30

SQL:


SELECT p.* FROM posts p
JOIN users u ON ...
WHERE u.age>30 AND p.published=true;

ENT:


client.Post.Query().
  Where(
    post.PublishedEQ(true),
    post.HasAuthorWith(
      user.AgeGT(30)
    )
  ).All(ctx)

7.6 Users who have posts

SQL:


SELECT u.* FROM users u
WHERE EXISTS (
  SELECT 1 FROM posts p
  WHERE p.author_id=u.id
)

ENT:


client.User.Query().
  Where(user.HasPosts()).
  All(ctx)

7.7 Users who have NO posts

SQL:


SELECT u.* FROM users u
WHERE NOT EXISTS (
  SELECT 1 FROM posts p
  WHERE p.author_id=u.id
)

ENT:


client.User.Query().
  Where(user.Not(user.HasPosts())).
  All(ctx)

7.8 Users with published posts

SQL:


WHERE EXISTS(
  SELECT 1 FROM posts p
  WHERE p.author_id=u.id
    AND p.published=true
)

ENT:


client.User.Query().
  Where(
    user.HasPostsWith(
      post.PublishedEQ(true)
    )
  ).All(ctx)

7.9 Users with (published OR titled) posts

SQL:


WHERE EXISTS(
  SELECT 1 FROM posts p
  WHERE p.author_id=u.id AND
    (p.published=true OR
     p.title ILIKE '%Go%')
)

ENT:


client.User.Query().
  Where(
    user.HasPostsWith(
      post.Or(
        post.PublishedEQ(true),
        post.TitleContains("Go")
      )
    )
  ).All(ctx)

7.10 Posts where author email not like

SQL:


SELECT p.* FROM posts p
JOIN users u ON ...
WHERE u.email NOT ILIKE '%test%';

ENT:


client.Post.Query().
  Where(
    post.HasAuthorWith(
      user.Not(
        user.EmailContains("test")
      )
    )
  ).All(ctx)

7.11 Multi-level edge filter

SQL:


SELECT p.* FROM posts p
WHERE EXISTS (
  SELECT 1 FROM users u
  WHERE u.id=p.author_id AND
    EXISTS (
      SELECT 1 FROM posts p2
      WHERE p2.author_id=u.id
        AND p2.published=true
    )
)

ENT:


client.Post.Query().
  Where(
    post.HasAuthorWith(
      user.HasPostsWith(
        post.PublishedEQ(true)
      )
    )
  ).All(ctx)

7.12 Query user’s posts from entity

SQL:


SELECT p.* FROM posts p
WHERE p.author_id=?;

ENT:


userEntity.QueryPosts().All(ctx)

7.13 Query post’s author from entity

SQL:


SELECT u.* FROM users u
WHERE u.id=?;

ENT:


postEntity.QueryAuthor().Only(ctx)

7.14 User’s published posts only

SQL:


SELECT p.* FROM posts p
WHERE p.author_id=?
  AND p.published=true;

ENT:


userEntity.QueryPosts().
  Where(post.PublishedEQ(true)).
  All(ctx)

8. Grouping & Having

8.1 Group by single field

SQL:


SELECT author_id, COUNT(*)
FROM posts
GROUP BY author_id;

ENT:


client.Post.Query().
  GroupBy(post.FieldAuthorID).
  Aggregate(ent.Count()).
  Scan(ctx, &out)

8.2 Group by with HAVING

SQL:


SELECT author_id, COUNT(*)
FROM posts
GROUP BY author_id
HAVING COUNT(*)>=2;

ENT:


client.Post.Query().
  GroupBy(post.FieldAuthorID).
  Aggregate(ent.Count()).
  Having(ent.CountGTE(2)).
  Scan(ctx, &out)

8.3 Group with HAVING + ORDER

SQL:


SELECT author_id, COUNT(*)
FROM posts
GROUP BY author_id
HAVING COUNT(*)>=2
ORDER BY COUNT(*) DESC;

ENT:


client.Post.Query().
  GroupBy(post.FieldAuthorID).
  Aggregate(ent.Count()).
  Having(ent.CountGTE(2)).
  Order(ent.Desc("count")).
  Scan(ctx, &out)

8.4 Users with >=2 published posts

SQL:


SELECT author_id FROM posts
WHERE published=true
GROUP BY author_id
HAVING COUNT(*)>=2

ENT:


client.Post.Query().
  Where(post.PublishedEQ(true)).
  GroupBy(post.FieldAuthorID).
  Aggregate(ent.Count()).
  Having(ent.CountGTE(2)).
  Scan(ctx, &out)

8.5 Group by multiple fields

SQL:


SELECT author_id, published, COUNT(*)
FROM posts
GROUP BY author_id, published;

ENT:


client.Post.Query().
  GroupBy(
    post.FieldAuthorID,
    post.FieldPublished
  ).
  Aggregate(ent.Count()).
  Scan(ctx, &out)

8.6 Group with multiple aggregates

SQL:


SELECT author_id, COUNT(*), AVG(views)
FROM posts
GROUP BY author_id;

ENT:


client.Post.Query().
  GroupBy(post.FieldAuthorID).
  Aggregate(
    ent.Count(),
    ent.Avg(post.FieldViews)
  ).Scan(ctx, &out)

9. CRUD Operations

9.1 Insert single record

SQL:


INSERT INTO users(name, email, age)
VALUES ('John', 'j@e.com', 25);

ENT:


client.User.Create().
  SetName("John").
  SetEmail("j@e.com").
  SetAge(25).
  Save(ctx)

9.2 Insert with relation by ID

SQL:


INSERT INTO posts(title, content, author_id)
VALUES ('Title', 'Content', 1);

ENT:


client.Post.Create().
  SetTitle("Title").
  SetContent("Content").
  SetAuthorID(1).
  Save(ctx)

9.3 Insert with entity relation

SQL:


INSERT INTO posts(title, content, author_id)
VALUES ('Title', 'Content', ?);

ENT:


client.Post.Create().
  SetTitle("Title").
  SetAuthor(userEntity).
  Save(ctx)

9.4 Bulk insert

SQL:


INSERT INTO users(name, email)
VALUES ('A', 'a@e.com'), ('B', 'b@e.com');

ENT:


client.User.CreateBulk(
  client.User.Create().
    SetName("A").
    SetEmail("a@e.com"),
  client.User.Create().
    SetName("B").
    SetEmail("b@e.com")
).Save(ctx)

9.5 Insert with default values

SQL:


INSERT INTO posts(title, content)
VALUES ('Title', 'Content');

ENT:


client.Post.Create().
  SetTitle("Title").
  SetContent("Content").
  Save(ctx)

9.6 Upsert (ON CONFLICT DO UPDATE)

SQL:


INSERT INTO users(email, name)
VALUES ('a@b.com', 'John')
ON CONFLICT (email)
DO UPDATE SET name='John';

ENT:


client.User.Create().
  SetEmail("a@b.com").
  SetName("John").
  OnConflict().
  UpdateNewValues().
  ID(ctx)

9.7 Update by ID

SQL:


UPDATE users SET age=26 WHERE id=1;

ENT:


client.User.UpdateOneID(1).
  SetAge(26).Save(ctx)

9.8 Update with conditions

SQL:


UPDATE users SET name='X'
WHERE email='a@b.com';

ENT:


client.User.Update().
  Where(user.EmailEQ("a@b.com")).
  SetName("X").
  Exec(ctx)

9.9 Bulk update

SQL:


UPDATE posts SET published=true
WHERE author_id=1;

ENT:


client.Post.Update().
  Where(post.AuthorIDEQ(1)).
  SetPublished(true).
  Exec(ctx)

9.10 Update with increment

SQL:


UPDATE users SET age=age+1
WHERE id=1;

ENT:


client.User.UpdateOneID(1).
  AddAge(1).Save(ctx)

9.11 Update with decrement

SQL:


UPDATE users SET age=age-1
WHERE id=1;

ENT:


client.User.UpdateOneID(1).
  AddAge(-1).Save(ctx)

9.12 Clear optional field (set NULL)

SQL:


UPDATE users SET age=NULL WHERE id=1;

ENT:


client.User.UpdateOneID(1).
  ClearAge().Save(ctx)

9.13 Update and return updated record

SQL:


UPDATE users SET age=26
WHERE id=1 RETURNING *;

ENT:


client.User.UpdateOneID(1).
  SetAge(26).Save(ctx)

9.14 Delete by ID

SQL:


DELETE FROM users WHERE id=1;

ENT:


client.User.DeleteOneID(1).Exec(ctx)

9.15 Delete with conditions

SQL:


DELETE FROM posts
WHERE published=false;

ENT:


client.Post.Delete().
  Where(post.PublishedEQ(false)).
  Exec(ctx)

9.16 Delete all

SQL:


DELETE FROM posts;

ENT:


client.Post.Delete().Exec(ctx)

9.17 Conditional delete with relations

SQL:


DELETE FROM posts
WHERE author_id IN (
  SELECT id FROM users WHERE age<18
);

ENT:


client.Post.Delete().
  Where(
    post.HasAuthorWith(
      user.AgeLT(18)
    )
  ).Exec(ctx)

10. Transactions

10.1 Basic transaction

SQL:


BEGIN;
INSERT INTO users...;
INSERT INTO posts...;
COMMIT;

ENT:


tx, _ := client.Tx(ctx)
tx.User.Create()...Save(ctx)
tx.Post.Create()...Save(ctx)
tx.Commit()

10.2 Transaction with rollback

SQL:


BEGIN;
INSERT INTO users...;
ROLLBACK;

ENT:


tx, _ := client.Tx(ctx)
defer tx.Rollback()
tx.User.Create()...Save(ctx)
return tx.Commit()

10.3 WithTx helper pattern

SQL:


BEGIN; ... ; COMMIT;

ENT:


client.WithTx(ctx, func(tx *ent.Tx) error {
    return tx.User.Create()...Save(ctx)
})

10.4 Transaction with multiple operations

SQL:


BEGIN;
UPDATE users...;
DELETE FROM posts...;
INSERT INTO logs...;
COMMIT;

ENT:


tx, _ := client.Tx(ctx)
tx.User.Update()...Exec(ctx)
tx.Post.Delete()...Exec(ctx)
tx.Log.Create()...Save(ctx)
tx.Commit()

11. Advanced Queries

11.1 Users with age above average (2-step)

SQL:


SELECT * FROM users
WHERE age > (
  SELECT AVG(age) FROM users
  WHERE age IS NOT NULL
)

ENT:


avg := client.User.Query().
  Where(user.AgeNotNil()).
  Aggregate(ent.Avg(user.FieldAge)).
  Float64X(ctx)
client.User.Query().
  Where(user.AgeGT(int(avg))).
  All(ctx)

11.2 Users in grouped result (2-step)

SQL:


SELECT * FROM users
WHERE id IN (
  SELECT author_id FROM posts
  GROUP BY author_id
  HAVING COUNT(*)>=2
)

ENT:


var result []struct{ AuthorID int }
client.Post.Query().
  GroupBy(post.FieldAuthorID).
  Aggregate(ent.Count()).
  Having(ent.CountGTE(2)).
  Scan(ctx, &result)
ids := []int{}
for _, r := range result {
  ids = append(ids, r.AuthorID)
}
client.User.Query().
  Where(user.IDIn(ids...)).
  All(ctx)

11.3 Search across multiple fields

SQL:


WHERE (name ILIKE '%q%'
  OR email ILIKE '%q%')
ORDER BY created_at DESC
LIMIT 10

ENT:


client.User.Query().
  Where(
    user.Or(
      user.NameContains(q),
      user.EmailContains(q)
    )
  ).
  Order(ent.Desc(user.FieldCreatedAt)).
  Limit(10).
  All(ctx)

11.4 Dynamic filter builder

SQL:


Multiple WHERE conditions
applied conditionally

ENT:


q := client.User.Query()
if nameFilter != "" {
  q = q.Where(
    user.NameContains(nameFilter)
  )
}
if ageMin > 0 {
  q = q.Where(user.AgeGTE(ageMin))
}
users, err := q.All(ctx)

11.5 Column vs column comparison

SQL:


SELECT * FROM posts
WHERE updated_at > created_at;

ENT:


client.Post.Query().Modify(
  func(s *entsql.Selector) {
    s.Where(
      entsql.GT(
        s.C(post.FieldUpdatedAt),
        s.C(post.FieldCreatedAt)
      )
    )
  }
).All(ctx)

11.6 Custom SQL function

SQL:


SELECT * FROM users
WHERE LENGTH(name) >= 5;

ENT:


client.User.Query().Modify(
  func(s *entsql.Selector) {
    s.Where(
      entsql.GTE(
        entsql.Func(
          "length",
          s.C(user.FieldName)
        ),
        5
      )
    )
  }
).All(ctx)

11.7 Order by joined column

SQL:


SELECT p.* FROM posts p
JOIN users u ON ...
ORDER BY u.name ASC;

ENT:


client.Post.Query().Modify(
  func(s *entsql.Selector) {
    t := sql.Table(user.Table)
    s.Join(t).On(
      s.C(post.FieldAuthorID),
      t.C(user.FieldID)
    )
    s.OrderBy(t.C(user.FieldName))
  }
).All(ctx)

11.8 Window functions (Postgres)

SQL:


SELECT *, ROW_NUMBER() OVER (
  PARTITION BY author_id
  ORDER BY created_at DESC
) AS rn ...

ENT:


client.Post.Query().Modify(
  func(s *entsql.Selector) {
    s.AppendSelect(
      "ROW_NUMBER() OVER (" +
      "PARTITION BY author_id " +
      "ORDER BY created_at DESC) " +
      "AS rn"
    )
  }
).Scan(ctx, &out)

11.9 Raw SQL query with args

SQL:


SELECT * FROM users
WHERE custom_condition = $1;

ENT:


var users []*ent.User
client.QueryContext(
  ctx,
  "SELECT * FROM users " +
  "WHERE custom_condition = $1",
  param
).Scan(&users)

11.10 Computed fields (COALESCE)

SQL:


SELECT COALESCE(age, 0) AS age
FROM users;

ENT:


client.User.Query().Modify(
  func(s *entsql.Selector) {
    s.Select(
      "COALESCE(age, 0) AS age"
    )
  }
).Scan(ctx, &out)

11.11 Date/time comparisons

SQL:


SELECT * FROM posts
WHERE created_at >
  NOW() - INTERVAL '7 days';

ENT:


client.Post.Query().
  Where(
    post.CreatedAtGT(
      time.Now().AddDate(0, 0, -7)
    )
  ).All(ctx)

12. Edge Operations

12.1 Add edge (connect existing entities)

SQL:


UPDATE posts SET author_id=1
WHERE id=5;

ENT:


client.Post.UpdateOneID(5).
  SetAuthorID(1).Save(ctx)

12.2 Add edge using entity

SQL:


UPDATE posts SET author_id=?
WHERE id=5;

ENT:


client.Post.UpdateOneID(5).
  SetAuthor(userEntity).Save(ctx)

12.3 Add multiple edges (many-to-many)

SQL:


INSERT INTO user_groups(user_id, group_id)
VALUES (1, 10), (1, 20);

ENT:


client.User.UpdateOneID(1).
  AddGroupIDs(10, 20).Save(ctx)

12.4 Remove edge (disconnect)

SQL:


UPDATE posts SET author_id=NULL
WHERE id=5;

ENT:


client.Post.UpdateOneID(5).
  ClearAuthor().Save(ctx)

12.5 Remove multiple edges (many-to-many)

SQL:


DELETE FROM user_groups
WHERE user_id=1
  AND group_id IN (10, 20);

ENT:


client.User.UpdateOneID(1).
  RemoveGroupIDs(10, 20).Save(ctx)

12.6 Clear all edges

SQL:


DELETE FROM user_groups
WHERE user_id=1;

ENT:


client.User.UpdateOneID(1).
  ClearGroups().Save(ctx)

13. Special Queries

13.1 Soft delete (set deleted_at)

SQL:


UPDATE users SET deleted_at=NOW()
WHERE id=1;

ENT:


client.User.UpdateOneID(1).
  SetDeletedAt(time.Now()).
  Save(ctx)

13.2 Query only non-deleted records

SQL:


SELECT * FROM users
WHERE deleted_at IS NULL;

ENT:


client.User.Query().
  Where(user.DeletedAtIsNil()).
  All(ctx)

13.3 Find or create (idempotent insert)

SQL:


INSERT INTO users(email, name)
VALUES ('a@b.com', 'John')
ON CONFLICT (email)
DO NOTHING RETURNING *;

ENT:


u, err := client.User.Query().
  Where(user.EmailEQ("a@b.com")).
  Only(ctx)
if ent.IsNotFound(err) {
    u, err = client.User.Create().
      SetEmail("a@b.com").
      SetName("John").
      Save(ctx)
}

13.4 Batch get by IDs

SQL:


SELECT * FROM users
WHERE id IN (1, 2, 3, 4, 5);

ENT:


client.User.Query().
  Where(user.IDIn(1, 2, 3, 4, 5)).
  All(ctx)

13.5 Random record selection

SQL:


SELECT * FROM users
ORDER BY RANDOM()
LIMIT 1;

ENT:


client.User.Query().Modify(
  func(s *entsql.Selector) {
    s.OrderBy("RANDOM()")
  }
).First(ctx)

13.6 Case statement / conditional selection

SQL:


SELECT *, CASE WHEN age<18
  THEN 'minor'
  ELSE 'adult'
END AS category
FROM users;

ENT:


client.User.Query().Modify(
  func(s *entsql.Selector) {
    s.AppendSelect(
      "CASE WHEN age<18 " +
      "THEN 'minor' " +
      "ELSE 'adult' " +
      "END AS category"
    )
  }
).Scan(ctx, &out)

13.7 JSON field query (Postgres)

SQL:


SELECT * FROM users
WHERE metadata->>'role' = 'admin';

ENT:


client.User.Query().Where(
  func(s *entsql.Selector) {
    s.Where(
      entsql.EQ(
        entsql.Raw(
          "metadata->>'role'"
        ),
        "admin"
      )
    )
  }
).All(ctx)

13.8 Full-text search (Postgres)

SQL:


SELECT * FROM posts
WHERE to_tsvector('english', content)
  @@ to_tsquery('golang');

ENT:


client.Post.Query().Modify(
  func(s *entsql.Selector) {
    s.Where(
      entsql.Raw(
        "to_tsvector('english', " +
        "content) @@ " +
        "to_tsquery(?)",
        "golang"
      )
    )
  }
).All(ctx)

📚 Additional Resources

Common Patterns


// 1. Reusable query predicates
func publishedPosts() predicate.Post {
    return post.PublishedEQ(true)
}
client.Post.Query().Where(publishedPosts()).All(ctx)
 
// 2. Query chaining for dynamic filters
q := client.User.Query()
if nameFilter != "" {
    q = q.Where(user.NameContains(nameFilter))
}
if ageMin > 0 {
    q = q.Where(user.AgeGTE(ageMin))
}
users, err := q.All(ctx)
 
// 3. Pagination helper
func paginate(page, pageSize int) *ent.UserQuery {
    offset := (page - 1) * pageSize
    return client.User.Query().Limit(pageSize).Offset(offset)
}

Import Requirements


// Basic ENT imports
import (
    "ent-playground/ent"
    "ent-playground/ent/user"
    "ent-playground/ent/post"
    "ent-playground/ent/predicate"
)
 
// For advanced SQL selector usage
import (
    entsql "entgo.io/ent/dialect/sql"
)

Query Return Types

Method	Returns	Use Case
`.All(ctx)`	`[]*ent.User, error`	Get all matching records
`.Only(ctx)`	`*ent.User, error`	Get exactly one (error if 0 or 2+)
`.First(ctx)`	`*ent.User, error`	Get first record (error if none)
`.Count(ctx)`	`int, error`	Count matching records
`.Exist(ctx)`	`bool, error`	Check if any exists
`.IDs(ctx)`	`[]int, error`	Get only IDs
`.Scan(ctx, &v)`	`error`	Scan into custom struct
`.Strings(ctx)`	`[]string, error`	Get string field values
`.Ints(ctx)`	`[]int, error`	Get int field values

Summary

This guide provides 120 SQL-to-ENT query comparisons covering:

✅ Basic queries - Selection, filtering, counting
✅ String operations - LIKE, contains, prefix/suffix matching
✅ Sorting & pagination - ORDER BY, LIMIT, OFFSET, cursor-based
✅ Aggregations - COUNT, SUM, AVG, MIN, MAX, GROUP BY, HAVING
✅ Logical operators - AND, OR, NOT, complex conditions
✅ Relationships - Eager loading, joins, edge filtering
✅ CRUD operations - INSERT, UPDATE, DELETE, upsert
✅ Transactions - BEGIN/COMMIT/ROLLBACK patterns
✅ Advanced queries - Subqueries, raw SQL, window functions
✅ Edge operations - Add, remove, clear relationships
✅ Special queries - Soft deletes, find-or-create, full-text search

Quick reference for ENT developers migrating from SQL! 🚀