⚠ 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

JOINING Three (or more) Tables in Grocery CRUD



Athar
  • profile picture
  • Member

Posted 12 January 2017 - 22:43 PM

I have three tables as follow.
-- Table `mydb`.`table1`
CREATE TABLE IF NOT EXISTS `mydb`.`table1` (
  `ID_table1` INT NOT NULL AUTO_INCREMENT,
  `PARENT` VARCHAR(45) NULL,
  PRIMARY KEY (`ID_table1`))
ENGINE = InnoDB;

-- Table `mydb`.`table2`
CREATE TABLE IF NOT EXISTS `mydb`.`table2` (
  `ID_table2` INT NOT NULL AUTO_INCREMENT,
  `ID_table1` INT NOT NULL,
  `CHILD` VARCHAR(45) NULL,
  PRIMARY KEY (`ID_table2`),
  INDEX `table2 [X] table11_idx` (`ID_table1` ASC),
  CONSTRAINT `table2 [X] table11`
    FOREIGN KEY (`ID_table1`)
    REFERENCES `mydb`.`table1` (`ID_table1`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- Table `mydb`.`table3`
CREATE TABLE IF NOT EXISTS `mydb`.`table3` (
  `ID_table3` INT NOT NULL AUTO_INCREMENT,
  `ID_table2` INT NOT NULL,
  `BABY` VARCHAR(45) NULL,
  PRIMARY KEY (`ID_table3`),
  INDEX `table3 [X] table21_idx` (`ID_table2` ASC),
  CONSTRAINT `table3 [X] table21`
    FOREIGN KEY (`ID_table2`)
    REFERENCES `mydb`.`table2` (`ID_table2`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

My controller goes like this:

public function Relations()
{
 $crud = new grocery_CRUD();
 $crud->set_table('table1');
 $crud->set_relation('id_table2','table2','CHILD');
 $crud->set_relation('id_table3','table3','BABY');
 $crud->columns('PARENT','CHILD','BABY');
 $output = $crud->render();
 $this->_example_output($output);
}
What I want is a list of VALUES from these attributes:
 
PARENT    CHILD      BABY
NAME        NAME       NAME       ADD        VIEW       DELETE
NAME        NAME       NAME       ADD        VIEW       DELETE
NAME        NAME       NAME       ADD        VIEW       DELETE
NAME        NAME       NAME       ADD        VIEW       DELETE

 

  • Values for Parents and Children are CRUD on a different controllers respectively.
  • Values for Babies will be CRUD on this controller

I tried different random shots but nothing worked, therefore I don't have even partial result. 

 

Your help is highly appreciated in advance.

 


HACK
  • profile picture
  • Member

Posted 16 January 2017 - 12:58 PM

table_1 should have a foreign key of tabel_2 and tabel_3 

 

or you can read  

 

http://www.grocerycrud.com/documentation/options_functions/set_relation_n_n


Athar
  • profile picture
  • Member

Posted 17 January 2017 - 14:29 PM

table_1 should have a foreign key of tabel_2 and tabel_3 or you can read  http://www.grocerycrud.com/documentation/options_functions/set_relation_n_n

 

Hack, I am having difficult time to understand what you mean. Take a look at the attached.

"GREEN" is my current structure, which is pretty simple and normalized. All I need is to be able to operate CRUD on the lowest TABLE3 *and* be able to see values from TABLE1 AND TABLE2 in the list of TABLE3. 

 

what you proposed is the YELLOW scenario. I am big time confused with that structure. Not only its *NOT* normalized, but also not feasible for queries/performance. Please help me understand this.

 

set_relation_n_n(); applies on LINK tables or any structure reflecting the GREY in the picture. That is not applicable in my need.2r3hxqr.png


Amit Shah
  • profile picture
  • Member

Posted 18 January 2017 - 01:51 AM

Practically myfriend Athar, this aint a straight forward stuff that one can deal directly with relations..as the grocerycrud's basic works with a single table.. and can work with the related table..  Only the one which is related to the primary table. What you trying to achieve is a level beyond.. trying to connect to a table which is not related to the primary table but secondary table. This is technically not possible with grocery crud direct methodologies. What you can do for the same is - write a custom model that will help u resolve your situation / scenario .. or write in a column_callback where u will query up the data from 3rd table relatively and display the output.

 

This are the base 2 methods i am aware of .. there may be experts who may share in further / beyond the same.. but to me.. this is the approach. Second one is easy .. but have a drawback.. it will hit the tables / queries .. the number of rows that gets displayed. But it may not be huge in number so it is more lucrative approach for me.


Athar
  • profile picture
  • Member

Posted 18 January 2017 - 15:44 PM

@Amit, Thanks a lot. 

 
You actually did help clearing up some smoke I had in my coconut. I was under the impression that it was me who was dropping the ball somewhere. Your proposals do make sense and worth to explore further. However, during the process of trial and run, I discovered something that may intrigue others. 
2u8cl0y.png
If the table in the middle layer has a Composite KEY consisting of PK and FK from the 1st table, we CAN pull the value from 1st table in the list of 3rd table. I was successfully able to do that.
 
However, that lead me to hitch. It does not retain the dependency in third table. or at least its not logical in some scenerios. 
 
Whats your say on this?