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, biapar, Willr, Ingo, swaiba, simon_w

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


Reply


2 Posts   407 Views

Avatar
cumquat

Community Member, 198 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.   
01/01/12

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

01/01/12

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   

02/02/12

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   

03/03/12

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

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

Regards

Mick

Avatar
cumquat

Community Member, 198 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;
         //
      }

laters

Mick