SugarQuery identifier for union Sugar 7.x

Question

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  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

Gerardo Garcia Lima · Accepted Answer

I finally solved by adding the fieldRaw method. The code will end up like this: <?php

$leadBean = BeanFactory::newBean('Leads');
$contactBean = BeanFactory::newBean('Contacts');
$sql = new SugarQuery();
$sql->from($leadBean);
$sql->select(array('id', 'first_name', 'last_name', array('Leads', 'tipo')));
$sql->select->fieldRaw("'Leads'", 'type');
$sql->Where()
->equals('first_name', $persona["Nombre"])
->equals('last_name', $persona["Paterno"] . " " . $persona["Materno"]);
$sql2 = new SugarQuery();
$sql2->from($contactBean);
$sql2->select(array('id', 'first_name', 'last_name', array('Contacts', 'tipo')));
$sql2->select->fieldRaw("'Contacts'", 'type');
$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();
 Also note that i moved the from method before the select. I dont know why but if it were after the select method it wouldn't work.