Repeating fields is going to be awesome – but we need to decide how to store this data.
We have a few options:
- For each ‘repeatable’ field, have a separate table devoted to it that has these fields: id, related_id, and value (stored as the field’s type, like date, number, etc..)
- For each ‘repeatable’ field’s Pod, have a separate table devoted to it that has these fields: id, related_id, field_id, and value (longtext)
- Have a catchall table for ‘repeatable’ data called wp_pods_repeatable that has these fields: id, pod_id, related_id, field_id, and value (longtext)
- Store repeatable field data within the same ‘pod’ table such as wp_pods_tbl_mypod.my_field_name (longtext – json array of values)
I’m also considering an additional field to whatever table we figure out this data should go in called meta (longtext, extensible to include other data) but I’m not sure if that’s useful.
I’m really leaning towards option #1, but feel free to chime in here with any +1 or -1 comments.
UPDATE: Seems everyone is voting for #1, which seems to be the best option regardless of the caveats of the additional tables – it comes down to what is the best for the developer and the data itself.
What exactly are we talking about when we say “repeatable”. Does this mean that an individual pod can have multiple instances of this field? Or that the field can be used across multiple pods?
Mike
Right, a repeatable field is like adding a phone_number field and allowing multiple entries on it.
I assume this is similar to how custom fields can be set many times for a single post.
I say we go with JSON data which will allow us to either pull that into some Javascript or parse it with JSON_decode in php adn then loop over the results.
I’m afraid JSON has the drawback of hindering any intermediate to advanced SQL operations, and will really make it difficult for users to do a simple $pods->find($params) query. If we go with options 1, 2, or 3, we will have the same setup as PICK / File fields do for filtering / selecting – which would really be easier on the user as well as the dev’s many needs.
Yeah you’re right. I was just thinking in situations where I wouldn’t be putting in filterable data. -1 to the json storage
Trying to think of this in the most extreme way possible. What if, continuing on the phone number example Scott posted, you wanted to query for Pods that had a certain phone number entered (as one of the repeatable fields). Not sure why that’d ever come up, but one of the main reasons I like Pods so much is the ability to do crazy queries very quickly and easily. Storing JSON would cut that out. That said, I’m thinking +1 on #1 so far.
Yep, SQL magic would be best suited for #1.
Yeah, I know that (especially after building the Habitat volunteer system) there are some crazy wacky things I will likely have to do as time goes on. #1 would probably suit that the best.
Plus the data is stored as the real type of data it is in the DB, making it easy again on the SQL side to do more with less.
#1 is also roughly the way Drupal handles their ‘repeatable’ fields in CCK from what I know. Of course, they combine when a field of the same name / type is put on multiple content types.
+1 for 1, since it makes the most sense to me
Also didn’t mention it, but once this is figured out, we could modify to allow for ‘repeatable field groups’ which could be for things like ‘Address’, ‘Contacts’, or other things that are disposable (only used for that pod item).
@jchristopher is going over the UI adjustments to make this easy to use, more to come soon on this specific implementation.
We decided to go with the nesting methods that WP uses for parent pages.
I want so badly to accept #3, since I don’t like the idea of adding extra tables for each repeatable field. However, the trade off of longtext against any other specific data type is too much for me.
+1 for #1
Yeah, the caveat is there’s a table for each field but the benefit is the data is stored the right way and there’s not as big of a ‘lookup’ vs a big table to rule them all.
But if Drupal can do it, I don’t see why we can’t. The user doesn’t even need to know, the UI will be very easy to use and won’t require much work from the implementation side.
+1 for #1, sounds like it will do the job and will be easy to access as well as maintain.
+1 for #1. It seems to make the most sense and provide the most flexibility.
+1 for a modded #2 where value would be stored the same as in #1: “stored as the field’s type, like date, number, etc..” If we are going to put in a bit of overhead, we might as well add in some more flexibility/programmability for our advanced users.
Maybe you misunderstood, #1 means separate tables for each repeatable field, #2 means separate tables for each ‘pod’ that stores data for all it’s repeatable fields – so the mod you stated wouldn’t work because it would effect all the other fields too.
Indeed, didn’t read right. I vote for #1.
+1 for #1 !! Having more tables isn’t always a bad thing especially when Pod’s has to stay consistent in the way you access data and also as flexible as possible.
Seems everyone is voting for #1, which seems to be the best option regardless of the caveats of the additional tables – it comes down to what is the best for the developer and the data itself.
For Post Types / Users / Comments, if the option is set to store in the meta table, there will need to be a decision at some point to figure out how to allow for the Pods::find($params) to work correctly. I’m going to say that it probably won’t be a big priority, as people can utilize the core WP functions for access to that data. If they choose table-based storage then they can utilize the Pods::find($params) in a more complex way.