Question about using LEFT OUTER JOIN in query building

[eduacostam]

So... this might be a fairly easy question to answer, but I couldn't find a way of making it work.

I'm trying to do a simple query that needs a LEFT OUTER JOIN with another table, I'm trying to use the models.NewQuery method for building it and found that the only join type that's available in qm is InnerJoin, I looked around and found that the queries module has joinKind constants that do include other types of joins, but couldn't find how to use it with query building.

Example of what I imagine:

// Use query building
err := models.NewQuery(db,
  Select("pilots.id", "pilots.name", "jets.id", "jets.pilot_id", "jets.age", "jets.name", "jets.color"),
  From("pilots"),
  LeftOuterJoin("jets on jets.pilot_id = pilots.id"),
).Bind(&paj)

Is there any way of doing this right now, or do I have to use Raw querying?

Thanks!

[aarondl]

Wow. I was sure I had replied to this in my brain, sorry!! There is no way to do this right now through query building. Mainly the problem is that Bind() has no idea what to do with an entity that can suddenly be null.

Currently for an innerjoin it'll take something that looks like

type Fun struct {
   Entity1 Happy `boil:",bind"`
   Entity2 Power `boil:",bind"`
}

And if you give it an array of those Fun structs, it'll be able to bind each row to the right parts of both of those things. The problem with a left/right join is that now one of those entire entities can be null. Which means when you pass things to scan, they have to be nullable. If they're not nullable then scan will return an error. Considering the struct above, if Entity2 has a non-nullable integer as a field, scan will fail to put a null into it.

It's maybe possible that if the Entity2 is a pointer in the Fun we know that it's possible that it's null and can do... something? Really it all comes down to how can you handle the scan without changing the structs we generate to be completely nullable (for example using pointers instead of null types, or what have you). The only way I can think to do that is to Scan into some temporary space, and then copy into the struct if it's not nil. Creating the temporary space using reflection is probably possible, we could maybe have a third piece of the struct tag to indicate nullability?

These are my ideas around the subject. Very very sorry I hadn't replied to this earlier.

[eduacostam]

@aarondl I'm also sorry I didn't get back to you sooner!!

I get what you are saying, and it makes sense, it's a tradeoff between having a struct that really maps to what we have in the database (in terms of nullable columns) or having something that has all it's fields null so that we can have this case working.

I think that your proposal might be the best thing, maybe having something as the struct you propose, being made of pointers, so that they can be null is a solution, but also I suppose that implementing that would not be as easy as doing a raw query myself haha.

Anyway, thanks for the answer! I'm still learning Go and this kind of though process always help ;)

[aarondl]

@eduacostam Well, in addition to that you can see there's a linked issue here that would actually solve this as a byproduct which is exciting. I've yet to test if that's usable but it might be! So let's hang on on this one.

[payaaam]

Are there any plans to incorporate LeftOuterJoin into the library now that #122 is closed?

[aarondl]

Unfortunately it's still not enough. The code in #122 can only deal with a column that is returned by the query but doesn't exist on the struct. In order to do a left join properly, every column needs to be read into an interface{} and each one has to be checked for nil before it's then bound to a struct. It's still possible to do this. Unless I'm missing something. Since the only way you can tell if something is missing is that the database returns NULL for each column for that table.

[ceshihao]

@aarondl Do you have any plan on this issue?

[aarondl]

Wasn't planning on it currently, no.

[emwalker]

Thought I would add a use case for a qm.LeftJoin for future consideration — influencing ordering with one or more fields on a left-joined row:

mods := []qm.QueryMod{
    qm.Load("ParentTopics"),
    qm.Load("ParentTopics.TopicTimelines"),
    qm.Load("TopicTimelines"),
    qm.InnerJoin("repositories r on topics.repository_id = r.id"),
    qm.OrderBy("topic_timelines.starts_at is null, topic_timelines.starts_at, topics.name"),
}

[aarondl]

Honestly, it's probably fine to include left/right/full outer join.

My new reasoning is this: Inner join doesn't work out of the box either. In a 100% vanilla query with query mods and no custom select, you get table.* as your select, and so the null columns won't even matter unless you rewrite the select to include the null columns from the other table which will then break on .Bind(). In sqlboiler doing inner joins and retrieving the data from the joined table is still an "advanced" technique in that you have to rename the selects very particularly and have a custom struct & bind to it. So it's probably fine that we impose the requirement of dealing with null on to the caller in the case of all the other join types, the major downside being you can't use your existing models with it.

I'd accept a PR to add the following query mods:

• LeftOuterJoin
• RightOuterJoin
• FullOuterJoin

It seems all three (psql, mysql, mssql) all support these.

[amanjain97]

@aarondl Is there any current workaround for this or any plan to fix it ?

[tzachshabtay]

@amanjain97 support was added in this MR: https://github.com/volatiletech/sqlboiler/pull/686, i.e you can do qm.LeftOuterJoin("jets as j on j.pilot_id = pilots.id") for example