Advanced Querying

Joins

Joins may be not be considered advanced, but it seemed like a better fit here because we'll be creating another model to demonstrate the use of joins.

Joins are a way of combining two data sets together given a common key. For example in our users table we have an ID (that's automatically generated by pop) that can be used as a foreign key in another table. I don't want to cover the ins and outs of relational databases so here's a link to a great Wikipedia article on relational databases.

First, let's create a new model using soda and migrate it to create the table in the database

$ soda generate model favorite_food user:uuid food:text -e development
v3.41.1

--> models/favorite_food.go
--> models/favorite_food_test.go
--> goimports -w main.go models/favorite_food.go models/favorite_food_test.go models/user.go models/user_test.go
> migrations/20180110140002_create_favorite_foods.up.fizz
> migrations/20180110140002_create_favorite_foods.down.fizz
$ soda migrate up -e development
v3.41.1

> create_favorite_foods

0.0076 seconds
dumped schema for ./development.sqlite

Now we'll write a program to pull the ID for the user, and populate the table with their favorite food

package main

import (
    "bitbucket.org/pop-book/models"
    "github.com/gobuffalo/pop"
    "log"
)

func main() {
    tx, err := pop.Connect("development")
    if err != nil {
        log.Panic(err)
    }

    foods := [3]string{"cake", "steak", "beer"}
    users := []models.User{}
    err = tx.All(&users)
    for i := 0; i < len(users); i++ {
        user := users[i]
        favoriteFood := models.FavoriteFood{User: user.ID, Food: foods[i]}
        _, err = tx.ValidateAndSave(&favoriteFood)
        if err != nil {
            log.Panic(err)
        }
    }
}

Verifying the data is there and good:

sqlite> SELECT * FROM favorite_foods;
113291c8-b4f4-4e8e-ba8a-821a6299c4d9|bf3ba75b-8dfe-4619-b832-31c4a087a589|cake|2018-01-10 09:21:21.380048-05:00|2018-01-10 09:21:21.380049-05:00
6bc22cfb-2c84-4175-a03b-d4396576ccf3|61b2db41-a66b-4093-8580-be0b114ec04e|steak|2018-01-10 09:21:21.381272-05:00|2018-01-10 09:21:21.381282-05:00
4ecff328-3c5d-4e8a-974f-9852d9626b91|0ce8fedf-f875-4240-b8c3-cf6291cd7474|beer|2018-01-10 09:21:21.381904-05:00|2018-01-10 09:21:21.381905-05:00
sqlite> SELECT * FROM users;
bf3ba75b-8dfe-4619-b832-31c4a087a589|Mrs.|Jessica|Jones|Private security, super hero.|2017-12-19 14:06:20.473953-05:00|2017-12-28 21:57:45.771782-05:00|NYC, NY|1900
61b2db41-a66b-4093-8580-be0b114ec04e|Mr.|Luke|Cage|Hero for hire.|2017-12-19 14:06:20.476127-05:00|2017-12-28 21:50:00.096033-05:00|NYC, NY|1900
0ce8fedf-f875-4240-b8c3-cf6291cd7474|Mr.|Frank|Castle|USMC, badass.|2018-01-09 16:25:33.702813-05:00|2018-01-09 16:25:33.702816-05:00|Hoboken, NJ|1900

Now, let's write a program to join the two tables together, so that we can print a list of the full name and their favorite food:

package main

import (
    "bitbucket.org/pop-book/models"
    "github.com/gobuffalo/pop"
    "log"
    "fmt"
)

func main() {
    tx, err := pop.Connect("development")
    if err != nil {
        log.Panic(err)
    }

    type Favorite struct {
        FirstName string `json:"first_name" db:"first_name"`
        LastName  string `json:"last_name" db:"last_name"`
        Food      string `json:"food" db:"food"`
    }

    favorites := []Favorite{}
    all_foods := tx.Where("favorite_foods.food IS NOT NULL")
    query := all_foods.LeftJoin("users", "favorite_foods.user=users.id")

    sql, args := query.ToSQL(&pop.Model{Value: models.FavoriteFood{}}, "favorite_foods.food",
        "users.first_name", "users.last_name")
    err = all_foods.RawQuery(sql, args...).All(&favorites)
    for i := 0; i < len(favorites); i++ {
        fmt.Printf("%s %s really loves %s\n", favorites[i].FirstName, favorites[i].LastName, favorites[i].Food)
    }
}

Compile and run:

$ ./main
Jessica Jones really loves cake
Luke Cage really loves steak
Frank Castle really loves beer

Let's have a bit of an explanation of what went on there. First, we created a struct in line to represent the joined data model. This is because the query needs a model to put the data into. Now, we could generate a model the soda way, but for this small use case that would be unnecessary since it would also create another database table and migration set.

Next, we set a Where clause, primarily to ensure that we're not getting empty data and so that we can get back a query object. Next, we run a left join between the users table and the favorite_foods table. Then we force that query to a SQL string, then return all the matches in All. The data set returned is in the format of FirstName + " " LastName + " " really loves " + Food.

Raw Queries

Sometimes it's just faster or more convenient to write a SQL statement than it is to use query building parts of any ORM. pop gives you this ability as well. Suppose you want to just want to write a quick and dirty lookup:

package main

import (
    "bitbucket.org/pop-book/models"
    "github.com/gobuffalo/pop"
    "log"
    "fmt"
)

func main() {
    tx, err := pop.Connect("development")
    if err != nil {
        log.Panic(err)
    }
    users := []models.User{}
    err = tx.RawQuery("SELECT * FROM users WHERE title=?", "Mr.").All(&users)
    if err != nil {
        log.Panic(err)
    } else {
        for i := 0; i < len(users); i++ {
            fmt.Printf("%s %s %s\n", users[i].Title, users[i].FirstName, users[i].LastName)
        }
    }
}

Compile and run:

./main
Mr. Luke Cage
Mr. Frank Castle

This is basically building out the same thing as what is happening under the hood with the query builder.