Query between two different postgresql cakephp databases

Asked

Viewed 191 times

0

I have two separate databases customers and tickets.

Where on the table cliente_tickets of the database customers a configuration is stored from which I need to access certain values in the database tickets. So I tried to use the construct of cakephp to connect to one bank and another, example:

  • is initially connected to the database customers;

´ $this->Clienteticket ... is from the database customers, but when connecting to the database tickets, I lose access to this table, so I don’t know how to proceed with the method find of cakephp, to display the data I want, someone knows 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']
    ));
postgresql cakephp

  • In the bank that has the table of clientes i would create a view with the result of the tickets using the dblink. Copy data from one database to another postgres

  • The problem with doing this is that it would take too long to show the results, and there are tables with at least 3 million records :Z

1 answer

0


First, I stored the information from the database tables in a variable client which were pertinent for me to use at the bank tickets.

After I made the connection to the bank tickets. $this->Cliente->construct('tickets');

Logo stored in a variable $SQL_Tickets the SQL query that returned the values it needed, so where it needed the values of the tables in the database customers, I used the created variable.

After using:

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

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

Browser other questions tagged postgresql cakephp

You are not signed in. Login or sign up in order to post.