Jump to:

23481 Posts in 18951 Topics by 2878 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: 310 Views
  • cumquat
    Community Member
    198 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.   

    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



  • cumquat
    Community Member
    198 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;



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.