How to store multiple values of same data type (an array of objects) in one cell of a MySQL row.?

Question

We have shifted our project from Google app engine to Apache Tomcat. I have a case scenario in which one client (web/mobile) need to send messages to multiple people depending on task and task-age.

In Google app engine project we made this case scenario possible using endpoint. We made two pojo class one for company and another for employee, in which employee is child of company. I can declare a list of user and a getter and setter method in company pojo class to fetch a company details including all employee working in that company from datastore in single query.

Now I have to perform same thing with MySQL in which one row of company table should also have one field of employee containing information of employee (an array of employee)!!!

How do I capture multiple people (i.e., array of people name) in same filed of a MySQL table??

It's most times a bad idea to do that. Use another table and insert a row for each value. — VMai
– VMai, Commented Aug 16, 2014 at 12:12
thank you vary much for your answers @VMai and dasblinkenlight !!!what i want to ask is it is our project requirement,we dont want to spend too much of server time to process!!!can you suggest me any solution of this!!! — Dev
– Dev, Commented Aug 16, 2014 at 14:12
one solution i have knowladge is to parse array in to json string and store it as a string in DB!!!! — Dev
– Dev, Commented Aug 16, 2014 at 14:13
can i use json to parse array in to json string and store it as a string in DB? would this be a viable solution from the prospective of performance and resource utilization? — Dev
– Dev, Commented Aug 18, 2014 at 8:07

Tuncay Göncüoğlu · Accepted Answer · 2014-10-14 08:54:21Z

1

Mysql has introduced a group_concat() function a while ago. I think what you are looking for is something like this:

select
  c.id as company,
  group_concat(e.id) as employees
from
  companies c
left join
  employee e on e.company_id = c.id
group by
  e.company_id

Your result will look like:

company | employees
------------------------------------
   1     1,2,3,4
   2     5,6,7,8

edited Oct 14, 2014 at 8:54

answered Aug 25, 2014 at 10:32

Tuncay Göncüoğlu

1,80718 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to store multiple values of same data type (an array of objects) in one cell of a MySQL row.?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related