⚠ 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

What am I missing here?



Athar

Athar
  • profile picture
  • Member

Posted 20 January 2017 - 22:33 PM

Am I missing something here?

2rxhfdk.png

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.

 

2rxihxs.png

 

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:

2rvxv2p.png

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.

 

2gu9v6h.png

 

And, this is what it blesses me with:

2ppga9y.png

 

 

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

 


Amit Shah

Amit Shah
  • profile picture
  • Member

Posted 24 January 2017 - 06:21 AM

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

Athar
  • profile picture
  • Member

Posted 24 January 2017 - 13:49 PM

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);

15ev5ll.png


web-johnny

web-johnny
  • profile picture
  • Administrator
  • 1,166 posts

Posted 24 January 2017 - 19:11 PM

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

Amit Shah
  • profile picture
  • Member

Posted 26 January 2017 - 18:35 PM

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 :)