incorrect "FilterWhere" query

[d0zer11st]

I'm getting wrong SQL query using query builder for "FilterWhere".

There is extra "OVER ()" keyword in result query.

how to reproduce:

0. bob version: github.com/stephenafamo/bob v0.26.1
1. postgres 16.2
2. Table

create table example
(
    id bigint primary key generated always as identity,
    mac           macaddr not null
)

	selectQuery := psql.Select(
		sm.From(models.TableNames.Examples),
		sm.Columns(
			models.ColumnNames.Examples.Hostname,
			psql.F(
				"count",
				"*",
			).FilterWhere(
				models.ExampleColumns.ID.GT(psql.Arg(0)),
			).As("non_zero_ids"),
		),
		sm.GroupBy("hostname"),
	)
	queryString, _, buildErr := selectQuery.Build()
	if buildErr != nil {
		log.Fatal(buildErr)
	}
	expectedQueryString := "select hostname, count(*) filter (where id > 0) as non_zero_ids from example group by hostname"
	fmt.Println(queryString)
	fmt.Println(expectedQueryString)

Got:

SELECT 
hostname, count(*) FILTER (WHERE ("example"."id" > $1))OVER () AS "non_zero_ids"
FROM example
GROUP BY hostname

Expected:

select 
hostname, 
count(*) filter (where id > 0) as non_zero_ids
from example 
group by hostname

[stephenafamo]

I'll take a look

[stephenafamo]

I've made some changes in #228 that should fix this @d0zer11st.
It changes how functions are modified, but this should be better overall.

	selectQuery := psql.Select(
		sm.From(models.TableNames.Examples),
		sm.Columns(
			models.ColumnNames.Examples.Hostname,
			psql.F(
				"count",
				"*",
			)(
				fm.Filter(models.ExampleWhere.ID.GT(0)),
				fm.As("non_zero_ids"),
			),
		),
		sm.GroupBy("hostname"),
	)

Try it out and let me know what you think.

[d0zer11st]

@stephenafamo thank you for quick response.
That works for me.

I noticed, psql.F(...)().As("name") still works, will this notation be deprecated ?

[stephenafamo]

I noticed, psql.F(...)().As("name") still works, will this notation be deprecated ?

Unlikely.

For ease of use, *dialect.Function embeds dialect.Expression. This makes it easy to fluently chain expressions involving function. For example:

HAVING count(1) > 2

sm.Having(psql.F("count", 1).GT(psql.Raw(2))

This is more annoying to do without embedding, but dialect.Expression also has an As() method.

The fm.As() mod instead is specifically to support some syntax when using a function as the source of a SELECT query.

I'll see if there is a way to reduce duplication

[d0zer11st]

got you.
thanks