Attempting to join a table on compound key match

ztaylor1990

Attempting to set up a series of joins with one of them matching on multiple columns, as the key for the primary table as well as the one joined have multiple entries for just a column like ID, and it's only the combination of name and id that make a row unique.
Essentially I want the SQL to end up something like

"SELECT ... FROM table1
LEFT JOIN table2 on table1.col1 = table2.col1 AND table1.carrier_name = table2.carrier_name"

I had tried something along the lines of

$editor->leftJoin( 'table2', 'table2.col1', '=', 'table1.col1', 'AND', 'table2.carrier_name', '=', 'table1.carrier_name' );

But this didn't work, as I'm assuming the function for join isn't meant for this use case.
Additionally, mjoin didn't seem to work as the primary and secondary tables both use the combination of id and carrier name as a compound key.

Using this code:

$editor->join(
        Mjoin::inst( 'rate' )
            ->link( 'table2.col1', 'table1.col1' )
            ->link( 'table2.carrier_name', 'table1.carrier_name' )
            ->fields(
                Field::inst( 'table1.rate * table1.miles as total_rate' )
            )
    );

Left an error on load stating "MJoin is not currently supported with a compound primary key for the main table."

Just a little lost as to how to do this join. The field itself doesn't even need to be editable, it just needs to be one I can view in the dt data object on the front end for some conditional formatting and I'm not sure if there's a better way to do this or some documentation that I've overlooked. Any help would be much appreciated.

allan

Hi,

At the moment your best bet is probably to use a VIEW which will do the join for you, as Editor's leftJoin currently does not support anything other than a simple single condition.

That said, there was a workaround discussed in this thread which might be of some interest.

We do have a feature request in our tracker to add this ability. I'm afraid we've not got to it yet though.

Allan