0

I am developing a website where I have modified the comment form to have review rating capabilities. I ask them to rate them on food, service and location. For each of those option there is “bad”(1),”okay”(2),”Good”(3). I store each in a custom comment field like the following “review_service”=1 & “review_food”=3.

Right now when a page loads “get_comments” is used to get all the custom comment fields and count them in the following array. The array is used to display results.

Array
(
[food] => Array
    (
        [1] => 3
        [2] => 1
        [3] => 1
    )

[service] => Array
    (
        [1] => 1
        [2] => 2
        [3] => 2
    )

[location] => Array
    (
        [1] => 1
        [2] => 3
        [3] => 1
    )

)

In future I want to show locations listed in order of counts. I’m sure using ‘get_comments’ through all posts is not efficient. I thought of two ideas.

  1. Store the array (serialized) in a post custom field for the location. Each time a review is done the array in the custom field is updated.

  2. Put the array in a custom table like below. EDIT: I forgot to add the option column.

    |Id | post_id | option_id |option|option_count|

    |1 |1433 |food |bad |5 |

    |2 |1433 |food |good |3 |

    In terms of speed/efficiency which option should I choose?

0

Since you plan on retrieving and displaying the data ( and most likely wanting to sort it ) then you should store your reviews data in a custom table.

All of these fields could be set as integer only: id,comment_id,food,service,location

Then you could grab the data by joining this table to the comments table and the comments table to the posts table.

0

Storing data in a serialized array a is terrible way to store data unless all you want is to pulling out in a block. If you want to query the data contained in the serialized string you've got a mess. Don't do that. Store granular data.

Second, your "custom table" really looks like the commentsmeta table or the postmetatable. It makes not sense to duplicate that. WordPress does not have a Core function that I am aware of that will pull metadata by key alone and not tied to an ID (which is a limitation in Core) so you will have to write your own function to pull that data, but you would anyway.

See also:
https://wordpress.stackexchange.com/a/82585/21376
https://wordpress.stackexchange.com/a/88715/21376

4
  • Hi @s_ha_dum thanks your advice would help with the original table. But i realised that I forgot the 'option' column. With that additional column in place, Do you still recommend i use the postmeta table? If so, there will have to be 9 fields per post. e.g.meta_key: review_food_{good/okay/bad} – Adzay Nov 18 '15 at 23:49
  • 9 fields shouldn't be a problem. MySQL can handle a lot of rows, though with thought to data design I think you can reduce that number. – s_ha_dum Nov 19 '15 at 0:01
  • Okay thanks, yeah it's going to be a tough one. if in future i want to create a function that brings a post with the 'best option count' 9 fields may be best. if that doesn't work well, i won't be able to resist looking to a custom table. @s_ha_dum – Adzay Nov 19 '15 at 0:11
  • A different table won't do you any good unless you need a different table structure, seriously. Just write good queries and throw indexes if need, but only if. – s_ha_dum Nov 19 '15 at 0:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.