Tableless data and Advanced Content Types: no reference in tables

[paddelboot]

Issue Overview

Certain field types are not represented in an Advanced Content Type (ACT) table. This makes it impossible for a non-WordPress application to reliably retrieve Pods data.

Expected Behavior

There should be - for "tableless data" fields - some reference in the table of an Advanced Content Type. The data could then be JOINed in a SQL query or something similar to get the complete Pod data.

Current Behavior

Right now, I need Pods functions to query all data by an ACT Pod. But this does not align well with the description of ACTs: "You need your data to be separate from your WordPress database tables. This might be necessary, for example, if you are using another piece of software with which you wish to share the database. Advanced Content Types means that you can interface between WordPress and that software."

Steps to Reproduce (for bugs)

1. Create Advanced Content Type Pod with tableless data field, ie. Image/File field.
2. Check database table and see no reference for this field

Solution (from @sc0ttkclark)

• PodsField_Pick::options: Add a new boolean option static::$type . '_ghost_storage' with 'depends-on' => array( static::$type . '_object' => 'table', static::$type . '_format_type' => 'single' ) that defaults to 0
• PodsAPI::save_pod_item: When a relationship field value is saved, we exclude relationship/file fields from being saved to the table columns; We need to include them, but only if the pod is table-based and the {$type}_ghost_storage field option is enabled.
• PodsAPI::save_field: When a relationship field is created/saved, we exclude relationship/file columns from being added to the table for table-based pods; We need to include them, but only if the {$type}_ghost_storage field option is enabled.

WordPress Environment

``` Debug Information

WordPress Version: 4.9.8

PHP Version: 7.2.9-1+ubuntu16.04.1+deb.sury.org+1

MySQL Version: 5.7.23

Server Software: Apache/2.4.34 (Ubuntu)

Your User Agent: Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:61.0) Gecko/20100101 Firefox/61.0

Session Save Path: /var/lib/php/sessions

Session Save Path Exists: Yes

Session Save Path Writeable: Yes

Session Max Lifetime: 1440

Opcode Cache:

Apc: No
Memcached: No
OPcache: Yes
Redis: No

Object Cache:

APC: No
APCu: No
Memcache: No
Memcached: No
Redis: No

WPDB Prefix: nx24_2_

WP Multisite Mode: Yes

WP Memory Limit: 64M

Pods Network-Wide Activated: No

Pods Install Location: /media/Entwicklung/Entwicklung.xxx/www/v2/wp-content/plugins/pods/

Pods Tableless Mode Activated: No

Pods Light Mode Activated: No

Currently Active Theme: xxx Interface Theme

Currently Active Plugins:

Loco Translate: 2.1.5
xxx Interface: 0.8.3Beta
P-Debug: 0.1a (Alpha)
Pods - Custom Content Types and Fields: 2.7.9
Restrict User Access: 1.0.1

</details>

## Pods Package Export (helpful!)
<!-- Please include the Pods Package Export as it will be helpful in reproducing your issues. -->
<!-- To access: Pods Admin, Components, Migrate: Packages, Enable. -->
<!-- Then Pods Admin, Migrate: Packages, Export and Copy and Paste the JSON here OR -->
<!-- Save to a text file and upload to this bug report -->

Copy and Paste the JSON Export from Pods Admin, Migrate: Packages, Export in WordPress admin here

## Workaround or Alternate Solution Until Bug is Addressed
Use pre save hook to set a value of a "number" field to match the relationship/file field.

[quasel]

Yes and no ;) - even if we stored the ID of the "related" item the external application would still have to know which other tables to join as "files" are always stored in the media library and other relationship type fields point to other "ACT / CPT" ID's so maybe it'S only a small advantage? Or did I miss something?

Possible other workaround maybe use the rest-api ;) or for files just store a link and avoid the media library? All deepens on the use case!

Relationship information is stored:
https://pods.io/docs/database-reference/database-storage-relationship-fields/

Maybe a question for @sc0ttkclark I can remember talking about it but can't find the issue!

[paddelboot]

I don't want to sound like a bigot, but for me the entire point of Advanced Content Types is to have an interface to a non-WP application. That is: having the convenience of backend data management that is provided by Pods, and having the flexibility of any layered architecture that queries that data with SQL. The REST API doesn't work with those content types apparently. So yes, I feel a bit disappointed, because I built an entire app on top of that premise. Maybe I should have done more research on the limitations of ACTs.

Having the option to simply store an img/file link in the ACT table would certainly be a useful workaround.

even if we stored the ID of the "related" item the external application would still have to know which other tables to join as "files" are always stored in the media library and other relationship type fields point to other "ACT / CPT" ID's so maybe it'S only a small advantage?

Right now, I don't even know that there might be a relationship field in the Pod.

[quasel]

e.g images are "relationship type" you can store the link easily in a field of type "website" it's for links!

furthermore if you want to go the REST API Route #5126 - or create an proper endpoint for the rest api!

My personal opinion - ACT are from the days where CPT didn't exist in WP if you want to utilize the full admin power of WordPress and have easy external access I would go the CPT + REST API Route there are plenty of resources out there! Sorry to disappoint you :(

ACT have their use cases but if it comes to regular full table changes or maybe some external ID stuff or… but @sc0ttkclark or @pcfreak30 are best to seek too for that ^^

[pglewis]

My personal opinion - ACT are from the days where CPT didn't exist in WP if you want to utilize the full admin power of WordPress [...]

Integrating with external apps outside the WordPress stack-- like in this case-- is definitely another major use case. I don't think there is much else out there that can fill that hole without a lot more elbow grease.

This is definitely one for @sc0ttkclark. I see the use case but "tableless" data was specifically meant as a quick and dirty way to provide a few common data collections without having the overhead of DB retrieval to get to them.

[paddelboot]

I would go the CPT + REST API Route there are plenty of resources out there!

This is also what I would do now. Even though querying post types and meta fields is more of a hassle than just a simple query on a table that maps the Pod 1:1.

I feel that if Pods managed to close this gap with the tableless data, it would become a lot more useful for enterprise level applications, mobile apps and whatnot. More or less what the WP REST API is trying to do.

[paddelboot]

e.g images are "relationship type" you can store the link easily in a field of type "website" it's for links!

The application is for paying customers of a call center, I can't expect them to copy&paste their company logo image link into a form field, unfortunately.

[pglewis]

As a brute force workaround you could probably gather the data Pods is using and populate a static ACT with the data to be used without too much trouble. Obviously not ideal but it's a one-time thing and then it's good to go, at least.

[paddelboot]

As a brute force workaround you could probably gather the data Pods is using and populate a static ACT with the data to be used without too much trouble.

You mean some kind of cache?

[pglewis]

I can't expect them to copy&paste their company logo image link into a form field, unfortunately

My mistake on the above, I was assuming some of the built-in tableless fields like "Countries"

[pglewis]

I'd honestly forgotten about some of the others that fall under "tableless"

[paddelboot]

My personal opinion - ACT are from the days where CPT didn't exist in WP

It could be so much more than that - almost more useful than native WP post types, from a certain perspective.

[paddelboot]

I know you are all very busy, but is there any update on how this might be updated in future versions? Being unable to query pod tables from an external application with ie. an ORM is hampering me quite a bit, because I'm stuck to using it within the Wordpress ecosystem.

[sc0ttkclark]

A temporary solution would be that you create an alternative Number field (0 decimal), then filter the pre save hook to set the number field to match the image / relationship field.

I understand that you are a little disappointed that relationship/file fields are not stored in the table we store other fields in, this has been something I personally have wanted over the years too. I'm glad I'm not the only one, there's probably another GitHub issue somewhere about this. The general idea is what I've termed "Ghost relationships" which would store relationships into the table itself.

We don't query relationships on that table, we query them on wp_podsrel which makes it possible to do more efficient querying across all of the different types of content. That means the only places we need to handle this are:

• PodsField_Pick::options: Add a new boolean option static::$type . '_ghost_storage' with 'depends-on' => array( static::$type . '_object' => 'table', static::$type . '_format_type' => 'single' ) that defaults to 0
• PodsAPI::save_pod_item: When a relationship field value is saved, we exclude relationship/file fields from being saved to the table columns; We need to include them, but only if the pod is table-based and the {$type}_ghost_storage field option is enabled.
• PodsAPI::save_field: When a relationship field is created/saved, we exclude relationship/file columns from being added to the table for table-based pods; We need to include them, but only if the {$type}_ghost_storage field option is enabled.

[paddelboot]

I'm also glad I'm not the only one who is interested in this. With cross-platform-requests and powerful frameworks outside of WP, this would add a lot of value.

I will try your workaround solution for sure. If this works, I would be able to finally do a simple SQL query on those tables, which would be great, to say the least. The only downside is that whenever my client adds a new relationship field, I will have to add some code.

Since there's no reference to relationship fields in the table, I assume you would need to store the information of the "real" field setup of that pod somewhere. Then use that information to do the actual query on both the pod table and _podsrel. So I was wondering where that information is actually stored?

[sc0ttkclark]

Files/Relationships are always stored in wp_podsrel https://pods.io/docs/database-reference/database-storage-relationship-fields/ using the Pod ID, Field ID, Item ID (related from) and Related Item ID (related to). That's where it gets stored normally, which is what Pods uses when it builds it's queries for find(). It allows you to query on relations like 'where' => 'related_actors.movies.genre.name = "Sci-Fi"' and it handles all of the wp_podsrel SQL joining automatically.

[quasel]

for reference if this is done wouldn't it make sense to move the relationship information for table storage in a separate column from meta storage too?

[sc0ttkclark]

For a meta-based pod, it remains the same. This would only be for table-based pods. Meta-based pods already save their relationships to meta (technically ghost storage but only for meta).

[paddelboot]

@sc0ttkclark Any estimate on when "Ghost relationships" for table based pods might be implemented?

[sc0ttkclark]

I put together a tasklist which should help get it done more easily -- however I do not have a timeline or a set Pods release it's tied to. My Pods time has been split between Pods 2.8 and the upcoming Pods Gravity Forms add-on update.

I could probably knock this out in the next few months unless someone else puts together the PR using the details in the tasklist.

[paddelboot]

This is biting me in the butt right now again. I did some testing with your workaround:

A temporary solution would be that you create an alternative Number field (0 decimal), then filter the pre save hook to set the number field to match the image / relationship field.

The problem is that I would need to update/save all the pod items in order for the mapping field to be populated. This is not impossible, but not very convenient either. @sc0ttkclark

[sc0ttkclark]

This solution is not super efficient for bidirectional relationships, since you'd have to go through and save the current ID on all related items too. Even our eventual solution for this would have to loop through all related items and save each individually to remain hook-able (maybe something we could give an option for bulk or individual edits, depending on how efficient the developer wants saving to be).

[paddelboot]

You're right. We have 50+ Pod items and will update them manually, luckily no bidirectional fields, just ordinary image/file fields. The benefit of being able to perform a SQL query on those Pod tables outweighs this effort, I think.

[sc0ttkclark]

Oh you're talking about retrofitting this solution into existing data. Yeah, that's something you could trigger via code that loops through the items via ->find() and runs a ->save() by passing the current relationship field value into the relationship field for saving again. That might make it easier especially if you've got a ton.