Thread Details
  • State Not Answered
  • Replies 6 replies
  • Subscribers 373 subscribers
  • Views 1733 views
  • Users 0 members are here
Forum Actions
Related

SugarQuery N-Deep Join Issue

Hirak Chattopadhyay

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)

Parents Reply Children
  • 0 in reply to Marc Klenotic

    From personal experience no. What we found a few years ago was that it seems to work if you give the join your going to relatedJoin onto an alias of its table name then it works. So if in your instance you give your contacts join an alias of 'contacts' like the below using your given example.

    $query = new SugarQuery();
$query->from(BeanFactory::getBean('Accounts'));
$query->join('contacts', ['alias' => 'contacts']);
$opp = $query->join('opportunities', ['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();

    You could also give your opportunities join an alias as well but it isn't really much different to using the joinName() method. Hope that helps.