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 leads.id id, '1' as 'type' FROM leads LEFT JOIN leads_cstm ON leads_cstm.id_c = leads.id WHERE leads.deleted = 0 AND leads.first_name = 'ASDFASDF' AND leads.last_name = 'ASDASDFASDF') UNION ALL (SELECT contacts.id id, '2' as 'type' FROM contacts LEFT JOIN contacts_cstm ON contacts_cstm.id_c = contacts.id 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