Couple of questions on GC Enterprise "virtual" data

[ekrueger]

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…

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

2. 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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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

[johnny]

Hello @ekrueger ,

I am glad to inform you that the latest version (3.1.14) has now the field type ‘virtual’ which you can use.

I didn’t have the time for the 1-1 relation but I will try to prioritize it for the next release.

Let me know if that worked for you.

Regards
Johnny

[ekrueger]

Thanks Johnny…I’ll update and let you know how it goes.
Thanks much for the quick attention to this.

Erick

[ekrueger]

Hi Johnny,

In working with this, I can define the fieldtype as “virtual”, and that clearly makes it valid and stops the errors that were originally happening.

The only issue I’m seeing is that it doesn’t appear that the callback*Field routines are working?

Something as simple as:
$crud->fieldType(“CemeteryGPS”, “virtual”);
$crud->callbackReadField(‘CemeteryGPS’, function ($fieldValue, $primaryKeyValue, $rowData) {
return ‘IN HERE’;
});

on the actual Read form, it is a blank area.

Either I’m doing something wrong, or the callback isn’t working?

Thanks much,

Erick

[ekrueger]

fyi, when I added that field as a column to the table, the callback worked exactly as expected, but without the column in the db table (just defined as a virtual field), the callback doesn’t do anything.

[johnny]

Hello @ekrueger ,

Unfortunetly, I can’t reproduce the issue that you are referring to. I have tried all different field callbacks and they are all working as expected. For example:

$crud->callbackReadField('a_test_field', function ($fieldValue, $primaryKeyValue, $rowData) {
    return 'IN HERE';
});

$crud->callbackAddField('a_test_field', function ($fieldValue) {
    return '+30 <input name="a_test_field" value="' . $fieldValue . '"  />';
});

$crud->callbackEditField('a_test_field', function ($fieldValue, $primaryKeyValue, $rowData) {
    return '+30 <input name="whatever" value="' . $primaryKeyValue . '"  />';
});

Are all returning the expected results. Also the callbackReadField is returning the IN HERE as expected:

[ekrueger]

Hey Johnny,
Yeah, I’m definitely puzzled right now.
It accepts setting the field type as virtual, without any issues.
It just isn’t firing on any of the callback routines, and no idea why at this point.
I originally thought it was something in my syntax, but changing the table definition from the real table that requires that field to be virtual, to a db view, where the column is defined, the callbacks work without any issues.

Even tried reinstalling 3.1.14 just to make sure I had the latest bits – which seems to be the case.

Still trying to figure out why this isn’t working as expected.

…

still banging on this one…for whatever reason, the callbackEditField() just isn’t being called for the virtual field.

I define it as a part of
$crud->fields(…)
set the fieldType to virtual
then setup the callbackEditField and regardless of it being a function or a closure, it isn’t being executed.

I put in some log_message()'s into the other callbackEditField() routines, and it is logging that it enters that function, but it isn’t for the virtual field.

Not sure why when I change to the DB view (that has that field defined), that the callback works without any issues, but as soon as it is using the real db table (where that field doesn’t exist), the callback suddenly doesn’t function.

[ekrueger]

Johnny, some more info to see if we can figure out why this isn’t working for me…

I defined a new virtual field, “virtual_test”.
If I do not do any callback’s, on the Edit form, I see this field listed, and a text input box is there to capture the input — exactly how I’d expect it to be.

If I define the callback:
$crud->callbackEditField(‘virtual_test’, function ($fieldValue, $primaryKeyValue, $rowData) {
log_message(“error”, “In callbackReadField”);
return ‘IN HERE’;
});

The Edit form still lists the field name, but there is no output to the value side.
When I look at what’s happening from the browser side, here’s the payload response for the Edit Form.
As you can see, the last field entry has no value associated with it. (Same true for the CemeteryGPS field at the top, which is also virtual).

I even tried things like
$crud->fieldType(“virtual_test”, “virtual”);
vs
$crud->fieldTypeFormFields(‘virtual_test’, ‘virtual’);

with no change in behavior.

But, if the field is something actually found in the DB table, then the callbacks are executed exactly as expected.

If I dump the values of $crud right before the initial render of the datagrid, I see what I think are the callbacks loaded into that data structure…

"fieldTypesEditForm":{
"virtual_test":"isNullable":false,"dataType":"backend_callback","defaultValue":null,"permittedValues":null,"options":null,"isRequired":false,"isReadOnly":false,"isSearchable":false,"hasOrdering":false},
"CemeteryGPS":{"isNullable":false,"dataType":"backend_callback","defaultValue":null,"permittedValues":null,"options":null,"isRequired":false,"isReadOnly":false,"isSearchable":false,"hasOrdering":false},
"VolunteerID":{"isNullable":false,"dataType":"backend_callback","defaultValue":null,"permittedValues":null,"options":null,"isRequired":true,"isReadOnly":false,"isSearchable":false,"hasOrdering":false},
"FindAGraveCemeteryID":{"isNullable":false,"dataType":"backend_callback","defaultValue":null,"permittedValues":null,"options":null,"isRequired":false,"isReadOnly":false,"isSearchable":false,"hasOrdering":false},
"CemeteryID":{"isNullable":false,"dataType":"backend_callback","defaultValue":null,"permittedValues":null,"options":null,"isRequired":false,"isReadOnly":false,"isSearchable":false,"hasOrdering":false},
"DARNum":{"isNullable":false,"dataType":"backend_callback","defaultValue":null,"permittedValues":null,"options":null,"isRequired":false,"isReadOnly":false,"isSearchable":false,"hasOrdering":false},
"GraveGPSLat":{"isNullable":false,"dataType":"backend_callback","defaultValue":null,"permittedValues":null,"options":null,"isRequired":false,"isReadOnly":false,"isSearchable":false,"hasOrdering":false},
"GraveGPSLon":{"isNullable":false,"dataType":"backend_callback","defaultValue":null,"permittedValues":null,"options":null,"isRequired":false,"isReadOnly":false,"isSearchable":false,"hasOrdering":false},

Actually, in looking at the above, the output to the browser seems like it might be missing a set of braces? Looking at:
“virtual_test”:“isNullable” …
-vs-
“VolunteerID”:{“IsNullable” …

Just seeing if that is something ?

Thanks

[johnny]

That is very weird, I believe that this should be the problem. I mean the bracket. I can’t reproduce it locally though. Do you mind sending me a zip file at info@grocerycrud.com with the your project to see if I can reproduce it with your source code?

[ekrueger]

Hey Johnny,
Yeah, it was strange for sure.

I can share the controller, view and the table schema so you have that in an email.
There’s a lot going in, and the controller code is a bit of a mess with the conversion from CI3 to CI4 and grocery crud 1.x to 3.x – so lots of stuff just commented out, but I’ll explain it in the email.

Thanks for giving it a look.

Erick