join argument wrong order

[vendelin8]

Hi,

Here's my example that works with a hack:

package main

import (
    "fmt"

    squ "github.com/Masterminds/squirrel"
)

func main() {
    companyName := "Some Company"
    employeeID := 123
    qb := squ.Select("c.name, e.id").
        From("companies c").
        LeftJoin("employees e on c.id = e.company_id AND e.id = $2", companyName).
        Where(squ.Eq{"companies.name": employeeID}).
        PlaceholderFormat(squ.Dollar)
    fmt.Println(qb.ToSql())
}

The hack is: I changed parameters because companies.name is added as $1, but it's value employeeID is the second one in the values array.

If I set e.id = $1, I end up with two $1 clauses and two values in an error.

Do you have any other workaround for this? Thanks!

[wilriker]

Even though specifying PlaceholderFormat(squ.Dollar) you still need to use ? as a placeholder when writing SQL statements - otherwise squirrel won't recognize them as parameters. If you write LeftJoin("[...] AND e.id = ?", companyName) it should work as you expect.

[vendelin8]

I'm not sure, because PostgreSQL wants $ placeholders... anyway, we changed that part, so let's close it now.

[wilriker]

Yes, PostgreSQL wants $N placeholders. Therefore setting PlaceholderFormat(squ.Dollar) will generate output SQL using these kinds of placeholders. But user-input into squirrel while building the query is expected to use ? as a placeholder. If you pass $1 or $2 to squirrel it will take this as an arbitrary literal string and not recognize that you intended to mean a placeholder.

So, your full example would look like this

package main

import (
    "fmt"

    squ "github.com/Masterminds/squirrel"
)

func main() {
    companyName := "Some Company"
    employeeID := 123
    qb := squ.Select("c.name, e.id").
        From("companies c").
        LeftJoin("employees e on c.id = e.company_id AND e.id = ?", companyName).
        Where(squ.Eq{"companies.name": employeeID}).
        PlaceholderFormat(squ.Dollar)
    fmt.Println(qb.ToSql())
}

If you run this it will output:

SELECT c.name, e.id FROM companies c LEFT JOIN employees e on c.id = e.company_id AND e.id = $1 WHERE companies.name = $2 [Some Company 123] <nil>