Hello @NOLO ,
I don’t know the structure of the tables, so I’ve guessed it is something like this:
-- Brands table
CREATE TABLE brands (
brand_id INT AUTO_INCREMENT PRIMARY KEY,
brand VARCHAR(100) NOT NULL
);
-- Models table
CREATE TABLE models (
model_id INT AUTO_INCREMENT PRIMARY KEY,
model VARCHAR(150) NOT NULL,
brand_id INT NOT NULL,
features TEXT,
FOREIGN KEY (brand_id) REFERENCES brands(brand_id)
);
-- Inventory table
CREATE TABLE inventory (
id INT AUTO_INCREMENT PRIMARY KEY,
serie VARCHAR(100) NOT NULL,
bien VARCHAR(50), -- e.g. "Laptop"
model_id INT NOT NULL,
user VARCHAR(100),
FOREIGN KEY (model_id) REFERENCES models(model_id)
);
Don’t check specifics, just the fields of tables really. So from my understanding you have a middle table that connects everything. The structure that I’ve tried is not even postgres but mysql but I believe that I’ve got the way that you want to actually implement it.
So from the above it seems that you want to use setRelation instead but it is just that the functionality for setRelation to support also a second setRelation (in our case brand) it is not possible on Grocery CRUD. I’ve tried to find a workaround for now but it will require a custom model.
So there are 3 steps that you need to follow.
Step 1.
Only for this CRUD (models) add the configuration
$config['optimize_sql_queries'] = false;
This is making sure that you will always have the join parameter since you will need it for the brand
Step 2. Create a custom model that will look like this:
<?php
namespace customModels;
use GroceryCrud\Core\Model;
use Zend\Db\Sql\Select;
class CustomModel extends Model
{
public function setColumns($columns) {
// Remove 'brand' from $columns
$columns = array_filter($columns, function($column) {
return $column !== 'brand';
});
$this->_columns = $columns;
}
/**
* Add extra join statements to include the brands table
* This method is automatically called by the parent Model class
*
* @param \Zend\Db\Sql\Select $select
* @return \Zend\Db\Sql\Select
*/
public function extraJoinStatements($select)
{
// Join with brands table using the brand_id from models table
$select->join(
['brands' => 'brands'],
'm.brand_id = brands.brand_id',
['brand' => 'brand'],
'left'
);
return $select;
}
}
3rd step:
Use the columns and add ‘brand’ in the list of columns. Also replace the relationNtoN with setRelation. So in our case it will be something like this:
$crud->columns(['serie', 'bien', 'model_id', 'brand' ,'user']);
$crud->setRelation('model_id', 'models', 'model');
So after all of those steps you would be able to see a table like this:
A full example of what I’ve checked is this:
...
$config = include('config.php');
$model = new \customModels\CustomModel($database);
$config['optimize_sql_queries'] = false;
$crud = new GroceryCrud($config, $database);
$crud->setModel($model);
$crud->setTable('inventory');
$crud->setSubject('Inventory', 'Inventories');
$crud->columns(['serie', 'bien', 'model_id', 'brand' ,'user']);
$crud->setRelation('model_id', 'models', 'model');
$output = $crud->render();
....
Let me know if a work-aroud like this works for you. I believe though that this can be a new feature that we can add in the future so it won’t require custom models.
Regards
Johnny
