It seems that using JSON to store the extra fields is a little short sighted.

I want to be able to write a module / component that searches through my custom item types and pick out certain rows and use that information to do a webservice call.

However, in order to do this I need to select all items from a particular category, decode the JSON and filter on the results. Then I can use this to send some data off to my webservice.

And what if I want to query the database on the basis of the webservice call to pick out a row based on one of the extra fields? It can't be done with a simple select x,y,z from ....

Is it possible that we can store each extra field in a column on it's own rather than in one JSON field? It seems almost impossible to pull out selected pieces of information from the custom item type this way? It's all or nothing?

I see that the search plugin puts all search terms into a separate column separated bv comma. Can this be extended to put each extra field into a separate column?

Hi. This is how K2 works. However extra fields values are also stored in the field extra_fields_search of the items table. So you can do basic searching of extra fields using this field.

But why JSON? Why not each group in a separate table?

It means that I can't easily pick out specific rows without searching through all of the item types regardless. I.e. from an external webservice call or whatever.

It just seems to me that the JSON solution is a half way house. It works and it is neat but I would like to see a separate table at least for each item type.

Also, it is not possible (at present) to store HTML in the extra fields. So there is no 'HTML' extra field type. Will this be easy to code using the pulgin API or are there plans to incorporate it at some point?





Lefteris Kavadas said:Hi. This is how K2 works. However extra fields values are also stored in the field extra_fields_search of the items table. So you can do basic searching of extra fields using this field.

I know it's not germaine to the question, which I'm also curious about, but if you were to actually normalize this data to 1NF you wouldn't do it with a column per custom field or table per custom field, as implied in earlier posts. You would create one new table to represent the many-to-many relationship between items and extra fields. It would have three columns: item_id, extra_field_id, and value. I'm sure I'm not doing much to help the OP get his question answered, but anyway that's how you normalize a compound field.

Adam Rifat said:But why JSON? Why not each group in a separate table?
It means that I can't easily pick out specific rows without searching through all of the item types regardless. I.e. from an external webservice call or whatever.

It just seems to me that the JSON solution is a half way house. It works and it is neat but I would like to see a separate table at least for each item type.

Also, it is not possible (at present) to store HTML in the extra fields. So there is no 'HTML' extra field type. Will this be easy to code using the pulgin API or are there plans to incorporate it at some point?





Lefteris Kavadas said:Hi. This is how K2 works. However extra fields values are also stored in the field extra_fields_search of the items table. So you can do basic searching of extra fields using this field.

A little more homework and I turned up a very good explanation of why K2 uses JSON for custom fields. I'm just starting a project where I'll have about 1500 nodes and 50 custom fields, and the client needs filtering over pretty much all the fields. He says it's easier to search using a lot of LIKE clauses than joining a lot of tables together, and I guess I don't have any choice but to believe it. Let's go MySQL fulltext index!

That's an interesting post that you found, Phil. Interesting that in his opinion J1.6 will largely supercede K2.

I like K2 and I think if it spurs the J dev team into integrating custom item types then that can only be a good thing. I wanted to use K2 for a project but then realised that JSON and an indexed text field probably wasn't going to be the best way to go. I really needed custom item types.

I would've thought that joining well indexed tables would be faster than using lots of LIKE statements but I could be wrong.

There are some pretty decent text based search engines out there. Have you looked at Lucene for example? There is a PHP implementation of this and does a pretty good job of text based searching (and is pretty quick). Depends what are trying to do though.

Roll on J1.6 beta - I think this will be a great CMS...