I want to express query SELECT a FROM b WHERE col>? UNION SELECT a FROM c WHERE col<? but squirrel is not supporting UNION.

I think, The following example code is good design for support UNION.

q, attrs, err := sq.Union(
        sq.Select("*").From("a").Where("col > ?", hoge),
        sq.Select("*").From("b").Where("col < ?", piyo)).ToSql()

q == "SELECT a FROM b WHERE col>? UNION SELECT a FROM c WHERE col<?"
attrs == []interface{hoge, piyo}

What do you think so? If you agree on this idea, I'll create patch and send PR. Thanks!

I can't find a way to express queries of the form SELECT * FROM table WHERE a OR b. According to the documentation (and the code I read so far), all the call of the Where method are put together using AND. It would be useful to be able to do OR too.

Here is what I came up with while thinking about what the cleanest syntax would be:

squirrel.Select("*").From("table").Where(squirrel.Or{
    conditionA,
    conditionB,
})

Here, the Where method is simply a variadic function taking a number of Or{} and And{} struct, which are merely slices of interfaces of the same kind than the current parameters of Where. That way would make complex imbrication of conditions quite easy.

What do you think about it ? If you agree with that system, would you like some help to make it ? Cheers.

Hi there! :) Here's a proposal for support of SELECT CASE statements (http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html#operator_case). The commit contains test which shows an idea which is better to discuss before writing any real code. The interface is a matter of discussion. Rationale of this proposal: In current version of squirrel we have to pregenerate a subquery with SELECT CASE, like this, for example:

    var values map[string]string = loadValues()
    args = make([]interface{}, len(values)*2)

    buf.WriteString("(SELECT CASE")
    for k, v := range values {
        buf.WriteString(" WHEN value = ? THEN ?")
        args[i*2], args[i*2+1] = value, i
    }
    buf.WriteString(" END AS col1)")

    qb := squirrel.Select(buf.String()).From("sometable")

But in this case nothing stops developer from making mistakes like forgotten escaped value. Also we can use squirrel to pregenerate a separate subquery, but the code is still messy. Support of SELECT CASE can be used to protect the developer :)

    var values map[string]string = loadValues()
    qb := squirrel.Select("").
        From("sometable").
        Case(nil)

    for k, v := range values {
        qb = qb.WhenThen(squirrel.Eq{"value": value}, i)
    }

    qb = qb.EndCase("col1")

What do you think about this?

Regards, Ivan.

Hi,

I've been playing with your library (feels great!) to handle complex queries based on user input.

I've had the idea of implementing my own intermediary "builder" methods that would help finalize the SQL query and thought about using "test examples" to quickly test the feature.

// builders_test.go, run with `go test -v`

package main

import (
	"fmt"

	sq "gopkg.in/Masterminds/squirrel.v1"
)

func mustPrintSQL(builder sq.SelectBuilder) {
	sql, params, err := builder.ToSql()
	if err != nil {
		panic(err)
	}
	fmt.Print(sql + " ")
	fmt.Println(params)
}

func ExampleBuildSearchWithNoInput() {
	builder := sq.Select("*").From("posts")
	builder = buildSearch(builder, "", "")
	mustPrintSQL(builder)
	// Output: SELECT * FROM posts []
}

func ExampleBuildSearchWithWildcard() {
	builder := sq.Select("*").From("posts")
	builder = buildSearch(builder, "title", "*hello*")
	mustPrintSQL(builder)
	// Output: SELECT * FROM posts WHERE title ILIKE ? [%hello%]
}

func ExampleBuildSearchWithoutWildcard() {
	builder := sq.Select("*").From("posts")
	builder = buildSearch(builder, "title", "Hello!")
	mustPrintSQL(builder)
	// Output: SELECT * FROM posts WHERE title = ? [Hello!]
}

I understand the query parameters need to get passed separately to the db driver, but I was wondering if it was possible to inspect the final output, for debugging or testing, especially for very complex queries.

So instead of:

SELECT * FROM posts WHERE id = ? [1]

I would see

SELECT * FROM posts WHERE id = 1

Maybe a ToSQLUnsafe(), DumpSQL() or DebugSQL() function?

Any thoughts?

This commit adds support for doing less than, less than or equal to, greater than, and greater than or equal to. I added tests as well.

Please give feedback if not merged.

The API currently includes DISTINCT SQL command, however postgres supports a DISTINCT ON command which allows you to define a column to compute distinctiveness, but still select columns as you usually would.

Is there any plans for this, or, if I submit a PM, would this be an accepted feature?

I want to create query like SELECT * FROM (SELECT Max(someval), modified FROM sometable group by someval ) WHERE modified>?, but I can't find any way because squirrel is not supporting subquery in FROM operator.

I have a idea to support subquery, see following code.

q, _, _ := sq.Select("*").FromSubQuery(
        sq.Select("Max(someval)", "modified").
                From("sometable").
                GroupBy("someval")).
        Where("modifyed > ?", t)

define:

func (b SelectBuilder) FromSubQuery(SelectBuilder) SelectBuilder

What do you think so? If you agree on this idea, I'll create patch and send PR. Thanks!

I am working on a license checker using the GitHub API and it comes back with NOASSERTION for this repo rather than MIT.

Would it be possible for you to assert your license type in the Github Admin U so the API response would say MIT?

https://docs.github.com/en/communities/setting-up-your-project-for-healthy-contributions/adding-a-license-to-a-repository

Thank you in advance or considering.

I would like to do something like this:

sq.
	Select("col").
	From("table").
	LimitSelect(sq.
		Select().
		Column(sq.Case().
			When("x = y", 10).
			Else(0).From("table"),
		),
	)

The SQL that would be generated by this would look something like:

SELECT col FROM table 
LIMIT (
  SELECT CASE WHEN x = y THEN 10 ELSE 0 END FROM table
)

This is valid for at least Postgres, as I'm able to run a query like this, however I am not sure about other flavors.

I can sort of work around this with .Suffix but it makes me rather sad to have to do it that way 😢

New API in Go 1.8 allows passing a context.Context to Exec, Query and QueryRow (via the ExecContext, QueryContext and QueryRowContext methods, respectively.)

Drivers can take advantage of this in numerous ways - we would particularly enjoy distributed tracing in Lightstep.

Trying to construct an update statement like the following in squirrel is proving impossible:

-- Update value in table a for all rows where the associated table b row has specified value in test_column
UPDATE table_a
SET table_a.value_column = $1
FROM table_b
WHERE table_b.a_id = table_a.id 
and table_b.test_column =  $2

I think UpdateBuilder should support a FROM clause to be able to leverage the abilities of postgres updates syntax: https://www.postgresql.org/docs/current/static/sql-update.html

hi Team,

We've been using Squirrel for a while now and (frankly) love it. The thing we're coming up against is there's not a lot of great support for subqueries. We're using sq.Alias to give a Window on a Column, but when params are passed in Squirrel seems to place them using the initial index despite passing Expr's around in what looks like a supported fashion.

We've had to revert a code change that we found in testing here: https://github.com/autobrr/autobrr/pull/599

This also seems to go wrong when using the WHERE IN clause, but admittedly that seems to be less supported (although greatly desired).

Thoughts, directions, patches for us to test are greatly appreciated.

I'm trying to use this library for SingleStore MySQL DB. By Insertbuilder, I've got this query string.

pgQb().Insert("hello").Columns("user_id", "name").Values(
	 	3, "test"
)

INSERT INTO hello(user_id, name) VALUES ($1,$2) [3 "test"]

Here is a valid query in SingleStore. INSERT INTO hello(user_id,name) VALUES (2, 'test323'); If I run this query in the SingleStore query field, it shows an error.

Looking forward to hearing the solution soon. Best.

I have a Quotes table, there is a created_at time, in the format of DATETIME, I am using the line

query = query.OrderBy(Quotes.created_at DESC")

Which totally works fine, but when I use it as

query = query.OrderBy(Quotes.created_at ASC")

It is throwing error, I need it in both order as it is necessary, can anybody recommend a fix or something that will help? Thanks!

MS SQL has a specific way for definition of returning values. It is similar to Postgres' RETURNING ID but should be placed in the middle of the query, so suffix is not applicable.

Example (from stackoverflow):

INSERT INTO MyTable(Name, Address, PhoneNo)
OUTPUT INSERTED.ID
VALUES ('Yatrix', '1234 Address Stuff', '1112223333')

liek this

func (m *customLabelModel) InsertBatch(rows []*Label) error {

	var labelInsertRows = strings.Join(stringx.Remove(labelFieldNames, "`id`"), ",")

	insert := squirrel.Insert("").Into(m.table).Columns(labelInsertRows)
	for _, row := range rows {
		insert = insert.Values(row.Id, row.Title, row.PicUrl, row.Sort, row.CreateTime, row.UpdateTime, row.IsDel)
	}
	query, values, err := insert.ToSql()
	if err != nil {
		return err
	}
	_, err = m.conn.Exec(query, values...)
	if err != nil {
		return err
	}
	return nil
}