innerJoin with foreign database

41 Posts

Nick2687 Reply #1, 7 years, 5 months ago

Hi everyone,

I'm working on moving some custom database tables into a foreign db instead of them being in the same database as my modx instance. So currently I have a copy of these tables in both places (locally and in the foreign db).

The problem i'm running into is that when I am trying to query data from the foreign db, innerJoins no longer seem to work as expected. Take the below code for example:

 
if($modx->getOption('use_foreign_database')) {
// $database_dsn, $foreign_database_user, $foreign_database_password variables are set here
    $db = new xPDO($database_dsn, $foreign_database_user, $foreign_database_password); 
    // Test connection
    $connected = ($xpdo->connect()) ? 'Connected' : 'Not Connected';
    $modx->log(xPDO::LOG_LEVEL_DEBUG, $connected);   
}

else {
    $db = $modx;
}
$package_path = MODX_CORE_PATH . 'components/dancers/model/';
$db->addPackage('dancers',$package_path,'ods_');


$query = $db->newQuery('Payroll');
    $query->innerJoin('PayrollPayPeriod','PayrollPayPeriod',array("Payroll.pay_period_id = PayrollPayPeriod.id"));
    $query->groupBy('employee_id');
    $query->select(array('Payroll.id, employee_id, sum(gross_income) as gross_income, PayrollPayPeriod.id, PayrollPayPeriod.pay_date'));
    $query->where(array(
        'PayrollPayPeriod.pay_date:>=' => date($year . '-' . $month . '-01'),
        'PayrollPayPeriod.pay_date:<=' => date($year . '-' . $month . '-t')
    ));
    $payrolls = $db->getCollection('Payroll', $query);

 foreach ($payrolls as $payroll) {

       $modx->log(xPDO::LOG_LEVEL_DEBUG, $payroll->get('employee_id'));
       $modx->log(xPDO::LOG_LEVEL_DEBUG, $payroll->get('PayrollPayPeriod.pay_date'));  
       $modx->log(xPDO::LOG_LEVEL_DEBUG, $payroll->get('gross_income'));  
}

If $modx->getOption('use_foreign_database') is false, than the query will return the results as I expect and the 3 objects within the foreach will log correctly to the error log, however if $modx->getOption('use_foreign_database') is true (meaning it's pointing to the foreign db), than it will still query the Payroll db correctly and the employee_id will print to the error log, but the other two objects (the sum'd gross_income) and the PayrollPayPeriod.pay_date print out empty statements.

I'm not sure what i'm missing here as the package schema and table relationships in that schema have not changed, the only changing is if I'm connecting to the local db, vs the foreign...

This question has been answered by nick2687. See the first response.

discuss.answer

41 Posts

Nick2687 Reply #2, 7 years, 5 months ago

Found some help in the slack community I was not hydrating the fields when connecting to the remote database, adding the below array fixed my issue.

$db = new xPDO($database_dsn, $foreign_database_user, $foreign_database_password, array (
                        XPDO::OPT_HYDRATE_FIELDS => true,
                        XPDO::OPT_HYDRATE_RELATED_OBJECTS => true,
                    ));

24,544 Posts

BobRay Reply #3, 7 years, 5 months ago

Thanks for reporting back on that.

Did I help you? Buy me a beer
Get my Book: MODX:The Official Guide
MODX info for everyone: http://bobsguides.com/modx.html
My MODX Extras
Bob's Guides is now hosted at A2 MODX Hosting

1 Posts

joegellen20 Reply #4, 7 years, 5 months ago

Quote from: nick2687 at Feb 14, 2019, 10:39 AM

Found some help in the slack community I was not hydrating the fields when connecting to the remote database, adding the below array fixed my issue.
$db = new xPDO($database_dsn, $foreign_database_user, $foreign_database_password, array (
                        XPDO::OPT_HYDRATE_FIELDS => true,
                        XPDO::OPT_HYDRATE_RELATED_OBJECTS => true,
                    ));

Thank you for the updating this thread.

Regards, mobdro

Answered innerJoin with foreign database