1

I just want to add json data type in post meta field as like in normal database. json data will be in the structure below,

    Post Name                        Meta Data

   1.Sample title 1 --->       {name:Austin,score:40},
                               {name:Rock,score:60},
                               {name:Vinci,score:48}

   2.Sample title 2 --->       {name:Steve,score:25},
                               {name:Ricky,score:90}

   3.Sample title 3 --->       {name:Bichel,score:10},
                               {name:Ronaldo,score:78},
                               {name:Mathew,score:80}

The result What I needed is

 Sample title 2  Winner name Ricky and score is 90 // since 90 is highest among all the three posts
 Sample title 3 Winner name Mathew and score is 80
 Sample title 1 Winner name is Rock and score is 60

Check the above example

The post no.1 have 3 usernames and 3 scores, similarly post no 2, 3.. have same type of usernames and scores. I wish to sort (meta data) score orderby highest score then get posts so the result will be

    Post Name                        Meta Data

   1.Sample title 1 --->       {name:Rock,score:60},
                               {name:Vinci,score:48},
                               {name:Austin,score:40}

   2.Sample title 2 --->       {name:Ricky,score:90},
                               {name:Steve,score:25}

   3.Sample title 3 --->       {name:Mathew,score:80},
                               {name:Ronaldo,score:78},
                               {name:Bichel,score:10}

again I will fetch all the post and sort the posts according to previously sorted highest score. If it is a tedious query, At least I wish to take first index of every postmeta's score and order the posts by highest score.

What I have already implemented is stored this json as array in post meta. Get all the post ($paged= -1) and corresponding postmeta then sort postmeta from php side and order the posts. but as long as it is small db it can handle this load. But this db may go as big as 50k posts.

So I wish to move to some other implementation. Just like normal DB with JSON data type orderby is it possible?, If not suggest me a solution for it.

  • Why do you want to store this info as JSON? It makes it almost impossible to use it to sort items on DB level - and sorting them in PHP will be very ineffective. – Krzysiek Dróżdż May 10 at 6:08
  • My sql have json data type and we can extract and order it in query. So I tried the same in wordpress. – Vrangle May 10 at 12:04
0

Querying by postmeta is very inefficient, so for a site with many posts, you're likely to run into issues.

In this type of situation you're almost always better off creating custom taxonomies. Taxonomy queries are much less demanding and scale better on larger sites.

  • you are right, using postmeta for json data is inefficient way, taxonomies accepts string, not even associative arrays. – Vrangle May 9 at 23:05
  • The only problem is that you can’t order by taxonomies easily, so using taxonomies in this case is not the best idea, I’m afraid... – Krzysiek Dróżdż May 10 at 5:58
  • @KrzysiekDróżdż so in what way it will be efficient to deal with these data. give me a suggestion – Vrangle May 10 at 12:02
  • @Vrangle it will need only one join if you’ll store these values as separate meta values. – Krzysiek Dróżdż May 10 at 12:05
  • 1
    Ok, you’ve lost me ;) Could you elaborate and put some examples in your question? It will be easier to answer, if we know the context and conditions. – Krzysiek Dróżdż May 10 at 12:24

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.