Essentially trying to write a MySQL query (can be any version) for a locations index view that shows all the items that have current associations with the locations. The item_location tables is representative of where and when the items have moved.
items
______________
| id | name |
--------------
| 1 | item1 |
| 2 | item2 |
| 3 | item3 |
| 4 | item4 |
--------------
locations
______________
| id | city |
--------------
| 1 | city1 |
| 2 | city2 |
| 3 | city3 |
| 4 | city4 |
--------------
item_location
_______________________________________________________
| id | item_id | location_id | date |
------------------------------------------------------|
| 1 | 1 | 1 | 1998-01-01 04:00:00 |
| 2 | 1 | 2 | 1998-01-02 04:00:00 |
| 3 | 2 | 1 | 1998-01-03 04:00:00 |
| 4 | 3 | 1 | 1998-01-04 04:00:00 |
| 4 | 4 | 3 | 1999-01-04 04:00:00 |
| 4 | 4 | 4 | 1999-02-04 04:00:00 |
-------------------------------------------------------
expected output (with limit 3):
____________________________________________________________________________________________
| location.id | location.city | items |
--------------------------------------------------------------------------------------------
| 1 | city1 | [{"id":"2", "name": "item2"}, {"id":"3", "name": "item3"}] |
| 2 | city2 | [{"id":"1", "name": "item1"}] |
| 3 | city3 | [], '', or null because city4 has latest date. |
--------------------------------------------------------------------------------------------
I am open to restructuring my database if there is a more common or efficient practice for such a situation.
I have been using JSON_ARRAYAGG(JSON_OBJECT("id", item.id, "name", item.name)) to build the arrays of json objects. I am open if there is a better/common/performant alternative for that as well.
I have created a reproducible example of my situation that is runnable.
Schema (MySQL v5.7)
CREATE TABLE `item_location` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`location_id` int unsigned NOT NULL,
`item_id` int unsigned NOT NULL,
`date` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `location_id` (`location_id`),
KEY `machine_id` (`item_id`)
) ENGINE=InnoDB;
CREATE TABLE `locations` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`city` varchar(100) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
CREATE TABLE `items` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(25) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
INSERT INTO `items` (`id`, `name`) VALUES
(1, '1405'), (2, '1447'), (3, '1641');
INSERT INTO `locations` (`id`, `city`) VALUES
(1, 'Kingsville'), (2, '
Wright City'), (3, 'Racine');
INSERT INTO `item_location` (`id`, `location_id`, `item_id`, `date`) VALUES
(1, 3, 1, '1992-01-14 05:00:00'),
(2, 3, 2, '1993-02-05 05:00:00'),
(3, 3, 3, '1997-05-13 04:00:00'),
(4, 1, 3, '2018-01-15 05:00:00'),
(5, 2, 3, '2006-07-01 04:00:00')
Attempted Query #1
SELECT DISTINCT item_location.location_id, locations.city, date, JSON_ARRAYAGG(JSON_OBJECT("id", items.id, "name", items.name)) AS items
FROM item_location
INNER JOIN items ON item_location.item_id = items.id
INNER JOIN locations ON item_location.location_id = locations.id
GROUP BY items.name, item_location.location_id, date;
| location_id | date | city | items |
| ----------- | ------------------- | ------------ | --------------------------- |
| 3 | 1992-01-14 05:00:00 | Racine | [{"id": 1, "name": "1405"}] |
| 3 | 1993-02-05 05:00:00 | Racine | [{"id": 2, "name": "1447"}] |
| 1 | 2018-01-15 05:00:00 | Kingsville | [{"id": 3, "name": "1641"}] |
| 2 | 2006-07-01 04:00:00 | Wright City | [{"id": 3, "name": "1641"}] |
| 3 | 1997-05-13 04:00:00 | Racine | [{"id": 3, "name": "1641"}] |
---
expected outcome:
| location_id | city | items |
| ----------- | ------------ | ———————————————————————————————————————————————————————— |
| 3 | Racine | [{"id": "1", "name": "1405"}, {"id": "2", "name": "1447"}] |
| 1 | Kingsville | [{"id": "3", "name": "1641"}] |
| 2 | Wright City | [] |
Here is my closest query except I'm not sure if there is a way for MySQL to be able to be able to know which item is the most current owner of the location:
Query #1
select `locations`.`id`, `locations`.`city` as `city`, `itms`.`items` from locations
left join (
SELECT il.location_id AS id, JSON_ARRAYAGG(JSON_OBJECT("name", i.name)) AS items
FROM item_location il
JOIN items i ON i.id = il.item_id
GROUP BY il.location_id
) itms USING (id)
order by `city`;
| id | city | items |
| --- | ------------ | ------------------------------------------------------ |
| 2 | Wright City | [{"name": "1641"}] |
| 1 | Kingsville | [{"name": "1641"}] |
| 3 | Racine | [{"name": "1405"}, {"name": "1447"}, {"name": "1641"}] |
