I am having enormous amount of problems with this framework/cms. Any help would be appreciated at this point...

So here is my problems:
I am trying to do something relatively simple: listing the groups that each member belongs to on the member listing page, and then be able to search on these listed member groups.

So that i can could have the search functionality i had to extend the model admin

//MODEL ADMIN
class CartusModelAdmin extends ModelAdmin{
	private static $managed_models = array('CartusMember');
	private static $url_segment = "members";
	private static $menu_title = "Network Members";
	private static $tree_class = 'Group';
	private static $subitem_class = 'Member';
	private static $allowed_actions = array("SearchForm",'groups');

	public  $showImportForm = false;

       public function getEditForm($id=null,$fields=null){
		$form = parent::getEditForm($id, $fields);

		$gridField = $form->Fields()->fieldByName($this->sanitiseClassName($this->modelClass));

		$con = $gridField->getConfig()->getComponentByType('GridFieldDataColumns');
		$con->setDisplayFields(array("Surname"=>"Last Name","Email"=>"Email Address","StatusName"=>"Status","DefaultCartusGroup"=>"Default Group","GroupsAsString"=>"Groups"));

		return $form;
	}
        
        public function getSearchContext(){
		
		$search_status = "Active";
		$search_groups ="";
		if($this->request->getVar('q') != ""){
			$q = $this->request->getVar('q');
			if(isset($q['StatusName']))
				$search_status = $q['StatusName'];
			if(isset($q['Groups']))
				$search_groups = $q['Groups'];
		}

		$context = parent::getSearchContext();
		
		$f = $context->getFields();
		$f->push(new ListboxField('q[StatusName]','Status',CartusConfig::$allstatus,$search_status,null,true));
	
		$f->push(new ListboxField('q[Groups]','Groups',DataObject::get("Group")->map('ID','Title')->toArray(),$search_groups,null,true));
		$filters = array(
				'Email' => new PartialMatchFilter('Email')
				,'StatusName' => new ExactMatchFilter('StatusName')
				,'Groups' => new ExactMatchFilter('GroupsAsString') //<-- PROBLEM AREA
		);
	
	$context->setFilters($filters);
		
		

		return $context;
	}

	
}

//DATA EXTENSION
class CartusMemberExtension extends DataExtension {
	//public static $allowed_actions = array("SearchForm");
	
	public static $db= array(
			"DefaultCartusGroup" => "Varchar(255)"
			,"StatusName" =>"Enum('Active,Legacy User,Pending Access,Inactive,Password Locked,Denied,Expired,New Account')"//HAS to match the same order and name as CartusConfig::allstatus property
			,"StatusReason" => "Enum('Not in Network,Removed from Network,Client Directed - Limited Restricted Access Supplier,User no longer in Role,User registered in Error,User Not Accessing Site,Cartus Leadership Directed Access Removal')"
	);
	
	
	private static $summary_fields = array('Surname','Email','StatusName','DefaultCartusGroup','GroupsAsString');

		
	
	public function updateCMSFields(Fieldlist $fields) {
		$fields->removeFieldFromTab('Root.Main','DateFormat');
		$fields->removeFieldFromTab('Root.Main','TimeFormat');
		$fields->addFieldToTab("Root.Main",new DropdownField('DefaultCartusGroup','Default Cartus Group',self::getMemberGroups("val_val"), $multiple=false));
		$fields->addFieldToTab("Root.Main",new DropdownField('StatusName','Status',CartusConfig::$allstatus,$multiple=false));
		$ff = new DropdownField('StatusReason','Reason',CartusConfig::$allreasons,$multiple=false);
		$fields->addFieldToTab("Root.Main",$ff->setHasEmptyDefault(true));
		


		return $fields;
	}
	
	public function getGroupName($id){
		$g = DataObject::get_one("Group", "ID ='".$id."'");
		return $g->getTreeTitle();
	}
	
	public static function getAllGroups(){
		return DataObject::get("Group")->map('ID','Title');
	}
	public static function getAllGroupsTitleTitle(){
		return DataObject::get("Group")->map('Title','Title');
	}
	

/**
 * 
 * @param string $type = id_val will return array[$id]=$val while val_val will return array[$val]=$val
 * @return multitype:NULL
 */
	public function getMemberGroups($type="id_val"){
		$id = Controller::curr()->getRequest()->param('ID');
		//	$member = Member::get()->byID($id);
		$arr_grp = array();
		if($id != "new"){
			$member_groups = permission::groupList($id);
			//print_r($member_groups);
				
			foreach($member_groups as $gid){
				$g = DataObject::get_one("Group", "ID ='".$gid."'");
				if($type =="id_val")
					$arr_grp[$g->ID]=$g->getTreeTitle();
				elseif($type =="val_val") 
					$arr_grp[$g->getTreeTitle()]=$g->getTreeTitle();
				
			}
				
		}
		//print_r($arr_grp);exit;
		return $arr_grp;
	}
	
	public function getGroupsAsString(){
		$g = DB::query("SELECT DISTINCT `Group`.`Title` FROM `Group`
							   INNER JOIN `Group_Members`  AS `GM` ON `GM`.`GroupID` = `Group`.`ID` WHERE `GM`.`MemberID`='".$this->owner->ID."'");

		$gg =array();
		foreach ($g as $gr){
			//print_r($gr);
			$gg[] = $gr['Title'];
		}
		if (count($gg) ==0) {
			return 'no Group';
		}
		//print_r($gg);
		return implode(', ', $gg);
		// or if one field is not enough for you, you can use a foreach loop:
		// $teamsArray= array();
		// foreach ($this->Teams() as $team) {
		//     $teamsArray[] = "{$team->ID} {$team->Title}";
		// }
		// return implode(', ', $teamsArray);
	}

	
	
	
	
	




}

//MEMBER
class CartusMember extends Member {
	
	

	public static $allowed_actions = array("groups");
	

	private static $searchable_fields = array("Email","StatusName");
	
	private static $field_labels = array(
			'Surname' => 'Last Name'
			,'StatusName' => 'Status'
			,'DefaultCartusGroup'=>'Default Group'
			,'Group'=>'Groups'
	);
	
	static $belongs_many_many = array(
			"Groups" => "Group"
	);
	private static $summary_fields = array('Surname','Email','StatusName','DefaultCartusGroup','GroupsAsString');
}

So the CartusMember class which extend the member class managed by the CartusModelAdmin allows me to tie in the GroupsAsString which then gets the groups a user belongs to and make a comma seperated string which then gets inserted into the listing.

Why do i get an error that " the column GroupsAsString is a unknown column in the where clause" If i already tied in the listing page with the group why is it not joined in searchresults?

Maybe i am doing this all wrong.

the end goal for this is:
1. I want to be able to list members with the groups they belong to and
2. search members by groups that they belong to.

Thanks

Ok the solution is to create my own custom filter class and update the getSearchContext method to point to that new filter class.
BEFORE:

public function getSearchContext(){
...
$filters = array( 
            'Email' => new PartialMatchFilter('Email') 
            ,'StatusName' => new ExactMatchFilter('StatusName') 
            ,'Groups' => new ExactMatchFilter('GroupsAsString') //<-- PROBLEM AREA 
      ); 
..

}

public function getSearchContext(){
...
$filters = array( 
            'Email' => new PartialMatchFilter('Email') 
            ,'StatusName' => new ExactMatchFilter('StatusName') 
            ,'Groups' => new GroupsRelationsFilter('Groups') //<-- PROBLEM AREA 
      ); 
..
}

class GroupsRelationsFilter extends SearchFilter{
   
    protected function applyOne(DataQuery $query){
        parent::applyOne($query);   
    }
   
    public function apply(DataQuery $query) {
        $values = array();
          foreach($this->getValue() as $value) {
              $values[] = Convert::raw2sql($value);
          }
         $query->leftJoin('Group_Members','`Group_Members`.`MemberID` = `Member`.`ID`')->leftJoin('Group','`Group`.`ID` = `Group_Members`.`GroupID`');
         $vv = array();        
         foreach ($values as $v ){
             $vv[] ="`Group`.`ID`='$v'";
         }
         
         $query->whereAny($vv);
          return $query;
    }
   
    protected function excludeOne(DataQuery $query) {
        parent::excludeOne($query);
    }

    public function isEmpty() {
         return $this->getValue() == null || $this->getValue() == '';
    }
    
}

Love it when someone takes the time to post an answer to their own question, especially when it's likely to be useful to others