pgm/playground/qry_select_test.go

package playground

import (
	"testing"

	"code.patial.tech/go/pgm"
	"code.patial.tech/go/pgm/playground/db"
	"code.patial.tech/go/pgm/playground/db/branchuser"
	"code.patial.tech/go/pgm/playground/db/employee"
	"code.patial.tech/go/pgm/playground/db/user"
	"code.patial.tech/go/pgm/playground/db/usersession"
)

func TestQryBuilder2(t *testing.T) {
	got := db.User.Debug().Select(user.Email, user.FirstName).
		Join(db.UserSession, user.ID, usersession.UserID).
		Join(db.BranchUser, user.ID, branchuser.UserID).
		Where(
			user.ID.Eq(1),
			pgm.Or(
				user.StatusID.Eq(2),
				user.UpdatedAt.Eq(3),
			),
			user.MfaKind.Eq(4),
			pgm.Or(
				user.FirstName.Eq(5),
				user.MiddleName.Eq(6),
			),
		).
		Where(
			user.LastName.NotEq(7),
			user.Phone.Like("%123%"),
			user.UpdatedAt.IsNotNull(),
			user.Email.NotInSubQuery(db.User.Select(user.ID).Where(user.ID.Eq(123))),
		).
		Limit(10).
		Offset(100).
		String()

	expected := "SELECT users.email, users.first_name FROM users JOIN user_sessions ON users.id = user_sessions.user_id" +
		" JOIN branch_users ON users.id = branch_users.user_id WHERE users.id = $1 AND (users.status_id = $2 OR users.updated_at = $3)" +
		" AND users.mfa_kind = $4 AND (users.first_name = $5 OR users.middle_name = $6) AND users.last_name != $7 AND users.phone" +
		" LIKE $8 AND users.updated_at IS NOT NULL AND users.email NOT IN(SELECT users.id FROM users WHERE users.id = $9) LIMIT 10 OFFSET 100"
	if expected != got {
		t.Errorf("\nexpected: %q\ngot: %q", expected, got)
	}
}

func TestSelectWithHaving(t *testing.T) {
	expected := "SELECT employees.department, AVG(employees.salary), COUNT(employees.id)" +
		" FROM employees GROUP BY employees.department HAVING AVG(employees.salary) > $1 AND COUNT(employees.id) > $2"
	got := db.Employee.
		Select(employee.Department, employee.Salary.Avg(), employee.ID.Count()).
		GroupBy(employee.Department).
		Having(employee.Salary.Avg().Gt(50000), employee.ID.Count().Gt(5)).
		String()

	if expected != got {
		t.Errorf("\nexpected: %q\ngot: %q", expected, got)
	}
}

func TestSelectWithJoin(t *testing.T) {
	got := db.User.Select(user.Email, user.FirstName).
		Join(db.UserSession, user.ID, usersession.UserID).
		LeftJoin(db.BranchUser, user.ID, branchuser.UserID, pgm.Or(branchuser.RoleID.Eq("1"), branchuser.RoleID.Eq("2"))).
		Where(
			user.ID.Eq(3),
			pgm.Or(
				user.StatusID.Eq(4),
				user.UpdatedAt.Eq(5),
			),
		).
		Limit(10).
		Offset(100).
		String()

	expected := "SELECT users.email, users.first_name " +
		"FROM users JOIN user_sessions ON users.id = user_sessions.user_id " +
		"LEFT JOIN branch_users ON users.id = branch_users.user_id AND (branch_users.role_id = $1 OR branch_users.role_id = $2) " +
		"WHERE users.id = $3 AND (users.status_id = $4 OR users.updated_at = $5) " +
		"LIMIT 10 OFFSET 100"
	if expected != got {
		t.Errorf("\nexpected: %q\ngot: %q", expected, got)
	}
}

// BenchmarkSelect-12    	  638901	      1860 ns/op	    4266 B/op	      61 allocs/op
func BenchmarkSelect(b *testing.B) {
	for b.Loop() {
		_ = db.User.Select(user.Email, user.FirstName).
			Join(db.UserSession, user.ID, usersession.UserID).
			Join(db.BranchUser, user.ID, branchuser.UserID).
			Where(
				user.ID.Eq(1),
				pgm.Or(
					user.StatusID.Eq(2),
					user.UpdatedAt.Eq(3),
				),
				user.MfaKind.Eq(4),
				pgm.Or(
					user.FirstName.Eq(5),
					user.MiddleName.Eq(6),
				),
			).
			Where(
				user.LastName.NotEq(7),
				user.Phone.Like("%123%"),
				user.Email.NotInSubQuery(db.User.Select(user.ID).Where(user.ID.Eq(123))),
			).
			Limit(10).
			Offset(100).
			String()
	}
}