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

Querying Group_Members Table


Reply

5 Posts   1481 Views

Avatar
IOTI

31 August 2010 at 2:42am Community Member, 189 Posts

I understand that you can't use DataObject::get on Group_Members because its a relational table with no classname column(thats my understanding at least)

What i want to do is get the GroupID where MemberID equals a certain MemberID depending on who is logged on. I'm trying to figure out how to do this easily. Do i need to use joins or can I just use a DB:Query?

any help would be appreciated.

Avatar
Willr

31 August 2010 at 8:32pm Forum Moderator, 5511 Posts

well if you just want ID's then you can just use something like DB::query("SELECT GroupID FROM Group_Members WHERE MemberID = '1'")

Avatar
IOTI

1 September 2010 at 1:10am Community Member, 189 Posts

I tried using that but it didn't seem to return anything, maybe i was doing something wrong.

Heres a snippet of my code

//getting current memberID
$memberID = Member::currentUserID();
//using rawquery to get groupID
$groupMemberID = DB::query("SELECT GroupID FROM Group_Members WHERE MemberID =". $memberID);

$groupMember = dataobject::get_one('Group', "ID = ". $groupMemberID);

$parentID = $groupMember->getField('ParentID');

Using this code, i get a couldnt run query SQL error "FROM "Group" WHERE (ID = ) LIMIT 1" it looks as though $groupMemberID is returning NULL. Is there some other step(s) I am missing?

Avatar
Willr

1 September 2010 at 10:08am Forum Moderator, 5511 Posts

DB::query() returns you a query object. You then have to call the various methods on it to get the raw data. For example the query DB::query("SELECT GroupID FROM Group_Members WHERE MemberID =". $memberID); would return a single column of ID's. If you wanted the top left most value (top group ID) you would call value(); on it

DB::query("SELECT GroupID FROM Group_Members WHERE MemberID =". $memberID)->value();

If you wanted a column of all the ID's in an array you call column() which returns the left most column

DB::query("SELECT GroupID FROM Group_Members WHERE MemberID =". $memberID)->column();

http://api.silverstripe.org/2.4/sapphire/model/SS_Query.html

Avatar
IOTI

1 September 2010 at 11:51pm Community Member, 189 Posts

perfect, thank you, Willr.