On March 26, 2019 we launched new MODX Forums. Please join us at the new MODX Community Forums.
Subscribe: RSS
  • 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
      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,
                          ));
      • Thanks for reporting back on that. smiley
          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
        • Quote from: nick2687 at Feb 14, 2019, 04:39 PM
          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. smiley
          Regards, mobdro