logo Buffalo slack logo
Querying
🇬🇧 EN 🇪🇸 ES
Database

Querying

In this chapter, you’ll learn how to retrieve data from your database using Pop.

Find By ID

user := User{}
err := models.DB.Find(&user, id)

Find All

users := []User{}
err := models.DB.All(&users)
err = models.DB.Where("id in (?)", 1, 2, 3).All(&users)

Find All with Order

// To retrieve records from the database in a specific order, you can use the Order method
users := []User{}
err := models.DB.Order("id desc").All(&users)

Find Last

// Last() orders by created_at
user := models.User{}
err := tx.Last(&user)

Find Where

users := []models.User{}
query := models.DB.Where("id = 1").Where("name = 'Mark'")
err := query.All(&users)

err = tx.Where("id in (?)", 1, 2, 3).All(&users)

Using in Queries

err = models.DB.Where("id in (?)", 1, 2, 3).All(&users)
err = models.DB.Where("id in (?)", 1, 2, 3).Where("foo = ?", "bar").All(&users)
Unfortunately, for a variety of reasons you can’t use an and query in the same Where call as an in query. 
// does not work:
err := tx.Where("id in (?) and foo = ?", 1, 2, 3, "bar").All(&users)

// works:
err := tx.Where("id in (?)", 1, 2, 3).Where("foo = ?", "bar").All(&users)

Select specific columns

Select allows you to load specific columns from a table. Useful when you don’t want all columns from a table to be loaded in a query.

err = tx.Select("name").All(&users)
// SELECT name FROM users

err = tx.Select("max(age)").All(&users)
// SELECT max(age) FROM users

err = tx.Select("age", "name").All(&users)
// SELECT age, name FROM users

Join Query

// page: page number
// perPage: limit

roles := []models.UserRole{}

q := tx.Q()
q.LeftJoin("roles", "roles.id = user_roles.role_id")
q.LeftJoin("users u", "u.id = user_roles.user_id")
q.Where(`roles.name like ?`, name)
q.Paginate(page, perPage)

err := q.All(&roles)

Count records

query := tx.Q()
count, err := query.Count(&models.User{})

query := tx.Q()
count, err := query.Where("name = ?", "John").Count(&models.User{})

query := tx.Q()
count, err := query.CountByField(&models.User{}, "first_name")
// Equals to
count, err := query.Count(&models.User{},, "first_name")

Pop to SQL

q := tx.Q()
q.LeftJoin("roles", "roles.id = user_roles.role_id")
q.LeftJoin("users u", "u.id = user_roles.user_id")
q.Where(`roles.name like ?`, "john")
q.Paginate(1, 20)

popModel := &pop.Model{Value: models.UserRole{}}
cols := []string{"user_roles.*", "roles.name as role_name", "u.first_name", "u.last_name"}

sql, args := tx.Q().ToSQL(popModel, cols...)

-- The original query is in one line
SELECT
    user_roles.*,
    roles.name as role_name,
    u.first_name,
    u.last_name
FROM
    user_roles AS user_roles
LEFT JOIN
    roles ON roles.id = user_roles.role_id
LEFT JOIN
    users u ON u.id = user_roles.user_id
WHERE
    roles.name like $1
LIMIT
    20
OFFSET
    0

[john]