[Athar]

Am I missing something here?

SQL Script:

CREATE TABLE IF NOT EXISTS `db`.`HR` (
  `ID_HR` INT NOT NULL AUTO_INCREMENT COMMENT 'PK',
  `EMPLOYEE NAME` VARCHAR(45) NULL COMMENT 'FULL NAME',
  `DESIGNATION` VARCHAR(45) NULL,
  `MANAGER` INT NOT NULL COMMENT 'EMPLOYEE MANAGER',
  PRIMARY KEY (`ID_HR`),
  INDEX `HR [X] HR1_idx` (`MANAGER` ASC),
  CONSTRAINT `HR [X] HR1`
    FOREIGN KEY (`MANAGER`)
    REFERENCES `db`.`HR` (`ID_HR`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;



-- -----------------------------------------------------
-- Data for table `db`.`HR`
-- -----------------------------------------------------
START TRANSACTION;
USE `db`;
INSERT INTO `db`.`HR` (`ID_HR`, `EMPLOYEE NAME`, `DESIGNATION`, `MANAGER`) VALUES (1, 'George', 'CEO', 1);
INSERT INTO `db`.`HR` (`ID_HR`, `EMPLOYEE NAME`, `DESIGNATION`, `MANAGER`) VALUES (2, 'Neil', 'PRESIDENT', 1);
INSERT INTO `db`.`HR` (`ID_HR`, `EMPLOYEE NAME`, `DESIGNATION`, `MANAGER`) VALUES (3, 'Kevin', 'DIRECTOR', 2);
INSERT INTO `db`.`HR` (`ID_HR`, `EMPLOYEE NAME`, `DESIGNATION`, `MANAGER`) VALUES (4, 'Peter', 'ACCOUNTS MANAGER', 3);
INSERT INTO `db`.`HR` (`ID_HR`, `EMPLOYEE NAME`, `DESIGNATION`, `MANAGER`) VALUES (5, 'Alexa', 'HR MANAGER', 3);

COMMIT;

Here is the result from simple query.

 

 

SQL View:

CREATE VIEW `DB`.`HRLIST` AS
SELECT 
    H.`EMPLOYEE NAME` AS EMPLOYEE,
    H.DESIGNATION AS DESINGATION,
    R.`EMPLOYEE NAME` AS MANAGER
FROM
    DB.HR H
        INNER JOIN
    DB.HR R ON R.ID_HR = H.MANAGER;

Result of SQL VIEW:

GC Controller:

function Test() {	
     $crud = new grocery_CRUD();
     $crud->set_table('HRLIST');
     $crud->columns('EMPLOYEE','DESIGNATION','MANAGER');
     $output = $crud->render();  
     $this->_example_output($output); 
}

NOTE that if I point it to $crud->set_table('HR'); [the physical table], instead of $crud->set_table('HRLIST'); [which is a SQL View], it works fine as you can see below. Which means my Controller and database are totally fine.

 

 

And, this is what it blesses me with:

 

 

The only thing I can suspect is "the VIEW itself". I tried with lowercase (hrlist) and/or UPPER(HRLIST). Any input?

 

[Amit Shah]

Well to my understanding, the library looks up for a table to exist and not a view...I dont remember doing it myself but u can join the same table with set_relation and get the derived solution.

[Athar]

Agreed: Views won't allow you to operate CRUD, however still can be handy sometimes.

 

I got it worked though. Simply put, for SQL VIEWS, you have to have a PK listed in the query. and then set the: 

$crud->set_table('HRLIST);
$crud->set_primary_key('ID);

[web-johnny]

It seems like another Codeigniter bug as Grocery CRUD is simply using the:

 

$this->db->table_exists

function of Codeigniter. Can you try to change the code at:

models/Grocery_crud_model.php

 

around line 43 and change the code:

 

function db_table_exists($table_name = null)
    {
    	return $this->db->table_exists($table_name);
    }

with:

 

function db_table_exists($table_name = null) {
    return true;
}

let me know if that worked

[Amit Shah]

Hi John, 
I agree that can be overridden but if done then wont it break for the regular further functionalities? Think one can alter the code not just to check if the table exists .. but also check if the view exists.

SHOW FULL TABLES IN youdatabasename WHERE TABLE_TYPE LIKE '%VIEW%';

This query should return the list of views too. may be this was a better solution  :)

 

Happy GCing :)