Joining a table on multiple criteria

[ccmpj]

I am re-working a query where I need to specify 2 criteria in the ON clause for a LEFT JOIN, but can't quite figure it out with Bob.

This is for MySQL and the join clause would look like this:

SELECT `Table1`.`id` FROM `Table1`
LEFT JOIN `Table2` ON `Table2`.`t1_id` = `Table1`.`id` AND `Table2`.`other_field` = 1

With just a single clause I can do

sm.LeftJoin(
    mysql.Quote("Table2").OnEQ(
        mysql.Quote("Table2", "t1_id"), 
        mysql.Quote("Table1", "id"),
    ),
)

For multiple criteria, I assume I should just use .On(), but I get square brackets in the output which is not legal, so suggests I am doing something incorrect, e.g.

sm.LeftJoin(
    mysql.Quote("Table2").On(
    mysql.Quote("Table2", "t1_id").EQ(mysql.Quote("Table1", "id").AND(
        mysql.Quote("Table2", "other_field").EQ(mysql.Arg(somValue)),
    ),
)

The output I end up with is

LEFT JOIN `Table2` ON [((`Table2`.`t1_id` = `Table1`.`id`) AND (`Table2`.`other_field` = ?))]

Without the square brackets, the query would work. What am I missing?

[stephenafamo]

Your query should work. This looks like a bug. I'll check and fix.

[ccmpj]

To be fair I’m not on the latest version due to the X starter, but I’m in the process of phasing that out so I can upgrade. If it is a bug, chances are you may have already fixed it

[ccmpj]

Wondering if it was fixed in 0.19.1. I should be in a position to upgrade tomorrow morning and will let you know.

[stephenafamo]

Yes, I'm pretty certain it was fixed in 0.19.1. Let me know if upgrading solves your issues.

PS: Some pretty significant breaking changes will be in the next version too, apologies in advance for any upgrade pain 🙈

[ccmpj]

It was indeed. I have upgraded to the latest version and the issue is no longer present. My fault for not tracking. I've removed the need for X and using Raw in some cases, but others X is no longer necessary, but that is a separate story.

I'll make sure I read the change log for the next version 😉

Thanks for the hard work on this.