Support for CRUD on views about php-crud-api

[mevdschee]

@ErikV88 tested this on MySQL and it works, could it be that you forgot to add:

ALTER TABLE posts ADD PRIMARY KEY id (id);`

Where 'posts' is your table?

[mevdschee]

Please let me know whether or not this solves the issue.

[mevdschee]

closed due to inactivity

[ErikV88]

Sorry, It fail when calling a view . We need a uniq table It also be cool if we add a table with synoynm /alias for all object . DatabaseObjects what have metadata to db objects , also sp view , and tables

[mevdschee]

Please post your table definition, so I can help you. Use:

show table posts;

Where 'posts' is the table you are using.

[ErikV88]

CREATE TABLE IF NOT EXISTS `BasketProducts` (
  `BasketID` int(11) NOT NULL,
  `ProductID` int(11) NOT NULL,
  `NummerOfProducts` int(11) NOT NULL DEFAULT '1',
  `PrimKey` varchar(64) NOT NULL DEFAULT '',
  PRIMARY KEY (`PrimKey`),
  KEY `WebShop_BasketProductsProductID` (`ProductID`,`BasketID`),
  KEY `IX_ProductBasket` (`BasketID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



DROP TRIGGER IF EXISTS `IXBasketProducts`;
DELIMITER //
CREATE TRIGGER `IXBasketProducts` BEFORE INSERT ON `BasketProducts`
 FOR EACH ROW SET NEW.PrimKey=UUID()
//
DELIMITER ;

ALTER ALGORITHM=UNDEFINED DEFINER=`myUser`@`localhost` SQL SECURITY DEFINER VIEW `aviw_BasketProducts` AS 
    SELECT `P`.`ID` AS `ID`,`P`.`Name` AS `Name`,`P`.`Price` AS `Price`,`P`.`Description` AS `Description`,`BP`.`NummerOfProducts` AS `NummerOfProducts`,`BP`.`PrimKey`     AS `PrimKey`,(`P`.`Price` * `BP`.`NummerOfProducts`) AS     `TotalPrice` 
    FROM (`BasketProducts` `BP`     
      INNER JOIN WebShop_Products` `P` on((`P`.`ID` = `BP`. `ProductID`))) 
      GROUP BY `BP`.`BasketID`,`BP`.`ProductID`

[mevdschee]

I think your view has either none or two primary keys. Check the output of:

SELECT COLUMN_NAMEFROMINFORMATION_SCHEMA.COLUMNSWHERECOLUMN_KEY= 'PRI' ANDTABLE_NAME = 'aviw_BasketProducts';

Probably it does not show one single primary key (1pk) on the view (as is required).

[mevdschee]

Tried to simulate your case and even without join there are no primary keys defined for views (probably by definition). This means that in order to expose full crud on a view we need another method to find the primary key. Fallback to the name "id" comes to mind as a possible solution or to default to the first column of the view.

[ErikV88]

I find a solution to it ... in my js lib , my object now the uniqtable of the view... Then i can call the view if update and delete or update . but if select then the view 👍

[mevdschee]

Fallback to the name "id" comes to mind as a possible solution or to default to the first column of the view.

This is now implemented