A Lesson In Apiculture
Many bees pollinate many flowers but what if we want to know exactly which flowers each bee pollinated? We could, hypothetically speaking, attach a tracking device to each bee, label every possible flower, and collect a data table like below:
| bee_id | flower_id |
|---|---|
| b1 | f1 |
| b1 | f2 |
| b2 | f1 |
| b2 | f3 |
| b3 | f5 |
| b4 | f4 |
| b5 | f2 |
| b5 | f3 |
| b5 | f7 |
| b5 | f9 |
Every time a bee lands on a flower, that flower’s ID is recorded and if the problem was simpler, such as how many flowers each bee visited, in MySQL we could use:
|
1 2 3 |
SELECT bee_id, COUNT(flower_id) as counts FROM hive GROUP by bee_id |
Giving a result like below:
| bee_id | counts |
|---|---|
| b1 | 2 |
| b2 | 2 |
| b3 | 1 |
| b4 | 1 |
| b5 | 4 |
However, this lacks the details of which exact flowers the bees visited.
Introducing GROUP_CONCAT
MySQL offers a seemingly little known function called GROUP_CONCAT which concatenates values in a group.
|
1 2 3 |
SELECT bee_id, GROUP_CONCAT(flower_id) as f_ids FROM hive GROUP by bee_id |
An almost exact SQL code as before, except the COUNT function has been replaced with GROUP_CONCAT resulting in:
| bee_id | f_ids |
|---|---|
| b1 | f1,f2 |
| b2 | f1,f3 |
| b3 | f5 |
| b4 | f4 |
| b5 | f2,f3,f7,f9 |
The essence of the problems this function helps to solve are instances of many-to-many relationships. Additional articles on GROUP_CONCAT are listed below:
That’s a sharp way of thninkig about it.