vendredi 9 février 2018

Laravel query builder join using either one of two conditions

I have a complex query that I want to use either Query Builder or Eloquent (preferred) but I'm struggling with an inner join.

The inner join needs to be one of either of 2 conditions so if one fails, the other is used.

This is my original query

SELECT DISTINCT tableA.crmid, tableB.* 
FROM tableB
INNER JOIN tableA ON tableA.crmid = tableB.customaccountsid
INNER JOIN tableC ON (tableC.relcrmid = tableA.crmid OR tableC.crmid = tableA.crmid)
WHERE tableA.deleted = 0 AND tableC.relcrmid = 123 AND tableC.relation_id = 186

This is my attempt at using Query Builder and I know where the problem lies. It's where I join tableC. I don't know how to use my condition there

DB::table('tableB')
    ->join('tableA', 'tableA.crmid', '=', 'tableB.customaccountsid')
    ->join('tableC', function($join) {
        $join->on(DB::raw('(tableC.relcrmid = tableA.crmid OR tableC.crmid = tableA.crmid)'));
    })
    ->where('tableA.deleted', 0)
    ->where('tableC.relcrmid', 3727)
    ->where('tableC.relation_id', 186)
    ->select('tableA.crmid', 'tableB.*')

Ant this is the output of the query when i output as SQL

SELECT `tableA`.`crmid`, `tableB`.* 
FROM `tableB` 
INNER JOIN `tableA` ON `tableA`.`crmid` = `tableB`.`customaccountsid` 
INNER JOIN `tableC` ON `tableC`.`relcrmid` = (tableC.relcrmid = tableA.crmid OR tableC.crmid = tableA.crmid) 
WHERE `tableA`.`deleted` = ? AND `tableC`.`relcrmid` = ? AND `tableC`.`relation_id` = ?



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire