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.
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:
Apache, Apache Lucene, Apache Hadoop, Hadoop, HDFS and the yellow elephant
logo are trademarks of the
Apache Software Foundation
in the United States and/or other countries.