join examples?

[krazik-intuit]

any examples on how to perform a join?

here is how sqlx did it: https://snippets.aktagon.com/snippets/757-how-to-join-two-tables-with-jmoiron-sqlx

[krazik-intuit]

actually looks to work similarly, (a comma got me again in the db tag), tho leaving this here for others since docs don't cover

[georgysavva]

Hi and thank you for opening this issue. Indeed, I didn't cover the join case in the docs explicitly, because the join isn't the responsibility of the scany library. I provided explanations of how to reuse structs with scany (this is what you would want to do in case of a join) in that docs section: https://pkg.go.dev/github.com/georgysavva/scany@v0.2.8/dbscan#hdr-Reusing_structs. Basically this section explains all mechanisms that one would want to use in case of a join.

But I think you are right, and having a high-level example of a join that showcases real-world usage would be great. I will think of a way of adding it to the docs or as a separate article. Thanks!

[arkady-bagdasarov]

Waiting for examples about how to create backref and etc

[georgysavva]

Waiting for examples about how to create backref and etc

@arkady-bagdasarov Could you please explain in more detail what kind of examples you need?

[arkady-bagdasarov]

@georgysavva for example, i how to implement has many relations? example from gorm

[georgysavva]

@georgysavva for example, i how to implement has many relations? example from gorm

@arkady-bagdasarov scany isn't an ORM. It doesn't work as gorm. You can't implement many to many or one to many relations with scany, because scany knows nothing about your relations and schema. The only way how scany can help you is by reusing your structs when you write your one to many/many to many queries with a JOIN.

Please check that docs section for details on how to reuse your structs with scany: https://pkg.go.dev/github.com/georgysavva/scany@v0.2.8/dbscan#hdr-Reusing_structs

[arkady-bagdasarov]

Thanks!

[Metalscreame]

@georgysavva Hi!

Question related to Joins examples.

SELECT o.id, o.name,  FROM table AS o 

Will scan work in this case (using AS)?

[georgysavva]

SELECT o.id, o.name, FROM table AS o

In that case the column names in SELECT are still: "id", "name". So in order for scan to work your struct fields should map to "id" and "name", e.g:

type User struct {
    ID string
    Name string
}

or

type User struct {
    UserID string `db:"id"`
    UserName string `db:"name"` 
}

[Metalscreame]

Thanks for the answer, regarding Joins. Faced today an issue with JOIN query

scany: rows contain a duplicate column 'id'

SELECT 
                o.id, vi.id
 FROM table1 AS o LEFT JOIN table2 AS vi ON o.vi_id = vi.id WHERE o.id = $1

I feel that this is regular case to select ids with the same field name from different tables

[georgysavva]

Hi! The problem here is that the database translates "o.id" and "vi.id" to the same column name "id" and you need to explicitly alias them to make it work with scany:

SELECT o.id AS "o.id", vi.id AS "vi.id" FROM table1 AS o LEFT JOIN table2 AS vi ON o.vi_id = vi.id WHERE o.id = $1

[Metalscreame]

I see. So in this case how will structures look like?

type Table1 struct {
    ID string `db:"id"
}
type Table2 struct {
    ID string `db:"id"
}

or

type Table1 struct {
    ID string `db:"o.id"
}
type Table2 struct {
    ID string `db:"vi.id"
}

2nd approach works definatelly, but i wonder if first will work :)

[georgysavva]

Depends on how you combine those to structs into a single one that you pass to scany. You can do it like that:

type Table1 struct {
    ID string `db:"id"
}
type Table2 struct {
    ID string `db:"id"`
}
type Result struct {
    O *Table1 `db:"o"`
    VI *Table2 `db:"vi"`
}

[Metalscreame]

Oh, thanks, this is a really nice example. Didn't expect it will work this way

[georgysavva]

Oh, thanks, this is a really nice example. Didn't expect it will work this way

@Metalscreame Check out this doc section for more details: https://pkg.go.dev/github.com/georgysavva/scany@v0.2.8/dbscan#hdr-Reusing_structs