left join and non id join in agiletoolkit atk4

Question

SELECT t1.field1, t1.field2, t1.field3, t2.field4, t2.field5,t2.field6 
FROM table1 AS t1 LEFT JOIN table2 AS t2 
ON t1.field1 = t2.field1 AND t1.field2 = t2.field2
WHERE t1.fieldstart <='2021-10-13' AND t1.fieldend >= '2021-10-31' OR t2.submitted_by='xyz';

I want convert the query above to agile toolkit akt4 code , any idea on how to do the left join in agiletoolkit, every time i use join it give me inner join , I want left join

$j_table = $m->join('table2.nameid');           

I have tried this it looks fine

$j_table = $m->leftjoin('table2.nameid');     

How ever when I try to join with none id I get this error

$j_table = $m->leftjoin('table2.name','name');     

Fatal Error atk4\data\Exception: You are trying to link tables on non-id fields. This is not implemented yet

looking for help please ?

Thanks

Answer 1

On Agile Toolkit v1.7.1 (legacy/v1 branch) only option how to do this is on underlying DSQL level.

To get DSQL instance from model use:

$model->table_alias = 't1';
$q = $model->dsql();

DSQL sample code below:

// You can use expression for more complex joins
$q->join('table2',
    $q->andExpr() // or orExpr()
        ->where('t1.field1=t2.field1')
        ->where('t1.field1=t2.field2'),
    'left',
    't2'
)

Bad thing about this approach is that as soon as you "move" from data model level to lower, DSQL level then you're kind of locked in there and can't use all nice features of data model because it's not aware of joins defined in dsql. At least that's how I remember that. A lot has changed in toolkit in last few years...

---

EDIT: Oh, actually looks like if found how it was possible to do with Model alone back then.

Try this:

$model->join('table2',
  [
    'foreign_alias' => 't2',
    'on' => $expr,
  ],
  'left'
);

where $expr can be simply a string like

$expr = 't1.field1=t2.field1 AND t1.field2=t2.field2';

but definitely preferred way is to use DSQL Expression object like

// which is functionally the same as above string approach
$expr = $model->expr('t1.field1=t2.field1 AND t1.field2=t2.field2');

// this is much better because takes care of escaping and table alias automatically
$expr = $model->expr('{}={} AND {}={}', [
    $model->getField('field1'),
    't2.field1',
    $model->getField('field2'),
    't2.field2',
]);

P.S. All code above is untested. If you find any error, then please let me know in comments and I'll fix.