Fetch rows from a table if their ids are in a serialized value in another table

Question

I want to fetch values from one table (people) if their ids are within the serialized data of another table (groups).

In the table groups I have this row that contains this serialized data stored:

a:18 {
i:0;s:1:"7";
i:1;s:2:"13";
i:2;s:2:"14";
i:3;s:2:"16";
i:4;s:2:"28";
i:5;s:2:"42";
i:6;s:2:"46";
i:7;s:2:"79";
i:8;s:2:"81";
i:9;s:2:"94";
i:10;s:3:"149";
i:11;s:3:"219";
i:12;s:3:"234";
i:13;s:3:"264";
i:14;s:3:"266";
i:15;s:3:"270";
i:16;s:3:"273";
i:17;s:3:"285";
}

Here's converted to an array so it's easier to look at

array (
  0 => '7',
  1 => '13',
  2 => '14',
  3 => '16',
  4 => '28',
  5 => '42',
  6 => '46',
  7 => '79',
  8 => '81',
  9 => '94',
  10 => '149',
  11 => '219',
  12 => '234',
  13 => '264',
  14 => '266',
  15 => '270',
  16 => '273',
  17 => '285',
)

I have another table, people, that has thousands of rows with data on those people (name, surname, mail), as you can tell from the array I want to fetch specifically the rows whose ids are in that groups' row (7, 13, 14, 16, 28, 42, 46, 79, 81, 94, 149, 219, 234, 264, 266, 270, 273 and 285).

I'm able to work in PHP with individual values within the table groups easily using this regex code

SELECT * FROM groups WHERE people REGEXP CONCAT('i:[0-9]{1,3};s:[0-9]{1,3}:.', ?, '.;')
/* Where ? is the specific id I want. */

Let's say I query this

SELECT * FROM groups WHERE people REGEXP 'i:[0-9]{1,3};s:[0-9]{1,3}:"7";'

That will indeed fetch me all the rows in the table groups that contain the id 7.

Now, what I need is to basically get name and surname from my table people but only those whose ids are in that specific data in groups. I'll write some non functioning code just so you can see my approach

SELECT name, surname
FROM people AS table1
IF id
IN (people REGEXP CONCAT('i:[0-9]{1,3};s:[0-9]{1,3}:.', table1.id, '.;')
FROM groups
WHERE id = 1)

Could you give me a hand? I'm certain regex has to be used and perhaps CASES but I have no experience with those. I want to get everything in a single query if possible.

This is nearly a hopeless task. Don't store serialized data if you want to search it. Normalize your tables. — Barmar, Apr 21 '22 at 18:28
Why are you serialising this kind of linking data? Normalise your table structure instead — ADyson, Apr 21 '22 at 18:28
This is effectively a duplicate of [Is storing a delimited list in a database column really that bad?](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) - although it's talking about comma separated data instead of serialised, it's basically the same scenario, the same database design flaw and the same consequences, including the inability to search the data or join to other tables properly — ADyson, Apr 21 '22 at 18:29
@ADyson This is even worse, it's not a delimited list. At least with comma-delimited data you can use `FIND_IN_SET()`. — Barmar, Apr 21 '22 at 18:30

score 0 · Accepted Answer · answered Apr 21 '22 at 18:35

0

You need to join the tables.

SELECT p.name, p.surname
FROM people AS p
JOIN groups AS g ON g.people REGEXP CONCAT('i:[0-9]{1,3};s:[0-9]{1,3}:"', p.id, '";')
WHERE g.id = 1

answered Apr 21 '22 at 18:35

Barmar

741,623
53
500
612

Just like that, quick and easy, thank you so much. – MCorbo Apr 21 '22 at 18:47
Do you think there's a need to normalize my tables at all? I just find it more convenient to handle data with as little rows and columns as possible, with your code I don't really see any drawback to keeping my data like this – MCorbo Apr 21 '22 at 18:49
Yes, you should still normalize. This is an awful way to process data, it can't take advantage of indexes. – Barmar Apr 21 '22 at 19:09

Fetch rows from a table if their ids are in a serialized value in another table

1 Answers1