Feature Idea JoinRelation

[Janther]

Since go-pg already knows what the table names and foreign keys are there could be a JoinRelation(). It could work like this:

// Normal Join
db.Model(&items).
    Join(`INNER JOIN "items_to_orders" as "item_to_order"`).
    JoinOn(`"item_to_order"."item_id" = "item"."id"`).
    Join(`INNER JOIN "orders" as "order"`).
    JoinOn(`"items_to_orders"."order_id" = "order"."id"`).
    Where(`"order"."paid" IS TRUE`).
    Select()

// Join Relation
db.Model(&items).
    JoinRelation("Orders").
    Where(`"order"."paid" IS TRUE`).
    Select()

[ErmakovDmitriy]

Hi @Janther,

Could you take a look at https://github.com/go-pg/pg/wiki/Writing-Queries#column-names ?

err := db.Model(book).Relation("Author").Select()
// SELECT
//   "book"."id", "book"."title", "book"."text",
//   "author"."id" AS "author__id", "author"."name" AS "author__name"
// FROM "books"
// LEFT JOIN "users" AS "author" ON "author"."id" = "book"."author_id"
// WHERE id = 1

It is possible that "Relation" method solves your request or I do not understand your idea.

[Janther]

Well, in the example that I put what I really want are the items, I care about the orders only whether they were paid or not.

The Relation method is mainly used when I want to load into memory both structs.

And it results in extra code.

db.Model(&items).
    Relation(`Orders.Paid`).
    Select()

paidItems := []Item{}
for _, item := range items {
    if item.Order.Paid {
        paidItems = append(paidItems, item)
    }
}

Or I could go the other way around and ask for the Orders first.

db.Model(&orders).
    Column("paid").
    Relation(`Items`).
    Where(`"orders"."paid" IS TRUE`).
    Select()

paidItems := []Item{}
for _, order := range orders {
    paidItems = append(paidItems, order.items...)
}

These 2 approaches put extra work on the Golang application by loading Order structs into memory and performing an iteration while the database is already really good at doing these things if you ask nicely.

I'll repeat the code in my first example.

db.Model(&items).
    Join(`INNER JOIN "items_to_orders" as "item_to_order"`).
    JoinOn(`"item_to_order"."item_id" = "item"."id"`).
    Join(`INNER JOIN "orders" as "order"`).
    JoinOn(`"items_to_orders"."order_id" = "order"."id"`).
    Where(`"order"."paid" IS TRUE`).
    Select()

This example is performant for everybody Golang (only loads into memory what's necessary), the database (already super clever in joins and filtering), and the network(no extra data sent). But it's a bit of extra work for the developer and go-pg already knows everything involved. My proposal is to offer the Relation knowledge for Where statements on the Relation.

db.Model(&items).
    JoinRelation("Orders").
    Where(`"order"."paid" IS TRUE`).
    Select()

From all of these ways of developing the same solution, I feel my proposal makes go-pg better for everybody. Also keeping in mind that my example is a rather simple one, it could go deeper.

// All the recipes that have ingredients from Spain or Italy
db.Model(&recipes).
    JoinRelation("Ingredients").
    JoinRelation("Ingredients.Country").
    WhereIn(`"country"."name" IN (?)`, []string{"Italy", "Spain"}).
    Select()

Also some decision and design will have to be taken around the nature of the join satements (In my mind they should be INNER for this type of API to return what I expect).

Maybe it's too much of a scope for what go-pg wants to achieve since this can already be done by writing a few more lines of code. Or maybe generating the Query is really not performant at all and better have the developer do it instead.

[Janther]

I'd also would like to state that I love go-pg and I understand that there's nothing "missing" for my queries to be built. I just see places here and there where it could grow.

[Keithwachira]

I do feel like this can be achieved using a subquery instead of using the golang to filter the paid .....orders. https://pg.uptrace.dev/queries/#subqueries from the documentation