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.

General Questions /

General questions about getting started with SilverStripe that don't fit in any of the categories above.

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

Displaying all the data from one table and the associated data from another [solved]

Go to End

2 Posts   653 Views


Community Member, 201 Posts

2 April 2012 at 8:09pm

Edited: 02/04/2012 8:14pm

Hi there,
Looking for some help/direction, I need to display a list of competences associated with a member so this would be something like below
Please note the dates should be inline with competences they relate to it's just not easy to show that on here, probably display as a table
Search Technician: Joe Bloggs

3 Basic Life Support

Date Passed

3.1 Demonstrate an understanding of the principles of ERC Guidelines –
To include scene safety, risks to the casualty & searchers.

3.2 Accurately and confidently perform a primary survey, using a systematic
approach to include the principle elements.


3.3 Demonstrate that you can carry out Effective and Efficient CPR on a Resuscitation
dummy for a period of not less than 4 minutes.

3.4 Demonstrates the correct techniques used to place casualties in the recovery position


3.5 Demonstrates the correct techniques to arrest major bleeds.

Patient Care & Evacuation
3.6 Understand appropriate patient care, and the equipment available to the team


3.7 As part of a team, demonstrate the ability to assist with casualty evacuation

3.8 Understand how to behave when dealing with the deceased

So I have a Competences table that lists the first two columns and then I have Passed table that stores the Passed date, the CompetenceID and the MemberID against each competency.

What I need to work out is how I can list all competences against a user and to show which have been passed and which haven't.
I'm doing this on a 2.4.7 system.

Hope someone can help




Community Member, 201 Posts

10 April 2012 at 7:46pm

Ok managed to get there with this, in case anyone needs it below is the query i used.

public function Comps() {
	$member = Member::currentUser()->ID;
		$sqlQuery = new SQLQuery(); 
			$sqlQuery->select = array('*');
			$sqlQuery->from = array("Competency LEFT OUTER JOIN CompetencyAchieved on Competency.ID = CompetencyAchieved.CompetencyID");
			$sqlQuery->where = array("CompetencyAchieved.MemberID = $member or CompetencyAchieved.MemberId is null");
			$sqlQuery->orderby = 'Number'; 
     		$sqlQuery->groupby = ''; 
			$rawSQL = $sqlQuery->sql(); 
			// execute and return a Query-object 
			$result = $sqlQuery->execute(); 
			//setup our blank DataObjectSet to push SQL result data into it. 
			$dos = singleton('Competency')->buildDataObjectSet($result);
				return $dos;