Selecting

There are various ways of selecting data from a db in qb. The following examples assume that you have defined the following tables using the table api;

Go

package main

import (
	"fmt"
	"github.com/aacanakin/qb"
)

func main() {
	db, _ := qb.New("mysql", "root:@tcp(localhost:3306)/qb_test?charset=utf8")
	defer db.Close()

	users := qb.Table(
		"user",
		qb.Column("id", qb.Varchar().Size(36)),
		qb.Column("email", qb.Varchar().Unique().NotNull()),
		qb.Column("password", qb.Varchar().NotNull()),
		qb.Column("name", qb.Varchar().NotNull()),
		qb.PrimaryKey("id"),
	)

	sessions := qb.Table(
		"session",
		qb.Column("id", qb.Varchar().Size(36)),
		qb.Column("user_id", qb.Varchar().Size(36)),
		qb.Column("auth_token", qb.Varchar().Size(36)),
		qb.Column("created_at", qb.Timestamp().NotNull()),
		qb.Column("expires_at", qb.Timestamp().Null()),
		qb.ForeignKey().Ref("user_id", "users", "id"),
	)

  fmt.Println(users.Create(db.Dialect()))
	fmt.Println(sessions.Create(db.Dialect()))
}

A simple select query example would be the following;

Go

sel := users.
		Select(users.C("id"), users.C("email")).
		Where(users.C("name").Eq("Al Pacino")).
		Build(db.Dialect())

fmt.Println(sel.SQL())
fmt.Println(sel.Bindings())

The output would be the following;

SQL

SELECT id, email
FROM user
WHERE user.name = ?;
[Al Pacino]

Selecting by And/Or Combiners

A more complex example using multiple where clauses with And combiner would be the following;

Go

sel := users.
		Select(users.C("id"), users.C("email")).
		Where(
			qb.And(
				users.C("name").Eq("Al Pacino"),
				users.C("email").NotEq("[email protected]"),
			)).
		Build(db.Dialect())

fmt.Println(sel.SQL())
fmt.Println(sel.Bindings())

The output would be the following;

SQL

SELECT id, email
FROM user
WHERE (user.name = ? AND user.email != ?);
[Al Pacino [email protected]]

The OR combiner can also be used in the same way;

Go

sel := users.
		Select(users.C("id"), users.C("email")).
		Where(
			qb.Or(
				users.C("name").Eq("Al Pacino"),
				users.C("email").NotEq("[email protected]"),
			)).
		Build(db.Dialect())

fmt.Println(sel.SQL())
fmt.Println(sel.Bindings())

It will output the following;

SQL

SELECT id, email
FROM user
WHERE (user.name = ? OR user.email != ?);
[Al Pacino [email protected]]

Moreover, And & Or functions can be used recursively;

Go

sel := users.
		Select(users.C("id"), users.C("email")).
		Where(
			qb.And(
				users.C("name").Eq("Al Pacino"),
				qb.Or(
					users.C("email").Eq("[email protected]"),
					users.C("email").Eq("[email protected]"),
				),
			)).
		Build(db.Dialect())

fmt.Println(sel.SQL())
fmt.Println(sel.Bindings())

The output would be the following;

SQL

SELECT id, email
FROM user
WHERE (user.name = ? AND (user.email = ? OR user.email = ?));
[Al Pacino [email protected] [email protected]]

Using Joins

Let's retrieve user sessions by using inner join;

Go

sel := qb.
		Select(
			sessions.C("id"),
			sessions.C("user_id"),
			sessions.C("created_at"),
			sessions.C("expires_at"),
		).
		From(sessions).
		InnerJoin(users, sessions.C("user_id"), users.C("id")).
		Where(sessions.C("user_id").Eq("3af82cdc-4d21-473b-a175-cbc3f9119eda")).
		Build(db.Dialect())

fmt.Println(sel.SQL())
fmt.Println(sel.Bindings())

The output would be the following;

SQL

SELECT session.id, session.user_id, session.created_at, session.expires_at
FROM session
INNER JOIN user ON session.user_id = user.id
WHERE session.user_id = ?;
[3af82cdc-4d21-473b-a175-cbc3f9119eda]

There are several types of joins that can be made in qb. Here's the following join table;

Join	Description
InnerJoin(table TableElem, fromCol ColumnElem, col ColumnElem)	Performs an INNER JOIN
LeftJoin(table TableElem, fromCol ColumnElem, col ColumnElem)	Performs a LEFT JOIN
RightJoin(table TableElem, fromCol ColumnElem, col ColumnElem)	Performs a RIGHT JOIN
CrossJoin(table TableElem)	Performs a CROSS JOIN

🚧

Beware about dialects' join supports

These join functions doesn't know about dialect's driver(sqlite, mysql, postgres). Therefore, for instance, you need to know if sqlite have right join support. This issue will be addressed in further releases of qb.

As you might notice, CrossJoin (Cartesian Product) does not require column joining. Therefore, it has only the table parameter.