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

[Jevgenij]

Hi!

I have db schema like this:

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 items by city.

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

So, for instance, item can be associated with few colours, and it have to be searchable by colours 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!

[Jevgenij]

Have found Aggregate filter plugin, with an example 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.