The where parameter in setRelationNtoN doesn’t appear to work

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”.

@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

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

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

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