Sugar query syntax error in SugarCRM

Question

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

$queryProspects = new SugarQuery();
$queryProspects->from(BeanFactory::getBean('Prospects'));
$leads = $queryProspects->joinTable('leads');
$queryProspects->select("prospects.id","prospects.lead_id");
$queryProspects->where()->equals("lead_id","117c3d5d-07d9-0ae7-5610-573ac87c9a35");

Before executing it like this.

$queryProspects->execute();

I am compiling my query like

$queryProspects->compileSql();

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 1

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',
    ),