Couple of questions on GC Enterprise "virtual" data

ekrueger · February 6, 2025, 6:56pm

Hi all,
I’m new to GC Enterprise 3.x with CI4, but have a history with GC 1.x and CI3 for a while now.

In working through the conversion of the code to the newer versions, I’ve come across a few questions, and hopefully straightforward to work out…

In the older GC software, I could define Column Callbacks for “virtual” columns, and those columns would be sortable/searchable in the datagrid. It appears that in GC 3, that is no longer possible. The only way I’ve figured out how to make this work is to do the ColumnCallback and also mapColumn for each of those “virtual” columns to one in the table. This made the info appear in the datagrid, but the sorting is tied to the column it is mapped to.

Ultimately, these extra data fields are just read-only data from another table. Consider the situation where the table we’re editing has a user# entry, and that refers to anther table where I get the user first/middle/last name components. It isn’t that I need to edit those name fields, but having them on the datagrid makes it way easier for someone to come along and know which entry to edit.

This worked great in GC 1, but doesn’t seem to be there in GC 3 to the same capacity.

I’m looking to be able to create “virtual” data fields for the insert/update/read fields. In this situation, the table contains a CountyID field (numeric). That is a pointer to another table that maps Counties to States, and then another table that maps States to Countries.

I’m looking to be able to define the State and Country fields, so that I can use pulldowns in the edit form, where you would first select the country, which in turn populates the State pulldown, and then select the State, which would populate the County pulldown, and then you select the County. (a numeric value – and the only value that is actually saved to the table).

Again, something I could do with Edit field callbacks in GC1, but in GC3, this creates an error with an unknown data type. I tried setting the field data type for those additional fields, but it didn’t resolve those errors.

I believe in both of the situations above, I could probably extend/customize the Model to allow for it to grab the extra columns from the related tables, and make it work, though not perfectly ideal. However, being newer to both CI4 and GC3.x, I’m struggling to figure out the best way to do this. Ideally I’d extend the GC3 Model so that I only need to modify the appropriate functions.
It appears, to me, that the GC3 Model is at:
app/Libraries/GroceryCrudEnterprise/grocery-crud/enterprise/src/GroceryCrud/Core/Model.php

However, what would the best practice for where to locate my version of the model?
app/Models/* ?

And if so, what would be the appropriate way to extend the model in that location (trying to work through the namespace issues correctly). The GC3 documents highlight this for CI3, which is fairly different from CI4. I understand the class functions, once the path and namespace/use/extends is all setup appropriately.

So a couple of wishlist items (unless I missed them on the documentation somewhere), and a HowTo (hopefully somewhat simple)

Thanks much in advance for the help/info.

Erick

ekrueger · February 8, 2025, 1:47pm

Hey Johnny, I was reading your response on another topic regarding the multiple fields in a 1:1 relationship – very similar to what is going on here that I need…and the suggestion of just using DB Views to get to the data. While I wasn’t exactly keen on that originally, I’m beginning to think that this is probably the best thing for me to do overall. The code changes to support that is actually really trivial as I already have callbacks on Read/Insert/Update anyways.

Ultimately, what I’m doing is that the DB contains different kinds of records, and I have 100s of volunteers who are editing those records to improve the content. When they want to ‘edit’, there is a button in the record to clone that record to an ‘Updater’ table. From there, that’s where grocerycrud comes to play. They edit the record, and mark it ready for publishing. Then a different group of volunteers reviews the work and hits the Publish (setActionButton) to push the updates back to the production record and remove it from the updater table. The first version of GC had worked really well for this, and I expect GC3 will as well.

Thanks,
Erick

johnny · February 10, 2025, 4:26am

Hello @ekrueger and thank you for your detailed description. If it wasn’t that detailed I wouldn’t understand your problem. To summarise it, I believe that 1:1 may resolve your problem but at the meantime, I am trying to help people to use they’re own custom models.

If you don’t mind, can you please share some insights of how this worked in Grocery CRUD version 1? Also if possible the specific controller or model you where using? If code is too sensitive, if you can please send it to me to info@grocerycrud.com would be great. I am wondering, since currently from older forum posts, it seems that people were struggling with joining two tables even with version 1 for example as per this forum post: JOINING Three (or more) Tables in Grocery CRUD - grocery CRUD forum

As I said on the other post, relation 1:1 is a bit tricky so I may end up creating a model example that you can add your own custom queries like this post here but let’s see .

But in general, I am trying to find the quick-win solutions that you can have a full working functionality now with some extra effort and have the big feature (e.g. 1-1 relation) in the future.

ekrueger · February 10, 2025, 2:23pm

Hey Johnny,
Thanks much for the note and looking into this.
Perhaps a bit of misunderstanding, it’s not so much that I’m specifically looking for the 1:1 relationships. Ultimately, while I’m looking to pull in additional details for the user, I’m only looking for GroceryCrud to update the 1 table – nothing on any sort of relationship.

Consider the following from GC1:

I am editing a table called “Patriots”.
This is the list of fields defined for GC1:
private $field_order = array(
‘PatriotID’,
‘VolunteerID’,
‘PatriotNamePrefix’,
‘PatriotNameFirst’,
‘PatriotNameMiddle’,
‘PatriotNameLast’,
‘PatriotNameSuffix’,
‘Birth’,
‘BirthPlace’,
‘Death’,
‘DeathPlace’,
‘SpouseName’,
‘Child’,
‘MilitaryRank’,
‘StateOfService’,
‘DARNum’,
‘QualifyingServiceSource’,
‘Bio’,
‘Citation’,
‘CemeteryID’,
‘FindAGraveCemeteryID’,
‘CemState’,
‘CemCounty’,
‘CemCity’,
‘CemAddress’,
‘CemName’,
‘FindAGraveMemorialID’,
‘CemLotNo’,
‘CemeteryGPS’,
‘GraveGPSLat’,
‘GraveGPSLon’,
‘TypeMarker’,
‘MonumentDate’,
‘CommentGravesite’,
‘Directions’,
‘Remarks’,
‘ReadyForApproval’,
‘ModificationTime’
);

In the above listing, all are fields within the Patriots table, with the exception of “CemeteryGPS”, which do not exist in any table.

I am able to then do:
$crud->callback_field(‘CemeteryGPS’,array($this,‘_CemeteryGPS_callback’));

which is:

public function _CemeteryGPS_callback($value, $primary_key) {
	$this->db->select("CemeteryGPSLat, CemeteryGPSLon");
	$this->db->join("Cemeteries", "UpdatePatriots.CemeteryID = Cemeteries.CemeteryID");
	$this->db->where("ID", $primary_key);
	$res = $this->db->get("UpdatePatriots")->result();
	if (is_null($res) || sizeof($res) == 0) {
		$CemeteryGPSLat = "n/a";
		$CemeteryGPSLon = "n/a";
	} else {
		$CemeteryGPSLat = $res[0]->CemeteryGPSLat;
		$CemeteryGPSLon = $res[0]->CemeteryGPSLon;
	}
	return '<span> <span name="CemeteryGPSLat" id="field-CemeteryGPSLat">' . $CemeteryGPSLat . '</span> , <span name="CemeteryGPSLon" id="field-CemeteryGPSLon">' . $CemeteryGPSLon . '</span> </span>';
}

Which, then on the Edit page, looks like:
[Image Removed]

The CemeteryGPS field is read-only, but it provided referential data for the user. In this specific case, I need the grave site GPS coordinates, and I display the cemetery coordinates, so that they can ensure they aren’t entering the same data for 2 different purposes.

Since the html form isn’t returning values for “CemeteryGPS”, I don’t have to remove them from the $post data prior to insert/update actions.

Now – when I tried doing the same thing with GC3, I get a dataType error on CemeteryGPS – even if I set the setFieldType for that field.

Another example…slightly different is where I want Column data, but not in the edit fields…
In this one, the datagrid looks like:

However, PatriotName* fields do not exist within the table for GroceryCrud…but instead, in a related table. It simply requires:
$crud->columns(‘ID’, ‘ProdID’, ‘VolunteerID’, ‘PatriotID’, ‘PatriotNameLast’, ‘PatriotNameFirst’, ‘PatriotNameMiddle’, ‘PatriotNameSuffix’,‘BioContribAuthor’, ‘BioText’);
and

		$crud->callback_column(‘PatriotNameLast’,array($this,‘_PatriotNameLast_column_callback’));
		$crud->callback_column(‘PatriotNameFirst’,array($this,‘_PatriotNameFirst_column_callback’));
		$crud->callback_column(‘PatriotNameMiddle’,array($this,‘_PatriotNameMiddle_column_callback’));
		$crud->callback_column(‘PatriotNameSuffix’,array($this,‘_PatriotNameSuffix_column_callback’));

and an example callback:
public function _PatriotNameLast_column_callback($value, $row) {
$positions = $this->db->get_where(‘Patriots’,array(‘PatriotID’=>$row->PatriotID))->result_array();
if ($positions) {
foreach ($positions as $items) {
$surname = $items[“PatriotNameLast”];
}
} else {
$surname = “”;
}
return $surname;
}

It wasn’t so much about having the datagrid search/filter functions work, as much as it was to just get the data there. I can make this one work fairly closely with the inclusion of the mapColumn, so that GC3 uses that other column for things like the dataType.

Another in the same table that I can’t do directly in GC3, is similar to the first one above, and the one I referenced originally:
In the Edit:

[Image Removed]

CountyID is a field within the table, but CemeteryState and CemeteryCountry are not.
They’re legitimate fields in other tables, but. the reason for including them here, isn’t to change any value in another table relationship, but instead, that they hold data to filter the CountyID to the right value – so you first select CemeteryCountry, and then it populates CemeteryState, and then you select that, and then it populates CountyID, which the user can select from the pulldown listing.
GC1 allows for doing this because of:
$crud->callback_field(‘CemeteryCountry’,array($this,‘_CemeteryCountry_callback’)); // pre-set this field based upon the user
$crud->callback_field(‘CemeteryState’,array($this,‘_CemeteryState_callback’)); // pre-set this field based upon the user

Whereas, GC3 throws a dataType error.
Since these are real form fields, I then have custom callback_update/callback_insert routines that remove these from the processing of this table:
// Reset FindAGraveCemeteryID == 0 ==> NULL
$crud->callback_update( function($post_array, $primary_key) {
// Unset field of CemeteryState as that does not exist in database.
unset($post_array[‘CemeteryState’]);
unset($post_array[‘CemeteryCountry’]);
if ($post_array[‘FindAGraveCemeteryID’] == 0) {
$post_array[‘FindAGraveCemeteryID’] = NULL;
}
return $this->db->update(‘UpdateCemeteries’,$post_array,array(‘ID’ => $primary_key));
} );

So, in GC3, I can mimick the behavior by using DB Views, and either linking to the data (the PatriotName* example above), or just creating dummy fields – at which point I can use callback Field routines to adjust/manipulate them as needed … but it’s more work to create those Views, and then have to modify the entire GC routine to use that view, change the update/insert to use the real table, and so forth.

Let me know if that helps. More than happy to send you additional information if that helps.

Thanks for taking the time to look into this.

Erick

ps I also sent this in email with the media items attached, since as a new member here, I’m restricted from including them in this posting. thanks!

johnny · February 10, 2025, 4:29pm

I see. So from what I understand for now the “fake” or “virtual” field type may work for you. It covers all the above scenarios from what I see. I will see if I can fix it fast enough. And of course for the future to have a 1-1 relation would be better but it will take longer. Till then I will also try to find you a work-around if possible so you will solve the issue with the errors that you currently have.

Regards
Johnny

ekrueger · February 10, 2025, 5:09pm

Absolutely. If I could insert “fake” columns and/or “fake” fields, I’d be in great shape.
I was hoping if I did a setFieldType on that “fake” field, that it would store that information and I wouldn’t get the dataType error…but not so lucky.

Thanks!

Erick

johnny · February 10, 2025, 6:23pm

Ok. So “virtual” field type it is. I like better virtual to be honest now

Till then I have created a custom model that will help you. So you just need to have the extra “fake” fields in the list of $customFields. You can use one custom model per $crud.

<?php

use GroceryCrud\Core\Model;
use GroceryCrud\Core\Model\ModelFieldType;

class CustomExampleModel extends Model {

    public $customFields = [
        'a_test_field',
        'a_second_test_field'
    ];

    public function getFieldTypes($tableName)
    {
        $fieldTypes = parent::getFieldTypes($tableName);

        foreach ($this->customFields as $customField) {
            $fieldTypes[$customField] = new ModelFieldType();
        }

        return $fieldTypes;
    }

    public function getColumns() {
        $columns = $this->_columns;

        foreach ($this->customFields as $customField) {
            if (($key = array_search($customField, $columns)) !== false) {
                unset($columns[$key]);
            }
        }

        return $columns;
    }

}

You can use custom models by simply adding it like this:

$model = new customModel($db);
$crud->setModel($model);

As also per documentation.

So in our case it would be something like this:

include('CustomExportState.php');
...
$model = new CustomExampleModel($database);

$crud = new GroceryCrud($config);

$crud->setModel($model);
...

Of course it is better to add it in a Codeigniter Model folder and have it’s own namespace.

Let me know if the above worked for you so I know that at least you have a work-around for now.

Regards
Johnny

ekrueger · February 11, 2025, 10:45pm

Hi Johnny,
First off – thanks so much for the super fast response.
I like “virtual” over “fake” as well.

So it seems that as long as I get $db populated and pass that to the Model($db) for initialization, that the rest of it works as expected. (using what you have for CI4 docs:
$db = $this->_getDbData();
and then passing that $db variable to the model initialization

It seems like the regular $config can remain the same, as long as I use the new model? Just making sure.

I need to work with this a bit now and see how it handles everything, but I think it’s super solid progress – for sure.

Thank you!

Erick

ekrueger · February 11, 2025, 11:06pm

Hey Johnny,

So the first thing I’m seeing is that for the “virtual” fields, the callbackEditField isn’t being honored, so the entry is “blank” on the Edit page vs what I have in that callback.

Thoughts ?

Erick

johnny · February 13, 2025, 5:54am

Hello @ekrueger ,

You are right! I didn’t expected that to happen to be honest. You can extend your custom model by adding the below function:

    public function getOne($id) {
        $row = parent::getOne($id);

        if ($row === null) {
            return null;
        }

        foreach ($this->customFields as $customField) {
            $row[$customField] = '';
        }

        return $row;
    }

So your final Model will look like this:

<?php

use GroceryCrud\Core\Model;
use GroceryCrud\Core\Model\ModelFieldType;

class CustomExampleModel extends Model {

    public $customFields = [
        'a_test_field',
        'a_second_test_field'
    ];

    public function getFieldTypes($tableName)
    {
        $fieldTypes = parent::getFieldTypes($tableName);

        foreach ($this->customFields as $customField) {
            $fieldTypes[$customField] = new ModelFieldType();
        }

        return $fieldTypes;
    }

    public function getColumns() {
        $columns = $this->_columns;

        foreach ($this->customFields as $customField) {
            if (($key = array_search($customField, $columns)) !== false) {
                unset($columns[$key]);
            }
        }

        return $columns;
    }

    public function getOne($id) {
        $row = parent::getOne($id);

        if ($row === null) {
            return null;
        }

        foreach ($this->customFields as $customField) {
            $row[$customField] = '';
        }

        return $row;
    }

}

ekrueger · February 13, 2025, 1:43pm

Thanks! That seems to fix it.
I figured there was at least 1 or 2 more functions that needed to be handled, but not familiar enough with the code to just know, and hadn’t traced through it in great detail.

Thanks for that.

Erick