How to select columns in an eager load?

[mehdiym]

What version of SQLBoiler are you using (sqlboiler --version)?

SQLBoiler v3.4.0

Issue

Related: https://github.com/volatiletech/sqlboiler/issues/508

I would like to be able to select specific columns, not all, for a relation ship when eager loading it. In my case, I have users and friends, and in the generated code for LoadFriendFromUsers method there is a users.* that is not customizable:

    query := NewQuery(
        qm.Select("`users`.*, `a`.`friend_to_id`"),
        qm.From("`users`"),
        qm.InnerJoin("`friends` as `a` on `users`.`id` = `a`.`friend_from_id`"),
        qm.WhereIn("`a`.`friend_to_id` in ?", args...),
    )

So using qm.Load(model.UserRels.FriendFromUsers) always fetch all friends columns.

Of course I first tried adding a query mod:

qm.Load(model.UserRels.FriendFromUsers, qm.Select(columns...))

but I got the following error:

failed to execute a one query for users: failed to eager load FriendFromUsers: failed to scan eager loaded results for users: sql: expected 16 destination arguments in Scan, not 13

And then I realized that relationship columns were hard-coded.

Please provide a relevant database schema so we can replicate your issue (Provided you are comfortable sharing this)

CREATE TABLE users
(
    id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    created_at    DATETIME     NOT NULL,
    updated_at    DATETIME     NOT NULL,
    email         VARCHAR(100) NOT NULL UNIQUE,
    name          VARCHAR(100) NOT NULL,
    password      VARCHAR(255)  NOT NULL,
    INDEX (name)
) ENGINE = INNODB;

CREATE TABLE friends
(
    friend_from_id INT UNSIGNED NOT NULL,
    friend_to_id   INT UNSIGNED NOT NULL,
    CONSTRAINT `user_friends_fk` FOREIGN KEY (friend_from_id) REFERENCES users (id),
    CONSTRAINT `friend_friends_fk` FOREIGN KEY (friend_to_id) REFERENCES users (id),
    CONSTRAINT `friend_pk` PRIMARY KEY (friend_from_id, friend_to_id)
) ENGINE = INNODB;

I any case, thank you so much for this wonderful lib. I moved from gorm because of your idiomatic way of doing it and a better relationship management.

[aarondl]

Thanks for the kind words. I'll have to look into this bug.

[aarondl]

So I looked into this in detail and the problem is that we have an inherent issue in the way query mods work and the way we need a many-to-many to work.

In the many-to-many when you look up one side of the relationship, you don't get any of the relationship information back (because that's in the join table). Hence the inner join so that we can still maintain what it was related to. We need this information to come back from the database in order to correctly assign pointers to R in your model struct.

I did think of a way we could possibly use Bind to fix this. Bind allows us to bind whatever comes back to the query to a struct of course (in a bit more of a dynamic way). There's not much of a tradeoff but the big problem with this is that users will now have to remember to pass in the select for the id in the join table (as it does now):

qm.Load(model.UserRels.FriendFromUsers, qm.Select(append(columns, `a`.`friend_to_id`"`)))

This is a boundary where the magic starts to break down, and I'd be concerned that by fixing this we'd end up making sqlboiler worse in a sense that you have to remember to do this instead of it just not working at all. Personally I'd rather people have to resort to raw queries over this. What do you think?

[mehdiym]

Hello aarondl, I understand your analysis and I trust you about the possibilities. I'll try with a raw query then. Thank you for looking into that.

[andradei]

If this helps with making the issue clearer:

I tried something like:

s, err := models.Somethings(
  qm.Load(models.SomethingRels.Dad, qm.Select("name")),
  qm.Load(models.SomethingRels.Mom, qm.Select("name")),
  qm.Load(models.SomethingRels.Spouse, qm.Select("name")),
).All(c, exec)

And only the last qm.Load Select was honored. So s.R.Spouse.Name was populated, but s.R.Dad/Mom were nil. Now, s.Dad/Mom (notice no .R.) are or type null.Int because they are foreign keys. This is important (and weird) because they are correctly set, and that's the same result I see when debug mode is on and the SELECT statement for eager-loading Dad/Mom is printed to stderr (it doesn't show the result of the query, but the struct s.Dad/Mom, like I said, weird).

I tried reading the eager-loading source, but that was too complex and I would need more time to be able to help.

[aarondl]

@andradei Could you create me a new bug report for this rather than an add-on to this one? They don't seem related but it really depends on what kind of relationships are going on here.

[andradei]

@aarondl Done, feel free to delete this and the above comments if you want. Thanks.