Eduardo Preciado

SugarQuery using deep joins

Discussion created by Eduardo Preciado on Oct 18, 2018
Latest reply on Oct 19, 2018 by Eduardo Preciado

I have a few questions regarding joins 

$moduleABean = BeanFactory::getBean($moduleA,$id);

$options = $array(
   //'alias' => 'foo', so this is basically another name for the table
// so you dont have to use a moduleCTable you can just use foo.blah  
   'relatedJoin' => 'link' // does this relate from b to c or is it just the
// table B 
)
// the end goal for this query is to get 
// all the Cs that are related to Bs
// which are related to As
// if I could combine it to add on
// get all Cs related to As directly that would be cool too
// could I in the end just orderby and it will order both
$sugarQuery = new SugarQuery();
$sugarQuery->from('moduleABean');
// this returns the table name right ?
$moduleBTable = $sugarQuery
->join('linkAtoB')->joinName();
//deep join
$moduleCTable = $sugarQuery->join('linkBtoC',array('relatedJoin', $moduleBTable))
  ->joinName();
$sugarQuery->where()->queryOr()
  ->equals('$moduleCTable.parent_id', $moduleBTable.id)
  ->equals('$moduleCTable.parent_id', $moduleABean->id);

$sugarQuery->orderBy('$moduleCTable.date',DESC);
$results = $sugarQuery->execute();

What am I doing wrong is this even possible. I do this and many other attempts and I am just returned an empty array even though I know there is Cs for both A and B. 

Outcomes