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 /

Moderators: martimiz, Sean, biapar, Willr, Ingo, swaiba

Return DataList results from external database


Go to End
Reply


5 Posts   2482 Views

Avatar
SnowB1

Community Member, 11 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, 5521 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, 11 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, 5521 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, 11 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;
		
}