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
   )

3. 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