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)