How to group by and count each item of serialized array?

  • I’m storing a list of items in a serialized array within a field in my database. Each user have this serialized array in own table. I need to count how many user has one item of the serialized array.

    For example:
    user id | value
    123       | a:4:{i:0;s:3:”615“;i:1;s:3:”619“;i:2;s:3:”918“;i:3;s:3:”570“;}
    12         | a:4:{i:0;s:3:”23“;i:1;s:3:”615“;i:2;s:3:”918“;}
    143       | a:4:{i:0;s:3:”918“;i:1;s:3:”125“;}

    i need this result:
    918 – 3 users have it
    615 – 2 users have it
    619 – 1 user have it
    570 – 1 user have it
    125 – 1 user have it
    23 – 1 user have it

    I will be very grateful for your help!!

    • This topic was modified 3 years, 4 months ago by cutmasterx.
    • This topic was modified 3 years, 4 months ago by cutmasterx.
    • This topic was modified 3 years, 4 months ago by cutmasterx.
    • This topic was modified 3 years, 4 months ago by cutmasterx.
    • This topic was modified 3 years, 4 months ago by cutmasterx.
    • This topic was modified 3 years, 4 months ago by cutmasterx.
    • This topic was modified 3 years, 4 months ago by cutmasterx.
    • This topic was modified 3 years, 4 months ago by cutmasterx.
Viewing 1 replies (of 1 total)
  • Plugin Author BuddyDev

    (@buddydev)

    Hi,
    Thank you for the question.

    I am sorry, I do not see any right way to do it for serialized array. The problem is you can not search for a specific value as any other serialized data in field containing it will match.

    If you have small number of users, you may fetch all users, loop through them and fetch the value and build the list aggregating it.

    Regards
    B

Viewing 1 replies (of 1 total)
  • The topic ‘How to group by and count each item of serialized array?’ is closed to new replies.