Jump to:

22978 Posts in 11759 Topics by 2827 members

General Questions

SilverStripe Forums » General Questions » Displaying all the data from one table and the associated data from another [solved]

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

Page: 1
Go to End
Author Topic: 259 Views
  • cumquat
    Avatar
    Community Member
    189 Posts

    Displaying all the data from one table and the associated data from another [solved] Link to this post

    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

  • cumquat
    Avatar
    Community Member
    189 Posts

    Re: Displaying all the data from one table and the associated data from another [solved] Link to this post

    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

    259 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.