mirza umair mirza umair - 1 year ago 126
SQL Question

Sugar query syntax error in SugarCRM

I am trying to join table prospects with leads. I am executing this query

$queryProspects = new SugarQuery();
$leads = $queryProspects->joinTable('leads');

Before executing it like this.


I am compiling my query like


This query is not working after executing. query result after compiling is

SELECT prospects.id id, prospects.lead_id lead_id FROM prospects JOIN leads ON () WHERE prospects.deleted = 0 AND prospects.lead_id = '117c3d5d-07d9-0ae7-5610-573ac87c9a35'

I know the error is () WHERE which I need to remove, but unable to do changes in sugar query in order to remove these brackets and where clause (which are showing in sql generated query).

Answer Source

Change from and join statement like this.

$queryProspects->from(BeanFactory::getBean('Prospects'), array('team_security' => false));

$leads = $queryProspects->join('lead')->joinName();

lead in join is the link (name field) in your prospects > vardefs.php as shown below.

 'lead' => array(
        'name' => 'lead',
        'type' => 'link',
        'relationship' => 'lead_prospect',
        'module' => 'Leads',
        'source' => 'non-db',
        'vname' => 'LBL_LEAD',