Consider the following example. For customers there should be a field which indicates if the customer wants to receive a newsletter. This is modelled in a table in Postgres by using a boolean as followed:
CREATE TABLE customers
(id integer
GENERATED ALWAYS AS IDENTITY,
email_address text
NOT NULL,
wants_newsletter boolean
NOT NULL
DEFAULT true,
PRIMARY KEY (id),
UNIQUE (email_address));
The controller looks like this:
<?php
namespace App\Controllers;
use GroceryCrud\Core\GroceryCrud;
class Customers extends BaseController
{
protected function _getDbData()
{
$db = (new \Config\Database())->default;
return ['adapter' => ['driver' => 'Pdo_Pgsql',
'host' => $db['hostname'],
'database' => $db['database'],
'username' => $db['username'],
'password' => $db['password'],
'charset' => 'utf8']];
}
protected function _getGroceryCrudEnterprise($bootstrap = true, $jquery = true)
{
$db = $this->_getDbData();
$config = (new \Config\GroceryCrudEnterprise())->getDefaultConfig();
$groceryCrud = new GroceryCrud($config, $db);
$groceryCrud->setCsrfTokenName(csrf_token());
$groceryCrud->setCsrfTokenValue(csrf_hash());
return $groceryCrud;
}
protected function _output($output = null, $title = null)
{
$output->title = $title;
if (isset($output->isJSONResponse)
&& $output->isJSONResponse) {
header('Content-Type: application/json; charset=utf-8');
echo $output->output;
exit;
}
return view('view', (array)$output);
}
public function index()
{
$crud = $this->_getGroceryCrudEnterprise();
$crud->setSubject('Customer', 'Customers');
$crud->setTable('customers');
$crud->columns(['email_address', 'wants_newsletter']);
$crud->fields(['email_address', 'wants_newsletter']);
$crud->fieldType('email_address', 'email');
$crud->displayAs('email_address', 'E-Mail Address');
$crud->displayAs('wants_newsletter', 'Wants Newsletter');
$crud->requiredFields(['email_address', 'wants_newsletter']);
$crud->uniqueFields(['email_address']);
$crud->defaultOrdering('email_address');
$crud->callbackAddForm(function ($data) {
$data['wants_newsletter'] = true;
return $data;
});
$output = $crud->render();
return $this->_output($output, 'Customers');
}
}
And that is the view:
<!DOCTYPE html>
<html>
<head>
<title>
<?php echo "Customers"; ?>
</title>
<meta charset="utf-8" />
<?php foreach($css_files as $file): ?>
<link type="text/css" rel="stylesheet" href="<?php echo $file; ?>" />
<?php endforeach; ?>
</head>
<body>
<div>
<?php echo $output; ?>
</div>
<?php foreach($js_files as $file): ?>
<script src="<?php echo $file; ?>">
</script>
<?php endforeach; ?>
</body>
</html>
We’re using Grocery Crud Enterprise 3.2.3 (i.e. currently the latest) in a CodeIgniter 4 project with the bootstrap v5 1.5.3 theme installed via Composer.
The issues:
(1) When editing a customer where wants_newsletter = true in the database, the form shows the toggle in the false state (
).
(2) When using the quick search for the boolean there’s an error. From the logs I can gather that as operation ILIKE '%1%' is used when filtering for true and ILIKE '%0%' when filtering for false. Of course ILIKE is a text operator not defined for booleans and furthermore the literals have to be booleans (true/false) as well instead of strings with wildcarded integer representations. So the DBMS rightfully rejects the queries.
(3) The default value of the column in the database isn’t taken into account when creating a new customer. The form shows the toggle in the false state. Trying to explicitly set the value changing data[] within callbackAddForm() as shown doesn’t work either. I also tried $data['wants_newsletter'] = 1; to no avail though. Maybe the root cause here is the same as (1).
How to work around these issues? Or what am I doing wrong?