# How to migrate MySQL data with many-to-many associations?

**URL:** <https://discuss.elastic.co/t/how-to-migrate-mysql-data-with-many-to-many-associations/140746>\
**Category:** Logstash\
**Created:** [July 19, 2018, 1:57pm UTC](https://discuss.elastic.co/t/how-to-migrate-mysql-data-with-many-to-many-associations/140746 "2018-07-19T13:57:14Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![Jevgenij](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jevgenij/32/33543_2.png) [@Jevgenij](https://discuss.elastic.co/u/Jevgenij)\
**Post date:** [July 19, 2018, 1:57pm UTC](https://discuss.elastic.co/t/how-to-migrate-mysql-data-with-many-to-many-associations/140746/1 "2018-07-19T13:57:14Z")

</div>

Hi!

I have db schema like this:

 ![SO_example](https://us1.discourse-cdn.com/elastic/original/3X/6/5/657d39449ba76a83d8300917c52576af2cc3df8c.png)

I've succeded migration for `city` table data with `LEFT JOIN`:

```
statement => "SELECT `item`.`id`, `item`.`title`, `item`.`description`, `city`.`name` AS `city` FROM `item` LEFT JOIN `city` ON `city`.`id` = `item`.`city`"

```

So now I can search `item`s by city.

But also there are many-to-many associations: `colour` and `specification`.

So, for instance, `item` can be associated with few `colour`s, and it have to be searchable by `colour`s too.

Record could be like:

```
city | title | colours
=======|=======|==================
London | cat | red, green, blue
Paris | cat | red, yellow

```

And there should be availability to search for cats in London, or red cats.

Any help would be appreciated!

---

<div class="post-metadata">

**Author:** ![Jevgenij](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jevgenij/32/33543_2.png) [@Jevgenij](https://discuss.elastic.co/u/Jevgenij)\
**Post date:** [July 19, 2018, 5:07pm UTC](https://discuss.elastic.co/t/how-to-migrate-mysql-data-with-many-to-many-associations/140746/2 "2018-07-19T17:07:07Z")

</div>

Have found Aggregate filter plugin, with an [example](https://www.elastic.co/guide/en/logstash/current/plugins-filters-aggregate.html#plugins-filters-aggregate-example4) matching my case.

So my statement is:

```
SELECT `item`.`id`, `item`.`title`, `item`.`description`, `city`.`name` AS `city`, `colour`.`name` AS `colour`
FROM `item`
LEFT JOIN `city` ON `city`.`id` = `item`.`city`
LEFT JOIN `item_has_colour` AS `ic` ON `ic`.`item` = `item`.`id
LEFT JOIN `colour` ON `colour`.`id` = `ic`.`colour`
ORDER BY `item`.`id`

```

It fetches all existing combinations with possibly multiple rows for one `item` for each `colour`.

Here the Aggregate filter enters the scene in order to combine these multiple rows of single `item` into one event:

```
filter {
  aggregate {
    task_id => "%{id}"
    code => "
      map['id'] = event.get('id')
      map['title'] = event.get('title')
      map['description'] = event.get('description')
      map['city'] = event.get('city')
      map['colours'] ||= []
      map['colours'] << {'colour' => event.get('colour')}
      event.cancel()
    "
    push_previous_map_as_event => true
    timeout => 3
  }
}

```

But here I ran into a problem: sometimes it does combine rows values for `colour`, sometimes it doesn't.

The thing is, logstash, as I can see in its `stdout` output, fetches rows in random order. Like there is no `ORDER BY` in my SQL statement.

May be the reason is in something different, but anyway multiple rows for single `item` are got combined or not on random basis.

Here I've stuck completely.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [August 16, 2018, 5:07pm UTC](https://discuss.elastic.co/t/how-to-migrate-mysql-data-with-many-to-many-associations/140746/3 "2018-08-16T17:07:12Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.