AnsweredAssumed Answered

SugarQuery N-Deep Join Issue

Question asked by Hirak Chattopadhyay on Jul 6, 2016
Latest reply on Nov 22, 2016 by Simon Pountney-Langham

I tried the N-Deep join from https://developer.sugarcrm.com/2014/04/22/sugarcrm-cookbook-sugarquery-the-basics/

but it seems to be not working. did anything changed in Sugar 7.7

 

$query = new SugarQuery();
$query->from(BeanFactory::getBean('Accounts'));
$contacts = $query->join('contacts')->joinName();
$opp = $query->join('opportunities', array('relatedJoin' => $contacts))->joinName();
$query->select(array("$contacts.full_name", "$opp.name"));
$query->where()->equals('industry','media');
$query->where()->equals("$opp.sales_stage", 'closed');
$results = $query->execute();

 

Here is the query I got, which seems to have join issue. Any idea ?

 

SELECT 
    jt0_contacts.salutation rel_full_name_salutation,
    jt0_contacts.first_name rel_full_name_first_name,
    jt0_contacts.last_name rel_full_name_last_name,
    jt2_jt0_contacts_opportunities.name name
FROM
    accounts
        INNER JOIN
    (SELECT 
        tst.team_set_id
    FROM
        team_sets_teams tst
    INNER JOIN team_memberships team_memberships ON tst.team_id = team_memberships.team_id
        AND team_memberships.user_id = ''
        AND team_memberships.deleted = 0
    GROUP BY tst.team_set_id) accounts_tf ON accounts_tf.team_set_id = accounts.team_set_id
        INNER JOIN
    accounts_contacts jt1_accounts_contacts ON (accounts.id = jt1_accounts_contacts.account_id
        AND jt1_accounts_contacts.deleted = 0)
        INNER JOIN
    contacts jt0_contacts ON (jt0_contacts.id = jt1_accounts_contacts.contact_id
        AND jt0_contacts.deleted = 0
        AND (jt0_contacts.team_set_id IN (SELECT 
            tst.team_set_id
        FROM
            team_sets_teams tst
                INNER JOIN
            team_memberships team_membershipsjt0_contacts ON tst.team_id = team_membershipsjt0_contacts.team_id
                AND team_membershipsjt0_contacts.user_id = ''
                AND team_membershipsjt0_contacts.deleted = 0)))
        INNER JOIN
    opportunities_contacts jt3_opportunities_contacts ON (jt3_opportunities_contacts.deleted = 0)
        INNER JOIN
    opportunities jt2_jt0_contacts_opportunities ON (jt2_jt0_contacts_opportunities.id = jt3_opportunities_contacts.opportunity_id
        AND jt2_jt0_contacts_opportunities.deleted = 0
        AND (jt2_jt0_contacts_opportunities.team_set_id IN (SELECT 
            tst.team_set_id
        FROM
            team_sets_teams tst
                INNER JOIN
            team_memberships team_membershipsjt2_jt0_contacts_opportunities ON tst.team_id = team_membershipsjt2_jt0_contacts_opportunities.team_id
                AND team_membershipsjt2_jt0_contacts_opportunities.user_id = ''
                AND team_membershipsjt2_jt0_contacts_opportunities.deleted = 0)))
WHERE
    accounts.deleted = 0
        AND accounts.industry = 'media'
        AND jt2_jt0_contacts_opportunities.sales_stage = 'closed'

 

This join wrong:

opportunities_contacts jt3_opportunities_contacts ON (jt3_opportunities_contacts.deleted = 0)

Outcomes