Node combined multiple rows into one using mysql or lodash

Question

Currently, I'm using Node MySQL library to implement MySQL query in Javascript framework. I have an issue about the left join with multiple rows. When I left join that table, it returns more than one object, this is because the left join will produce duplicate rows but different values of that particular attribute. What I want to achieve right now is, returns one object and insert that multiple values to array of that particular attribute.

Table A

id | name | age
 1   abel   22
 2   john   22

Table B

id | user_id | equip
 1      1      armor
 2      2      sword
 3      1      knife
 4      2      gun

Query

SELECT * FROM Table_A LEFT JOIN TABLE_B ON TABLE_B.user_id = TABLE_A.id;

Current Situation

{
    id: 1
    name: abel
    age: 22
    user_id: 1
    equip: 'armor'
},
{
    id: 1
    name: abel
    age: 22
    user_id: 1
    equip: 'knife'
},
{
    id: 2
    name: john
    age: 22
    user_id: 2
    equip: 'sword'
},
{
    id: 2
    name: john
    age: 22
    user_id: 2
    equip: 'gun'
}

What I want to achieve

{
    id: 1
    name: abel
    age: 22
    user_id: 1
    equip: [
       'armor','knife'
    ]
},
{
    id: 2
    name: john
    age: 22
    user_id: 2
    equip: [
       'sword','gun'
    ]
}

Anyway to achieve using node mysql query or lodash?

Answer 1

What you're trying to do can't be accomplished with a join. Here's what I came up with, though SQL wizards may have better solutions:

SELECT Table_A.name, GROUP_CONCAT(item_name) AS equip
  FROM Table_B
JOIN Table_A
  ON Table_A.id=Table_B.person_id 
WHERE Table_A.id=1;

That will produce

+------+-------------------+
| name | person_items      |
+------+-------------------+
| abel | armor,sword,knife |
+------+-------------------+

Then just create an array with split(','). But I see a few problems beyond the one you're trying to solve.

Table_B is doing too much work. Drop the user_id column and create a third table, Table_A_Table_B, with three columns: id primary key, table_a_id foreign key referring to Table_A.id; table_b_id foreign key referring to Table_B.id.

Unless you change the tables this way, you'll need to enter an item name every time you add a row to Table B. Eliminate that redundancy, and use the relational database properly, by creating/inserting the item once in its own table and using a child table to point to it and an entity in Table_A whenever you need to connect them (that's Table_A_Table_B).

Names need to be more descriptive and accurate, ex. Table_A -> people, Table_B -> items, and Table_A_Table_B -> 'person_item,equip->item_name`.

From square one:

CREATE TABLE people (
  id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50),
  age INT UNSIGNED
);

CREATE TABLE items (
  id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
  item_name VARCHAR(20)
);

CREATE TABLE person_item (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  person_id INT UNSIGNED NOT NULL,
  item_id INT UNSIGNED NOT NULL,

  PRIMARY KEY (id),
  FOREIGN KEY (person_id) 
    REFERENCES people(id)
    ON UPDATE CASCADE ON DELETE CASCADE,
  FOREIGN KEY (item_id)
    REFERENCES items(id)
    ON UPDATE CASCADE ON DELETE CASCADE
);

Mix in a bit of data:

INSERT INTO people (name, age) VALUES ('abel', 20), ('john', 21);
INSERT INTO items (item_name) VALUES ('armor'), ('sword'), ('knife');
INSERT INTO person_item (person_id, item_id) VALUES (1, 1), (1, 2), (1, 3), (2, 1), (2, 2), (2, 3);

MySQL doesn't support arrays, and best practices for what you want to do would, I think, be not to do it at all, but instead to do this:

SELECT p.*, i.* 
  FROM people p 
JOIN person_item pi 
  ON p.id=pi.person_id 
JOIN items i 
  ON i.id=pi.item_id 
WHERE p.id=1;

which would produce:

+------+-----+-----------+
| name | age | item_name |
+------+-----+-----------+
| abel |  22 | armor     |
| abel |  22 | sword     |
| abel |  22 | knife     |
+------+-----+-----------+

which you could then use Node to get the data you want. Or you can do this:

SET sql_mode='';

SELECT people.name, GROUP_CONCAT(item_name) AS person_items
  FROM items 
JOIN person_item 
  ON person_item.item_id=items.id 
JOIN people 
  ON people.id=person_item.person_id 
WHERE people.id=1;

Producing:

+------+-------------------+
| name | person_items      |
+------+-------------------+
| abel | armor,sword,knife |
+------+-------------------+