QRM of nested query with "double" join on the same table

[emarj]

Hi there,

First of all, thanks for this library, it is very useful. I am trying to use jet to rewrite my store layer and I am encountering some difficulties with a nested double join. Some help would be great.

TL;DR: I noticed that this post is quite long. I try to briefly pinpoint the issue. I learned how to use aliases to map joins to nested structures. The issue I am having is that I am joining two tables (account and entity) twice and I struggle to fill the inner most structures with the appropriate information. In some sense I am looking for a way to nest aliases.

I have this schema:

CREATE TABLE entity (
	id INTEGER,
	name TEXT NOT NULL,
	PRIMARY KEY(id)
);
CREATE TABLE account (
	id INTEGER,
	name TEXT NOT NULL,
	owner_id INTEGER NOT NULL,
	PRIMARY KEY(id),
);
CREATE TABLE "transaction" (
	id INTEGER,
	from_id INTEGER NOT NULL,
	to_id INTEGER NOT NULL,
	amount INTEGER NOT NULL,
	PRIMARY KEY(id)
);

type Entity struct {
	ID       null.Int `sql:"primary_key"`
	Name     string   
}

type Account struct {
	ID          null.Int `sql:"primary_key"`
	Name        string   
	Owner       Entity  
}

type Transaction struct {
	ID        null.Int        `sql:"primary_key"`
	From      Account         `alias:"From"`
	To        Account         `alias:"To"`
	Amount    decimal.Decimal
}

To extract all transactions for a certain entity I use the following query:

From := j.Account.AS("from")
To := j.Account.AS("to")

FromEntity := j.Entity.AS("from_owner")
ToEntity := j.Entity.AS("to_owner")

stmt := jet.SELECT(
		Transaction.AllColumns,
		From.AllColumns,
		To.AllColumns,
		FromEntity.AllColumns,
		ToEntity.AllColumns,
	).FROM(
		Transaction.INNER_JOIN(
			From,
			From.ID.EQ(Transaction.FromID),
		).INNER_JOIN(
			To,
			To.ID.EQ(Transaction.ToID),
		).INNER_JOIN(
			FromEntity,
			FromEntity.ID.EQ(From.OwnerID),
		).INNER_JOIN(
			ToEntity,
			ToEntity.ID.EQ(To.OwnerID),
		),
	).WHERE(
		FromEntity.ID.EQ(jet.Int(int64(eID))).OR(ToEntity.ID.EQ(jet.Int(int64(eID)))),
	)


list := []Transaction{}

err := stmt.Query(db, &list)

The query works as expected but the Owner fields of the From and To fields are not populated. To solve this, in similar cases, I would add an alias to the field Account.Owner like:

type Account struct {
	ID          null.Int `sql:"primary_key"`
	Name        string   
	Owner       Entity   `alias:"from_owner"` //but also `alias:"to_owner"` ?
}

This works, but of course it fills both owners as the "from" owner. I should also add a second alias to_id, but I should also specify that those aliases should be used in conjunction with aliases of parents.
Ideally i would write something like

From := j.Account.AS("from")
To := j.Account.AS("to")

FromEntity := j.Entity.AS("from.owner")
ToEntity := j.Entity.AS("to.owner")

with the part before the dot referring to the parent alias.
And then simply write:

type Account struct {
	ID          null.Int `sql:"primary_key"`
	Name        string  
	Owner       Entity   `alias:"owner"`
}

Q: How should I modify the aliases and/or the model to have both Owner fields filled with the correct information?

Thanks a lot. I've been banging the head against the wall for a while now.

[houten11]

Hi,

I don't see any other way than to create two Account structs:

type Entity struct {
	ID   null.Int `sql:"primary_key"`
	Name string
}

type ToAccount struct {
	ID    null.Int `sql:"primary_key"`
	Name  string
	Owner Entity `alias:"to_owner"`
}

type FromAccount struct {
	ID    null.Int `sql:"primary_key"`
	Name  string
	Owner Entity `alias:"from_owner"`
}

type Transaction struct {
	ID     null.Int    `sql:"primary_key"`
	From   ToAccount   `alias:"From"`
	To     FromAccount `alias:"To"`
	Amount decimal.Decimal
}

[emarj]

Thanks for your reply.

It will be quite inconvenient to have a duplicate type. Of course I could find a way to use them only for database interrogation and then casting them into a single type again. But this is less than ideal.

[go-jet]

Hi @emarj ,
Yeah, two structs is probably the only way to go.
Conjunction of parent alias seems like an interesting idea. Something to think about.

[emarj]

Hi @go-jet,
Would be nice to be able to use the point "." in table aliases for this purpose. I would like to experiment with such a patch, but unfortunately I got lost pretty quickly in the "reflective" code (I am not that skilled with reflection). Could you briefly point me to the parts that should be modified to obtain such a thing? I am not asking for details but just a rough idea from someone that knows the code. Thanks

[go-jet]

Sure, take a look at getTypeInfo method. This method is responsible for figuring out the mapping between struct fields and result set column index.