AnsweredAssumed Answered

SugarQuery identifier for union Sugar 7.x

Question asked by Gerardo Garcia Lima on Oct 22, 2015
Latest reply on Oct 23, 2015 by Gerardo Garcia Lima
Hi everyone!,

I need help with a query. I m doing a UNION but i need to know from which module im getting the result.

$leadBean       = BeanFactory::newBean('Leads');          $contactBean    = BeanFactory::newBean('Contacts');            $sql = new SugarQuery();          $sql->select(array('id', 'first_name', 'last_name', array('Leads', 'tipo')));          $sql->from($leadBean);          $sql->Where()              ->equals('first_name', $persona["Nombre"])              ->equals('last_name', $persona["Paterno"] . " " . $persona["Materno"]);            $sql2 = new SugarQuery();          $sql2->select(array('id', 'first_name', 'last_name', array('Contacts', 'tipo')));          $sql2->from($contactBean);          $sql2->Where()               ->equals('first_name', $persona["Nombre"])               ->equals('last_name', $persona["Paterno"] . " " . $persona["Materno"]);            $sqUnion = new SugarQuery();          $sqUnion->union($sql);          $sqUnion->union($sql2);          $result = $sqUnion->execute();

in a sql query for this i would resolve it like this.

(SELECT id, '1' as 'type'  FROM leads LEFT JOIN leads_cstm ON leads_cstm.id_c =  WHERE leads.deleted = 0 AND leads.first_name = 'ASDFASDF' AND leads.last_name = 'ASDASDFASDF') UNION ALL (SELECT id, '2' as 'type' FROM contacts LEFT JOIN contacts_cstm ON contacts_cstm.id_c =  WHERE contacts.deleted = 0 AND contacts.first_name = 'ASDFASDF' AND contacts.last_name = 'ASDASDFASDF');

That way it returns a new colum with the number 1 for leads and number 2 for contacts.

I just want to know how i can do this, or another way to identify where the data comes from, contacts or leads