Skip to main content

This site requires you to update your browser. Your browsing experience maybe affected by not having the most up to date version.

Data Model Questions /

Return DataList results from external database


Reply


5 Posts   1851 Views

Avatar
SnowB1

Community Member, 9 Posts

3 August 2012 at 8:08am

Hello all,

I am working on setting up an external login system for a group of sites that will use an external database. I have everything working on the front end of the site. The issue I am having is getting the list of members for the site into a section in the admin. I have the menu item created and the Member object, my issue is getting the results to show in the gridfield. I have returned the data in an ArrayList and I can export the values properly but they do not align with the $db fields. I then switched to the DataList and have had issued getting data in place. If I add a where clause I get an sql error

[User Error] Couldn't run query: SELECT DISTINCT count(DISTINCT "ExternalLoginMembers"."ID") AS "0" FROM "ExternalLoginMembers" WHERE ("RegisteredSite" = mysite) Unknown column 'RegisteredSite' in 'where clause'

I have my code for now below if anyone can help point me in the right direction. Thanks in advance.

/* Admin */
class ExternalLoginAdmin extends ModelAdmin {

   public static $managed_models = array('ExternalLoginMembers');
   static $url_segment = 'members';
   static $menu_title = 'Members';
   static $menu_priority = -1;

   /*
    * Get Member listing
    */
function getList() {

      $members = new externalLogin();
      
      $sitemem = $members->getSiteMembers($_SERVER['HTTP_HOST']);
      
      return $sitemem;
      
   }

}

/*Members*/
class ExternalLoginMembers extends DataObject {

   static $db = array(
      'Username' => 'Varchar',
      'FirstName' => 'Varchar',
      'LastName' => 'Varchar',
      'StreetAddress' => 'Varchar',
      'AptSuiteFloor' => 'Varchar',
      'City' => 'Varchar',
      'State' => 'Varchar',
      'Zip' => 'Varchar',
      'Gender' => 'Varchar',
      'DateOfBirth' => 'Date',
      'EmailAddress' => 'Varchar',
      'RegisteredDate' => 'Varchar'
   );
   /**/
   static $searchable_fields = array(
      'Username',
      'FirstName',
      'LastName',
      'Gender'
   );

   /**/
   static $summary_fields = array(
      'Username' => 'Username',
      'FirstName' => 'First Name',
      'LastName' => 'Last Name',
      'StreetAddress' => 'Street Address',
      'AptSuiteFloor' => 'Apt/Suite/Floor',
      'City' => 'City',
      'State' => 'State',
      'Zip' => 'Zip',
      'Gender' => 'Gender',
      'DateOfBirth' => 'Date Of Birth',
      'EmailAddress' => 'Email Address',
      'RegisteredDate' => 'Date Registered'
   );

/* My External Login Class function */
function getSiteMembers($site) {

      global $databaseConfig, $databaseConfig2;

      //USE OUR DB
      DB::connect($databaseConfig2);

      $dos = DataList::create('ExternalLoginMembers');
      
      //BACK TO SILVERSTRIPE DB
      DB::connect($databaseConfig);

      return $dos;
   }

Avatar
Willr

Forum Moderator, 5513 Posts

4 August 2012 at 6:46pm

("RegisteredSite" = mysite) Unknown column 'RegisteredSite' in 'where clause'

Where is registered site coming from? It's not a field listed in your ExternalLoginMembers definition. The ORM (like DataList::create()) should be limited to use with the current SilverStripe DB.

If you want to import from a remote DB use DB::query() directly to get your data, then create new records in the ORM or wrap the data as an ArrayData class.

Avatar
SnowB1

Community Member, 9 Posts

7 August 2012 at 1:04am

Edited: 07/08/2012 6:14am

Willr,

It is an external Database which I am pulling records from and I am able to get them using the DB::query. The only issue I have is how to get them into records in the ORM. I tried using

$myMembers = new ArrayList;

and I was able to use the export functionality, but in the listing area all I got was an ID field and an empty record. How would I go about this as I wasn't sure I could use the DataObject::get() on an external database and if I could what format I would need to return it in. I did try using the following code where $dos was the ArrayList:

$rec = $records->next();

while ($rec) {
$do = new DataObject($rec);
$dos->add($dos);
$rec = $records->next();
}

Thanks

Avatar
Willr

Forum Moderator, 5513 Posts

7 August 2012 at 8:07pm

Don't make the imported records DataObjects unless you want them copied to your database. Making them ArrayData objects will give you templating abilities without needing a database table.

If you want to encapsulate logically functions with the data then subclass ArrayData rather than DataObject.

while($rec = $records->next()) {
$myMembers->push(new ArrayData($rec));
}

Avatar
SnowB1

Community Member, 9 Posts

8 August 2012 at 2:00am

I tried adding in your code and I am gettting a [Warning] Missing argument 1 for ArrayData::__construct(). I made sure the $rec contained an associative array and I have also tried passing in my own array to test with and I get the same error. I posted the updated code below if you can see where I am going wrong I would appreciate it.

function getSiteMembers($site) {

global $databaseConfig, $databaseConfig2;
$myMembers = new ArrayList();

//USE OUR DB
DB::connect($databaseConfig2);

$records = DB::query("SELECT Username, FirstName, LastName, StreetAddress, AptSuiteFloor, City, State, Zip, Gender, DateOfBirth, EmailAddress, RegisteredDate FROM " . $this->member_table . " WHERE Approved = 1 and RegisteredSite = '" . $site . "'");

while($rec = $records->next()) {

//$myMembers->push(new ArrayData($rec));
$test = array("Username" => "SnowB1", "FirstName" => "test", "LastName" => "test", "StreetAddress" => "123 test", "AptSuiteFloor" => "", "City" => "Erie", "State" => "PA", "Zip" => "16504", "Gender" => "Male", "DateOfBirth" => "01/01/1978", "EmailAddress" => "test@test.com", "RegisteredDate" => "01/01/2012");

    $myMembers->push(new ArrayData($test));
}
      
//BACK TO SILVERSTRIPE DB
DB::connect($databaseConfig);

return $myMembers;
      
}