Query builder should add components rather than replacing them. Example: Calling WHERE multiple times should add additional clauses, rather than replace the only clause

[veqryn]

trafficstars

Describe the bug The query builder replaces rather than adds components and clauses. This goes against the behavior of every single query builder I've ever worked with, and is rather surprising and unintuitive, while also making some situations difficult or impossible.

Environment (please complete the following information):

• OS: linux and mac
• Database: postgres
• Database driver: pgx v5
• Jet version: 2.11.1

Code snippet

func (d DAO) SelectAllAccountsByFilter(ctx context.Context, filters models.Filters) ([]model.Accounts, error) {
	query := SELECT(
		Accounts.AllColumns,
	).FROM(
		Accounts,
	)

	if len(filters.Names) > 0 {
		query = query.WHERE(Accounts.Name.IN(Strings(filters.Names)...))
	}
	if filters.Active != nil {
		query = query.WHERE(Accounts.Active.EQ(Bool(*filters.Active)))
	}
	if len(filters.FavColors) > 0 {
		query = query.WHERE(Accounts.FavColor.IN(Strings(filters.FavColors)...))
	}

Expected behavior When passed a Filters struct with all three values set, I expected to see an SQL statement that included 3 clauses in the WHERE portion. Instead, only the last one was set.

I expect this kind of "builder" behavior for all parts of the query, including the SELECT, the FROM, the ORDER_BY, everything. (I should be able to call SELECT multiple times, to add new columns to be selected. Same for FROM, ORDER_BY, everything, etc.)

Why? Because building complex queries or queries for complex api's, are often dependent on logic, so there is often a need to change parts of the query based if statements.

Yes, I realize an ugly workaround would be to build it separately, then pass it into JET when finished, but besides being ugly, it defeats a lot of the purpose of a query builder.