Jump to:

3373 Posts in 998 Topics by 712 members

Data Model Questions

SilverStripe Forums » Data Model Questions » Return DataList results from external database

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

Page: 1
Go to End
Author Topic: 1502 Views
  • SnowB1
    Avatar
    Community Member
    8 Posts

    Return DataList results from external database Link to this post

    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;
       }

  • Willr
    Avatar
    Forum Moderator
    5462 Posts

    Re: Return DataList results from external database Link to this post

    ("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.

  • SnowB1
    Avatar
    Community Member
    8 Posts

    Re: Return DataList results from external database Link to this post

    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

  • Willr
    Avatar
    Forum Moderator
    5462 Posts

    Re: Return DataList results from external database Link to this post

    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));
    }

  • SnowB1
    Avatar
    Community Member
    8 Posts

    Re: Return DataList results from external database Link to this post

    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;
          
    }

    1502 Views
Page: 1
Go to Top

Want to know more about the company that brought you SilverStripe? Then check out SilverStripe.com

Comments on this website? Please give feedback.