Why does Relation() use Left Outer Join instead of Inner Join? Isn't Inner Join faster?

[sgon00]

Hi, when I try the README.md example, db.Model(story).Relation("Author").Where("story.id = ?", story1.ID).Select() will simply run SELECT ... FROM "stories" AS "story" LEFT JOIN "users" AS "author" ON "author"."id" = "story"."author_id" WHERE (story.id = 1).

I heard go-pg is good on performance. But why LEFT OUTER JOIN here? INNER JOIN is always faster than other joins. I don't really get it. Thanks a lot.

[vmihailenco]

Because story.author_id can be NULL and LEFT JOIN handles that nicely.

[sgon00]

Thanks for the quick reply. I thought it was a one-to-one relationship. If it's null, that becomes one to zero or one relationship. But whatever, is that possible to change the LEFT OUTER JOIN to INNER JOIN somehow for better performance? Thanks.

[sgon00]

@vmihailenco dear sir, I found a workaround. If I am sure story.author_id is NOT NULL, is the following manual INNER JOIN a correct and recommended approach for this query?

story = new(Story)
err = db.Model(story).
    Column("story.id", "story.title").
    ColumnExpr("author.id AS author__id, author.name AS author__name, author.emails AS author__emails").
    Join("INNER JOIN users AS author ON author.id = story.author_id").
    Where("story.id = ?", story1.ID).
    Select()

I am new to go-pg and just want to make sure what I did is the recommended approach. Thanks a lot.

[vmihailenco]

That is definitely an option, but it does not use Object-Relational Mapping - you can use author.id AS author__id to load data into any struct field. I guess you don't have any choice but to use it at the moment.

Ideally go-pg ORM should have some knob to switch between LEFT JOIN and INNER JOIN, but we don't have it.

[sgon00]

I am very sorry to interrupt you again on this topic. Regarding the words you said:

That is definitely an option, but it does not use Object-Relational Mapping - you can use author.id AS author__id to load data into any struct field. I guess you don't have any choice but to use it at the moment.,

I don't quite understand it. Sorry that I am not a native English speaker. Can you please explain a bit more on this paragraph? Will this approach cause problem in the future so that I should not use? Sorry that I am new on this.

[vmihailenco]

By all means use it. It will continue to work.

I mean that you can write something like

type Author struct {
    ID int
    Name string
}

type Book struct {
    Author Author
}

book := new(Book)
db.Model(book).ColumnExpr("1 as author__id, 'author name' as author__name").Select()

And it will load data into Book.Author as expected even though you did not define any relations. The only "problem" is that you go-pg does not generate join for you.

[sgon00]

@vmihailenco Thank you very much for the detail explanation. Got it.

Just one last question in this issue. If I use the go-pg built-in way such as db.Model(story).Relation("Author").Where("story.id = ?", story1.ID).Select(), is that possible to select columns from the relation "Author" (User) table? I tried some codes, but none of them work. This may become important for performance if I only want to select indexed columns instead of all columns from Author/User table.

Btw, since you didn't close this issue, should I close this issue or leave it open? I think you didn't close it may because you want to add INNER JOIN ability later.

[vmihailenco]

Relation("Author.name").Relation("Author.foo_bar") should select only name and foo_bar. You can also try this but I am not sure:

q.Relation("Author", func(q *orm.Query) (*orm.Query, error) {
    return q.Column("name", "foo_bar"), nil
})

should I close this issue or leave it open

Let's keep it open since hopefully I will be able to properly fix it some time later (not soon though).

[NathanBaulch]

I'd love to see INNER JOIN support to better handle "cascaded" soft delete.

Borrowing the author-story model above, I'd like some way to exclude stories from my query results where the author has been soft deleted. Currently these stories are returned with a nil author thanks to the extra soft delete expression injected in the join condition. This can be tested for however it gets tricky when exposing these entities via a pageable API where the client is expecting a certain number of results.

There are plenty of workarounds however they're all a bit hacky (and more work, more fragile) compared to filtering in the DB with INNER JOIN.

[frickenate]

Support for INNER JOIN with relations is really a must-have. However, for most cases, there is a simpler workaround than what other people have been posting (namely, you don't have to entirely avoid Relation()). An INNER JOIN is effectively equivalent to a LEFT JOIN if you don't have additional conditions in its ON() clause and if you add a WHERE left_joined_table.* IS NOT NULL.

The example in this ticket can be written as such:

story := &Story{}
db.Model(story).Where("story.id = ?", 1).Relation("Author", func(q *orm.Query) (*orm.Query, error) {
    return q.Where("author.* IS NOT NULL"), nil
})

Or, taking advantage of the WherePK() method:

story := &Story{Id: 1}
db.Model(story).WherePK().Relation("Author", func(q *orm.Query) (*orm.Query, error) {
    return q.Where("author.* IS NOT NULL"), nil
})