Is there a good way to get children in MySQL as an array?

Question

First of all, apologies if this is a duplicate, I don't really know the best terminology for this question.

Say I have SQL schema that represents:

Businesses:
id: int
name: string

Employees:
id: int
business_id: int REFERENCES Businesses(id)
name: string

Is there any conceivable way I could query for a list of businesses each with an array of employees attached as a field? Currently I am pulling the list of businesses into my server, and then for each business in the array, getting all employees who have a business_id equal to its id. This works, but for a large number of businesses, it results in a high number of queries to the database.

Another option I considered was reading all employees into memory on my server, putting them into a map of [business_id->[employees]], and then sending the employees array to the right businesses employees field on the server. This seems to be a much more performant option, but I wanted to know if there is anything built into MySQL that would allow me to do this out of the box, as it seems like a pretty common task. Thank you!

EDIT: @MatBailie made a point about code smells here, and I just wanted to clarify the total intent of this code. I will add a step by step walkthrough of what it would do from a high level.

Select all companies from a list
Get all employees of each company
Display to a user a list of companies, and next to each one, a scrollable list of each employee

I understand, as @radocaw pointed out, that getting a list of employees for a business is a trivial join, but my issue is that this will take numerous trips to the database. I will post my current method of solving this issue in Golang below, using two simple SELECTs from MySQL and the use of maps. This is for you, @Tangentially Perpendicular!

    employeeMap := make(map[int][]Employee)
    employees,err := db.Query("SELECT business_id,f_name,l_name,email FROM employees")
    if err != nil {
        //handle error
    }
    for employees.Next(){
        var employee Employee
        err = employees.Scan(&employee.BusinessId,&employee.FName,&employee.LName,&employee.Email)
        if err != nil {
            //handle error
            continue
        }
        if _,ok := employeeMap[employee.BusinessId];ok{
            employeeMap[employee.BusinessId] = append(employeeMap[employee.BusinessId],employee)
        }else {
            employeeMap[employee.BusinessId] = make([]Employee,1)
            employeeMap[employee.BusinessId[0] = employee
        }
    }

This is used to build a map of business ids to lists of employees. Next I would just get the businesses from the database, and for their id, attach that array of employees to them.

I'm not sure if this is even a good way to do this, but it seems more optimized and direct than hitting the database with n joins, where n is the number of businesses. Thanks everyone for the great feedback and high quality answers/comments!

You might want to check https://stackoverflow.com/questions/97197/what-is-the-n1-selects-problem-in-orm-object-relational-mapping, which describe your problem as the "N+1 SELECT" problem — Progman, Jul 23 '21 at 20:06
This is solved pretty well with a simple `JOIN` between both tables, all in a single query. You'll get N rows per business, one for each employee, that you can manipulate into an array at client side. There is no concept of arrays in SQL. — Alejandro, Jul 23 '21 at 20:10
@Alejandro that's interesting, I'm not sure if that approach is any better or worse than what I suggested with the map to arrays. I wasn't sure if SQL had any concept of a subtable, I know I've seen them returned by subqueries but not as a column value. Thanks for the confirmation! — Aaron, Jul 23 '21 at 20:20
@Progman +1 for reminding me what this is called. I think the idea of doing the reads in one pass instead of every time is better, no matter how I do it. — Aaron, Jul 23 '21 at 20:23
What are you going to do with this data when you get it? Focus on the end result and write your query to achieve that, not the intermediate step. Many nested queries questions here start by trying to retrieve data across tables, but can be solved by proper use of JOINs and aggregate functions on the database server. You may find that for some queries you don't need to retrieve the underlying data at all. — Tangentially Perpendicular, Jul 23 '21 at 21:19
This seems like an XY problem to me ( https://en.wikipedia.org/wiki/XY_problem ). Wanting to use arrays in MySQL and/or having multiple round-trips to the database are both code-smells. Until you describe what you're planning on doing with this data / why you want to do this, it's going to be impossible to turn you to a more relational-database pattern. — MatBailie, Jul 23 '21 at 21:58

score 1 · Answer 1 · answered Jul 23 '21 at 21:49

There are several subtly different questions here.

The first is:

Is there a good way to get children in MySQL as an array?

Answer: There may be a way - but it cannot be 'good'.

The second is:

Is there any conceivable way I could query for a list of businesses each with an array of employees attached as a field?

Answer: In a normalised environment, it's inconceivable that a list of businesses would have 'an array of employees attached as a field'

The third might be:

In a normalised environment, how might I pull the a list of businesses into my server and then, for each given business, obtain a list of all employees of that business.

Answer: This is a trivial exercise, normally employing JOINs. For further help, see https://stackoverflow.com/help/minimal-reproducible-example

score 0 · Answer 2 · answered Jul 23 '21 at 21:45

0

You can achieve something close by returning a CSV list using group_concat:

SELECT b.id,b.name,group_concat(e.id) employee_ids, group_concat(e.name) employee_names 
FROM businesses b 
LEFT JOIN employees e on b.id = e.business_id 
GROUP BY b.id,b.name

And then subsequently splitting the values into arrays in code.

answered Jul 23 '21 at 21:45

Eric Shieh

697
5
11

This is cool, I will test it out when I'm at work on Monday, if it works, I'll accept it as the answer because it answers the question exactly. – Aaron Jul 24 '21 at 23:34

Is there a good way to get children in MySQL as an array?

2 Answers2