Unique IDs and Custom Tables

This topic has 11 replies, 2 voices, and was last updated 11 months, 3 weeks ago by Peter.

Viewing 12 posts - 1 through 12 (of 12 total)

Author

Posts
November 26, 2023 at 12:04 AM #43925
Bluemoon75
Participant
Hi Support,

I don't recall seeing this exact issue in the docs/samples or forums, but I may have missed it. With fields requiring a Unique ID, is there a way that I could still create my custom tables in the way most DB Schemas are created. For example I would like to create my tables in the first format below. However when I do that I run into issues with trying to call the API functions (rwmb_meta) even when passing in the table args params.
```
API::create(
	"user", [
		'name' 			=> 'VARCHAR(255)',
		'address' 		=> 'VARCHAR(255)'],
	['name'],   
	true 
);

API::create(
	"company",[
		'name' 			=> 'VARCHAR(255)',
		'address' 		=> 'VARCHAR(255)'],
	['name'],   
	true 
);
```
If I create them in a unique pattern the API seems to work, but I would prefer to not have to prefix each column name.
```
API::create(
	"user", [
		'user-name' 		=> 'VARCHAR(255)',
		'user-address' 		=> 'VARCHAR(255)'],
	['name'],   
	true 
);

API::create(
	"company",[
		'company-name' 			=> 'VARCHAR(255)',
		'company-address' 		=> 'VARCHAR(255)'],
	['name'],   
	true 
);
```
Is their a pattern/setting/API that I'm missing so I can use the first schema pattern?

Thanks

Add New Note to this Reply
November 26, 2023 at 4:58 AM #43928
Bluemoon75
Participant
Hi,

I figured this out. If I just use the \MetaBox\CustomTable\API::get call then I can get back what I need and the code stack looks to be lighter than rwmb_meta. Do you have any plans to enhance the API with calls like?
```
\MetaBox\CustomTable\API::getAll
\MetaBox\CustomTable\API::getByIDs(array IDs)
```
Thanks for your time!

Add New Note to this Reply
November 26, 2023 at 8:43 PM #43933

Peter
Moderator

Hello,

However when I do that I run into issues with trying to call the API functions (rwmb_meta) even when passing in the table args params.

What exactly are the issues? And let me know the code that you use to call the API function.

Add New Note to this Reply

November 28, 2023 at 12:41 AM #43948
Bluemoon75
Participant
Hi Peter,

The function rwmb_meta does not work when 'addresses' is a serialized array (aka group box) and the data is coming from a custom table.
```
//BROKEN: Returns empty array
$args = [
    'storage_type' => 'custom_table',
    'table'        => 'company'
];
$data = rwmb_meta('addresses', $args, 4872);

//WORKS: The full object (table row) is returned with the correct serialized array 'addresses'
$data = \MetaBox\CustomTable\API::get( 4872, 'company');
```
Note: rwmb_meta DOES return data correctly if its not serialized from a custom table, OR if the data is from the wp_postmeta table then the serialized value will work. Hope that helps clarify the issue.

Thanks

Add New Note to this Reply
November 28, 2023 at 3:42 AM #43951

Bluemoon75
Participant

One other point I need to mention. Within all my custom field groups I use the id 'addresses' for both my users and by company custom groups and table column names. If I change one of them to say 'addresses-1' then the rwmb_meta functions works.

This indicates that rwmb_meta can only work with one unique id, while the API works differently. If this is the underlying architecture, I assume that is fine, However it doesn't seem very consistent with the fact that you could pass in a custom table to the function but the function is really tightly coupled with the uniqueness of the ID and not the uniqueness of the table column of the args passed in, also the fact that the API works different than the rwmb_meta, seems a little awkward. Please let me know if this is an intended use of the function or if it's a possible bug.

Thanks

Add New Note to this Reply

November 28, 2023 at 10:26 PM #43957
Peter
Moderator
Hello,

I test this case again on my demo site and see the helper function rwmb_meta() still returns the serialized value correctly. By default, the object type is post. If you use the user meta, please pass the object type user to the argument:
```
$args = [
    'storage_type' => 'custom_table',
    'table'        => 'company',
    'object_type'  => 'user'
];
```
The API function works because it uses the SQL query and query directly in the database while using the helper function rwmb_meta() there are some data types added to this. Please follow the documentation
https://docs.metabox.io/extensions/mb-user-meta/#getting-field-value

Add New Note to this Reply
November 28, 2023 at 11:12 PM #43959

Bluemoon75
Participant

Hi Peter,

The object type I'm testing with is post, user is not involved in any way even though I have a table named user. As I mentioned earlier I can return the value but only when the field Field Group ID is unique across all field groups. If I have 'addresses' on both user and company field ID then rwmb_meta doesn't work for serialized objects. If I change it to 'addresses-foo' for either one then it does work.

Did you setup your environment as Field Group User (table user), Field Group Company (table company), then 'addresses' field ID for both User and Company Field groups? Also keep in mind that just because I have a table and group named user doesn't imply that it correlates to the object_type user. Anyways that's how I have it setup and can readily reproduce the issue, but only with rwmb_meta(). Simply doing a simple serialized test against a field group ID is not got to reproduce the issue.

Thanks~

Add New Note to this Reply

November 29, 2023 at 9:19 PM #43962

Peter
Moderator

Hello,

Yes, I add a group field "addresses" with the same ID in two field groups (meta box). Please export your field groups to JSON files and share them here. I will import them to my site and check this issue.

Here are field groups on my site.
https://drive.google.com/file/d/1mDJORfNw9bJLcP6N2aBroji3UdHP_ioO/view?usp=sharing
https://drive.google.com/file/d/10pZmv9m5_kv1tHC06doQkVDLe67nTTTA/view?usp=sharing

Add New Note to this Reply

November 30, 2023 at 12:46 AM #43964

Bluemoon75
Participant

Hi Peter,

That is not same setup I have.

Here is the layout I have. Attached in the zip file are:
1. The custom post-type (tools)
2. The group fields (Company/User)

Note I'm using the custom table option to store my data. Note if you try to save a user and company the company data gets saved to the users data when using the wordpress forms.

https://www.dropbox.com/scl/fi/zhrclhuhsmptqhqk859bo/post-and-group-json.zip?rlkey=q17p5e5n4b4o536laqln7so2c&dl=0

Add New Note to this Reply

November 30, 2023 at 8:55 PM #43974

Peter
Moderator

Hello,

I see the issue. You have two group fields with the same ID on a page, so the value of the last group will be saved and the value will display in all group fields after saving. It is expected behavior.

On the same page, each field needs to have a unique ID. It is noted in the documentation
https://docs.metabox.io/field-settings/

Field ID. Required and must be unique. It will be used as meta_key when saving to the database. Use only numbers, letters, and underscores (and rarely dashes).

Add New Note to this Reply

December 3, 2023 at 10:25 PM #43991
Bluemoon75
Participant
Hi Peter,

Thanks for validating that! I would think the UI would be more helpful with letting the users know there are IDs that are the same, a simple warning message should show up for those scenarios, especially with larger systems.

Toolset has the same layout but they do warn users, probably something that should be added to the feature request for MB, especially if causes breaking changes for the user.

When working across large sets of custom tables its common for column names to overlap. At least within the custom table ecosystem it would be nice to be able to have the same column names i.e
```
user.address
company.address
```
Now I have to come up with something like
```
user.address
company.c-address
```
Which defeats the notion of good database schema design. I hope you can pass along these recommendations to your development team to consider for improvements in the future.

Thanks

Add New Note to this Reply
December 4, 2023 at 9:03 PM #43999

Peter
Moderator

Thanks for your feedback.

I will inform the development team to consider supporting this feature in future updates.

Add New Note to this Reply
Author

Posts

Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic.