many2many table with extra field

[julienkosinski]

I would need to specify a custom extra field to my many2many join table.

What I have in mind is the ability to specify a struct containing all the fields of the join table. This setting would throw an error if the struct does not contain mandatory taged fields.

Thanks!

[elliotcourant]

Would you be able to provide a simple code example of what you're trying right now and how you would like it to behave?

[thecampagnards]

Hello, I think I am in the same situation

Here an example:

I have a many2many table with an extra column, like this:

CREATE TABLE "a" (
    "id" bigserial primary key NOT NULL
);

CREATE TABLE "b" (
    "id" bigserial primary key NOT NULL
);

CREATE TABLE "a_to_b" (
    "a_id" bigint NOT NULL references "a"("id"),
    "b_id" bigint NOT NULL references "b"("id"),
    "extra" bigint NOT NULL
);

I'm trying to Select it via query Relation + ColumnExpr:

// db.go
type A struct {
	tableName struct{} `sql:"a"` // nolint: structcheck, unused
	ID int64           `sql:"id"`
}

type B struct {
	tableName struct{} `sql:"b"` // nolint: structcheck, unused
	ID int64           `sql:"id"`
}

// main.go
type A struct {
	db.A `pg:",inherit"`
	Bs []*db.B `pg:"many2many:a_to_b"`
}

type B struct {
	db.B `pg:",inherit"`
	Extra int64
}

func main(){
    // ...
    var As []*A
    query := dbh.Model(&As).
    Relation("Bs").
    ColumnExpr("a_to_b.extra AS b.extra") // doesnt work

    count, err := query.SelectAndCountEstimate(100) // fail
}

I have this error:

ERROR #42601 syntax error at or near "."

If I switch the . with __, I have this error:

missing FROM-clause entry for table "a_to_b"

I feel I'm doing it wrong but I haven't found any place on how to do Thanks !