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.

We've moved the forum!

Please use forum.silverstripe.org for any new questions (announcement).
The forum archive will stick around, but will be read only.

You can also use our Slack channel or StackOverflow to ask for help.
Check out our community overview for more options to contribute.

Data Model Questions /

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

Return DataList results from external database


Go to End


5 Posts   4678 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, 5523 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, 5523 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;
		
}