Jump to content


What am I missing here?

SQL VIEW CONTROLLER SELFJOIN JOIN NESTED QUERY TABLE DATABASE RELATION

  • Please log in to reply
4 replies to this topic

#1 Athar

Athar

    Member

  • Members
  • PipPip
  • 13 posts
  • LocationNew York

Posted 20 January 2017 - 10: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?

 


~ I think I think, therefore; I think I exist.


#2 Amit Shah

Amit Shah

    grocery CRUD Master

  • Advanced Member
  • PipPipPip
  • 1,192 posts
  • LocationMumbai, India

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.



#3 Athar

Athar

    Member

  • Members
  • PipPip
  • 13 posts
  • LocationNew York

Posted 24 January 2017 - 01: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


~ I think I think, therefore; I think I exist.


#4 web-johnny

web-johnny

    grocery CRUD Author

  • Administrators
  • 1,100 posts
  • LocationLondon

Posted 24 January 2017 - 07: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


Posted Image

#5 Amit Shah

Amit Shah

    grocery CRUD Master

  • Advanced Member
  • PipPipPip
  • 1,192 posts
  • LocationMumbai, India

Posted 26 January 2017 - 06: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 :)







Also tagged with one or more of these keywords: SQL, VIEW, CONTROLLER, SELFJOIN, JOIN, NESTED QUERY, TABLE, DATABASE, RELATION

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users