CODEWITHSUNDEEP
phpwordpress
Antonios Tsimourtos
Antonios Tsimourtos

Reputation: 1672

Get users where meta key is a serialized array of terms

I have a list of users which i would like to fetch only by using the appropriate meta_query arguments :

Users are able to choose/select favorite categories and that data is saved as meta and more specifically as a serialized array.

meta_key: favorite-categories

meta_value: a:9:{i:0;s:1:"7";i:1;s:2:"10";i:2;s:1:"8";i:3;s:2:"21";i:4;s:2:"22";i:5;s:2:"24";i:6;s:2:"28";i:7;s:2:"25";i:8;s:2:"26";}

The above meta_value means that the specific user likes the below favorite categories (term ids) :

Array
(
    [0] => 7
    [1] => 10
    [2] => 8
    [3] => 21
    [4] => 22
    [5] => 24
    [6] => 28
    [7] => 25
    [8] => 26
)

How my meta_query should be structured in order to fetch users that "liked" the categories 7 and 26? Problem is that i can't think the best approach on that.

The below argument does not seem to work :

    [key] => favorite-categories
    [value] => Array
        (
            [0] => 43
            [1] => 36
            [2] => 41
            [3] => 42
            [4] => 40
            [5] => 30
            [6] => 8
            [7] => 9
            [8] => 10
            [9] => 29
            [10] => 7
            [11] => 20
            [12] => 19
            [13] => 22
            [14] => 18
            [15] => 21
            [16] => 26
            [17] => 25
            [18] => 28
            [19] => 27
        )

    [compare] => IN
    [type] => text
)

Upvotes: 0

Views: 869

Answers (2)

Antonios Tsimourtos
Antonios Tsimourtos

Reputation: 1672

The way I solved this, since there is no way changing the stored procedure, is the below : 

$arguments = array(
    array(
    "key" => 'favorite-categories',
    "value" => ":\"7\"",
    "compare" => "LIKE",
    ),
 array(
        "key" => 'favorite-categories',
        "value" => ":\"13\"",
        "compare" => "LIKE",
        ),

Which actually means search serialized array LIKE :"7" AND LIKE :"13"

Upvotes: 1

gadz82
gadz82

Reputation: 92

I think the best and more performant solution is create a join table between users and categories id. Assuming this way is not possible, you should unserialize and retrieve the array into PHP, and search in it after a some kind of mapping (structured according to your needs).

Upvotes: 0

Related Questions