Query between two different databases postgresql cakephp

0

I have two distinct database clients and tickets .

Where in the clients_tickets table of the clients database a configuration is stored that I need to access certain values in tickets . So I tried to use construct of cakephp to connect to one bank and another, for example:

  • is initially connected to the clients database

' $ this-> ClientTicket ... is from the clients database, but when connecting to the tickets database, I lose access to this table, hence I do not know how to proceed with the method find of cakephp , to display the data I want, does anyone know how to proceed?

$this->loadModel('ClienteTicket');

$cliente_tickets = $this->ClienteTicket->find('all');

//Altera o banco de dados para o Tickets
$this->Cliente->construct('tickets');

$this->loadModel('Project');
$this->loadModel('Tracker');
$this->loadModel('Issue');
$this->loadModel('CustomField');
$this->loadModel('CustomFieldsProject');
$this->loadModel('CustomValue');

$tickets = $this->ClienteTicket->find('all', array(
        'fields' => array('Issue.id', 'Tracker.name', 'Issue.subject',
            'Issue.created_on', 'IssueStatuse.name', 'CustomValue.value'),
        'joins' => array(
            array('table' => 'custom_fields',
                'alias' => 'CustomField',
                'type' => 'LEFT',
                'conditions' => [
                    'ClienteTicket.cf_id = CustomField.id',
                ],
            ),
            array('table' => 'custom_values',
                'alias' => 'CustomValue',
                'type' => 'LEFT',
                'conditions' => [
                    'CustomValue.custom_field_id = CustomField.id',
                ],
            ),
            array('table' => 'custom_fields_trackers',
                'alias' => 'CustomFieldsTracker',
                'type' => 'LEFT',
                'conditions' => [
                    'CustomFieldsTracker.custom_field_id = CustomField.id',
                ],
            ),
            array('table' => 'trackers',
                'alias' => 'Tracker',
                'type' => 'LEFT',
                'conditions' => [
                    'CustomFieldsTracker.tracker_id = Tracker.id',
                ],
            ),
            array('table' => 'custom_fields_projects',
                'alias' => 'CustomFieldsProject',
                'type' => 'LEFT',
                'conditions' => [
                    'CustomFieldsProject.custom_field_id = CustomField.id',
                ],
            ),
            array('table' => 'projects',
                'alias' => 'Project',
                'type' => 'LEFT',
                'conditions' => [
                    'CustomFieldsProject.project_id = Project.id',
                ],
            ),
            array('table' => 'issues',
                'alias' => 'Issue',
                'type' => 'LEFT',
                'conditions' => [
                    'CustomValue.customized_id = Issue.id',
                ]),
            array('table' => 'issue_statuses',
                'alias' => 'IssueStatuse',
                'type' => 'LEFT',
                'conditions' => [
                    'Issue.status_id = IssueStatuse.id',
                ],
            ),
        ),
        'conditions' => [
            'CustomValue.value like \'' . $cod_cliente . '\''
        ],
        'group' => 'Issue.id, Tracker.name, Issue.subject,
        Issue.created_on, IssueStatuse.name, CustomValue.value',
        'limit' => 5,
        'order' => ['Issue.id' => 'DESC']
    ));
    
asked by anonymous 20.03.2017 / 14:31

1 answer

0

First, I stored in a variable the information of the client database tables that were relevant for me to use in tickets .

After I connected to tickets . $this->Cliente->construct('tickets');

After I used it:

$tickets = $this->Cliente->query($SQL_Tickets);

//Voltei para o banco anterior
$this->Cliente->construct('default');
    
20.03.2017 / 20:05