The where parameter in setRelationNtoN doesn't appear to work

Grocery CRUD Community Edition
1

Title says it all. And another sentence to make in more than 20 characters.

2

I can confirm this @johnny, and Iā€™ll be using +20 characters to tell you how Iā€™ve tested itā€¦
Iā€™m using CodeIgniter 4.4.1 and GCE v3.0.12.
Iā€™ve followed the docs available here and tested with your own example database. To my surprise the example didnā€™t work out-of-the-box because the in ā€˜examples_database.sqlā€™ the actor table has no ā€œstateā€ column (as mentioned in the referred docs).

$crud->setTable('film');
$crud->setSubject('Film', 'Films');

$crud->setRelationNtoN('actors', 'film_actor', 'actor', 'film_id', 'actor_id', 'fullname', null, [
     'actor.state' => 'public'
]);
$crud->setRelationNtoN('categories', 'film_category', 'category', 'film_id', 'category_id', 'name');

$output = $crud->render();

Also setting where parameter to ['actor.state' => 'public'], per documentation, will not work and will trigger the error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'actor.state' in 'where clause'

Even after creating the missing column ā€˜stateā€™ in the examples database.
I think thatā€™s because the generated queries renamed the actor table to ā€˜aā€™, like in:

SELECT `a`.* FROM `actor` AS `a`

So setting where parameter to ['a.state' => 'public'] or, better, ['state' => 'public'] will work, but, unfortunatelly, stiil does not filter the results. I think that was what to OP means by ā€œdoesnā€™t appear to workā€.

3

@johnny I have also noticed the following error while quick filtering in the $fieldName column relative to setRelationNtoN(), even without the where parameter:

CRITICAL - 2023-10-01 17:51:58 --> SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') AND `film_category`.`category_id` = '2' LIMIT 10 OFFSET 0' at line 1
in VENDORPATH\laminas\laminas-db\src\Adapter\Driver\Pdo\Connection.php on line 376.
 1 VENDORPATH\laminas\laminas-db\src\Adapter\Driver\Pdo\Connection.php(376): PDO->query()
 2 VENDORPATH\laminas\laminas-db\src\Adapter\Adapter.php(194): Laminas\Db\Adapter\Driver\Pdo\Connection->execute()
 3 VENDORPATH\grocery-crud\enterprise\src\GroceryCrud\Core\Model.php(1133): Laminas\Db\Adapter\Adapter->query()
 4 VENDORPATH\grocery-crud\enterprise\src\GroceryCrud\Core\Model.php(1390): GroceryCrud\Core\Model->getResultsFromSelect()
 5 VENDORPATH\grocery-crud\enterprise\src\GroceryCrud\Core\State\DatagridState.php(320): GroceryCrud\Core\Model->getList()
 6 VENDORPATH\grocery-crud\enterprise\src\GroceryCrud\Core\State\DatagridState.php(331): GroceryCrud\Core\State\DatagridState->getData()
 7 VENDORPATH\grocery-crud\enterprise\src\GroceryCrud\Core\GroceryCrud.php(2878): GroceryCrud\Core\State\DatagridState->render()
 8 APPPATH\Controllers\FullExample.php(26): GroceryCrud\Core\GroceryCrud->render()
 9 SYSTEMPATH\CodeIgniter.php(919): App\Controllers\FullExample->index()
10 SYSTEMPATH\CodeIgniter.php(494): CodeIgniter\CodeIgniter->runController()
11 SYSTEMPATH\CodeIgniter.php(353): CodeIgniter\CodeIgniter->handleRequest()
12 FCPATH\index.php(79): CodeIgniter\CodeIgniter->run()

Thatā€™s because the generated query has a empty where() as you can see below:

SELECT `film`.`film_id` AS `film_id`, `film`.`title` AS `title`, `film`.`description` AS `description`, `film`.`release_year` AS `release_year`, `film`.`rental_duration` AS `rental_duration`, `film`.`rental_rate` AS `rental_rate`, `film`.`length` AS `length`, `film`.`replacement_cost` AS `replacement_cost`, `film`.`rating` AS `rating`, `film`.`special_features` AS `special_features`, `film`.`last_update` AS `last_update` FROM `film` INNER JOIN `film_category` ON `film_category`.`film_id`=`film`.`film_id` WHERE () AND `film_category`.`category_id` = '2' LIMIT 10 OFFSET 0
4

Thanks @bgeneto for your detailed description. I will have it a look and let you know,

It seems that the issue is also in the open-source edition.

Regards
Johnny

5

I can reproduce the issue. Seems that this is caused by:

GC-498: Where statement using Lamina-Db Where object doesnā€™t work as expected when we filter.

on 3.0.10 . I will try to fix it for the new version.

Regards
Johnny

1 Like
6

Hello @bgeneto ,

This is now fixed on version 3.0.13 . As I was expecting it was a bug that Iā€™ve missed with the 3.0.10

Regards
Johnny