⚠ In case you've missed it, we have migrated to our new website, with a brand new forum. For more details about the migration you can read our blog post for website migration. This is an archived forum. ⚠

  •     

profile picture

Indirect join



John Ophof

John Ophof
  • profile picture
  • Member

Posted 25 January 2015 - 12:31 PM

I have a table like this

 

scrum_team with fields scrum_masterID

CREATE TABLE `scrum_team` (
  `scrum_teamID` int(11) NOT NULL AUTO_INCREMENT,
  `team_name` varchar(45) DEFAULT NULL,
  `date_started` datetime DEFAULT NULL,
  `scrum_masterID` int(11) DEFAULT NULL,
  `product_ownerID` int(11) DEFAULT NULL,
  PRIMARY KEY (`scrum_teamID`),
  KEY `fk_scrum_team_scrum_master1_idx` (`scrum_masterID`),
  KEY `fk_scrum_team_product_owner1_idx` (`product_ownerID`),
  CONSTRAINT `fk_scrum_team_product_owner1` FOREIGN KEY (`product_ownerID`) REFERENCES `product_owner` (`product_ownerID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_scrum_team_scrum_master1` FOREIGN KEY (`scrum_masterID`) REFERENCES `scrum_master` (`scrum_masterID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
 

the field scrum_masterID is primary key of the table scrum_master

scrum_master has a field personID

CREATE TABLE `scrum_master` (
  `scrum_masterID` int(11) NOT NULL AUTO_INCREMENT,
  `personID` int(11) DEFAULT NULL,
  PRIMARY KEY (`scrum_masterID`),
  KEY `fk_scrum_master_person1_idx` (`personID`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
 

personID is linked to the person table having fields like first_name, christian name

CREATE TABLE `person` (
  `personID` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(45) DEFAULT NULL,
  `insertion` varchar(45) DEFAULT NULL,
  `phone` varchar(45) DEFAULT NULL,
  `email` varchar(45) DEFAULT NULL,
  `location` varchar(45) DEFAULT NULL,
  `christian_name` varchar(45) DEFAULT NULL,
  `ittle` varchar(45) DEFAULT NULL,
  `copafilth_itemID` int(11) DEFAULT NULL,
  `gmt_offset` int(4) DEFAULT NULL,
  `personcol` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`personID`),
  UNIQUE KEY `email_UNIQUE` (`email`),
  KEY `fk_person_copafilth_item1_idx` (`copafilth_itemID`),
  CONSTRAINT `fk_person_copafilth_item1` FOREIGN KEY (`copafilth_itemID`) REFERENCES `copafilth_item` (`copafilth_itemID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
 

In the form of scrum team I want to pick people from the person table and fill in the proper fields (scrum_masterID and  product_ownerID

 

I hope this is clear and one of you is more brilliant than me :-)

 

Tomorrow I have a demo so help will be great!

 

I've tried this

 

$crud->set_relation("scrum_masterID", "scrum_master", "personID");      

$crud->set_relation("personID", "person", "christian_name");

but this was no good since personID is not a field of the scrum_team table.

 

I have created a view that will help to see my DB structure:

 

create view vw_scrum_team as
    select st.scrum_teamID, st.scrum_masterID, st.product_ownerID, st.team_name, st.date_started,
    ps_sm.first_name as "Scrum master first name",
    ps_sm.christian_name as "Scrum master christian name" ,
    ps_po.first_name as "Product owner first name",
    ps_po.christian_name as "Product owner christian name"
    from scrum_team st
    left outer join scrum_master sm on st.scrum_masterID = sm.scrum_masterID
    left outer join product_owner po on st.product_ownerID = po.product_ownerID
    left outer join person ps_sm on ps_sm.personID = sm.personID

    left outer join person ps_po on ps_po.personID = po.personID
 

In my datamodel I have multiple constructs like this one.

 

Another related issue I have is the following. In my Database I want all tables to have a parent relationship with it self. So every entry can be put in a hierarchy for this my table setup is like this:

 

CREATE TABLE IF NOT EXISTS `application` (
  `applicationID` INT(11) NOT NULL,
  `name` VARCHAR(255) NULL,
  `parentID` INT(11) NULL,
  PRIMARY KEY (`applicationID`),
  CONSTRAINT `parentID`
    FOREIGN KEY (`parentID`)
    REFERENCES `application` (`applicationID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

 

On my javascript console is this when I add an application entry

jquery-1.11.1.min.js:4 POST http://127.0.0.1/ezbuild30/scrumba.php/sprint/application/insert 500 (Internal Server Error)

 

This is he code for application in my controller:

 

function application() {
    $table = "application";
    $subject = "Application";
    $crud = new grocery_CRUD();
    $crud->set_theme('datatables');
    $crud->set_table($table);
    $crud->set_subject($subject);

    $crud->edit_fields('name', 'parentID');
    $crud->columns('name', 'parentID');
    $crud->add_fields('name', 'parentID');

    $crud->display_as('name', 'Application');
    $crud->display_as('parentID', 'Belongs to application');

    $crud->set_relation("parentID", "application", "name");

    $output = $crud->render();
    $this->_scrumba_output($output);
  }

 

Many thanks in advance.

 

John