⚠ 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

How to show third table data



Erich Casagrande

Erich Casagrande
  • profile picture
  • Member

Posted 01 September 2016 - 04:29 AM

Hi every one,

I'm pretty new in Grocery CRUD, I buy the themes and really loving the GC.

But i'm little lost, I have a table called: User, that table are related with table city and city with table estate and state with country.

How do i show all those 3 ? in a view ?

 

Thanks for help, I really try to find a similar topic to my problem but i'm not good in english :( ( i'm brazilian )


Athar

Athar
  • profile picture
  • Member

Posted 17 January 2017 - 14:49 PM

Trick:

  1. Use query showing rows from all these tables. 
  2. Save the query as VIEW
  3. Use the view in your controller in place of 'table'

Amit Shah

Amit Shah
  • profile picture
  • Member

Posted 18 January 2017 - 01:41 AM

Athar,

 

Sorry brother - even i got confused with the reply.. if u can explain the same with some example.. it would be great...

 

@Erich - can you be more specific what exactly you are looking for? If you can draw and explain.. that too will be a great thing for someone to understand and share you an insight on what you need to do.


Athar

Athar
  • profile picture
  • Member

Posted 18 January 2017 - 16:55 PM

@Amit, Not sure if the poster is still interested in his Question.  BUT anyways for others:
 

"I have a table called: User, that table are related with table city and city with table estate and state with country."

 

 
Question:
a- How do i show all those 3 ? 
b- in a view ?
 
v8o8eg.png
Step 1. Create Tables using this SQL Script:
- MySQL Workbench Forward Engineering

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
USE `mydb` ;

-- -----------------------------------------------------
-- Table `mydb`.`STATE`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`STATE` ;

CREATE TABLE IF NOT EXISTS `mydb`.`STATE` (
  `ID_STATE` INT NOT NULL AUTO_INCREMENT,
  `ATTRIBUTES` VARCHAR(45) NULL,
  PRIMARY KEY (`ID_STATE`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`CITY`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`CITY` ;

CREATE TABLE IF NOT EXISTS `mydb`.`CITY` (
  `ID_CITY` INT NOT NULL AUTO_INCREMENT,
  `ID_STATE` INT NOT NULL,
  `ATTRIBUTES` VARCHAR(45) NULL,
  PRIMARY KEY (`ID_CITY`),
  INDEX `table2 [X] table1_idx` (`ID_STATE` ASC),
  CONSTRAINT `table2 [X] table1`
    FOREIGN KEY (`ID_STATE`)
    REFERENCES `mydb`.`STATE` (`ID_STATE`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`USER`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`USER` ;

CREATE TABLE IF NOT EXISTS `mydb`.`USER` (
  `ID_USER` INT NOT NULL AUTO_INCREMENT,
  `ID_CITY` INT NOT NULL,
  `ATTRIBUTES` VARCHAR(45) NULL,
  PRIMARY KEY (`ID_USER`),
  INDEX `table3 [X] table21_idx` (`ID_CITY` ASC),
  CONSTRAINT `table3 [X] table21`
    FOREIGN KEY (`ID_CITY`)
    REFERENCES `mydb`.`CITY` (`ID_CITY`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

Step 2. Insert Data using this script:

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

-- -----------------------------------------------------
-- Data for table `mydb`.`STATE`
-- -----------------------------------------------------
START TRANSACTION;
USE `mydb`;
INSERT INTO `mydb`.`STATE` (`ID_STATE`, `ATTRIBUTES`) VALUES (1, 'DELHI');
INSERT INTO `mydb`.`STATE` (`ID_STATE`, `ATTRIBUTES`) VALUES (2, 'NEW YORK');
INSERT INTO `mydb`.`STATE` (`ID_STATE`, `ATTRIBUTES`) VALUES (3, 'Rio De Janeiro');

COMMIT;


-- -----------------------------------------------------
-- Data for table `mydb`.`CITY`
-- -----------------------------------------------------
START TRANSACTION;
USE `mydb`;
INSERT INTO `mydb`.`CITY` (`ID_CITY`, `ID_STATE`, `ATTRIBUTES`) VALUES (1, 1, 'DELHI CITY');
INSERT INTO `mydb`.`CITY` (`ID_CITY`, `ID_STATE`, `ATTRIBUTES`) VALUES (2, 2, 'NEW YORK CITY');
INSERT INTO `mydb`.`CITY` (`ID_CITY`, `ID_STATE`, `ATTRIBUTES`) VALUES (3, 3, 'Rio De Janeiro');

COMMIT;


-- -----------------------------------------------------
-- Data for table `mydb`.`USER`
-- -----------------------------------------------------
START TRANSACTION;
USE `mydb`;
INSERT INTO `mydb`.`USER` (`ID_USER`, `ID_CITY`, `ATTRIBUTES`) VALUES (1, 3, 'ERICH');
INSERT INTO `mydb`.`USER` (`ID_USER`, `ID_CITY`, `ATTRIBUTES`) VALUES (2, 1, 'AMIT');
INSERT INTO `mydb`.`USER` (`ID_USER`, `ID_CITY`, `ATTRIBUTES`) VALUES (3, 2, 'ATHAR');

COMMIT;

Step 3. Create VIEW using this script:

CREATE VIEW `ALL_IN_ONE` AS
    SELECT 
        U.ATTRIBUTES AS USER,
        T.ATTRIBUTES AS CITY,
        S.ATTRIBUTES AS STATE
    FROM
        mydb.state S
            INNER JOIN
        city T ON s.id_state = T.ID_STATE
            INNER JOIN
        user U ON U.id_city = T.ID_city;

Step 4. Verify the view gives you want you're looking for:

n5sswm.png

Step 5. Use this SQL VIEW in your GC Controller. (DON'T GET CONFUSED WITH MVC VIEWS)

public function View()
{
	$crud = new grocery_CRUD();
	$crud->set_table('ALL_IN_ONE');
	$crud->set_subject('List of Users');
	//$crud->columns('',''); ---// you don't need this to have as columns are defined inside the VIEW
	//$crud->set_relation('','',''); ---// you don't need this to have as JOINS are inside the view
	$output = $crud->render();
	$this->_example_output($output);
}

Steps 1-4 should be done on database, and last is for GC. Hope this Helps!


Amit Shah

Amit Shah
  • profile picture
  • Member

Posted 24 January 2017 - 06:29 AM

Well what you have suggested is surely a way but grocery crud is not just about listing but it is surely about add / edit / delete too.. that will not be possible back there in the view:)

 

So when it comes to add / edit - u will have to deal the same in the code separately. 

Also need to check if grocery crud dose support grabbing the data from the view (surely i haven't tried to comment on it). 

 

Alternatively - there is a better way - handle the same @code level - as i suggested - u can have a callback to display the state on the columns or you can create your own custom model with custom queries. Either one will help you to arrive at the same solution

 

Happy GCing :)