How to setup ElasticSearch index structure with multiple entity bindings

by

That’s a great start!

I would definitely flatten it all out (i.e. denormalize) and come up with product documents that look like the one below. That way you get rid of the N:M relationship between products and flags by simply creating a flags array for each product. It will thus be easier to query those flags.

{
   "id": "00c8234d71c4e94f725cd432ebc04",
   "title": "Alpha",
   "price": 589.0,
   "flags": ["Sellout", "Top Product"]
}
{
   "id": "018357657529fef056cf396626812",
   "title": "Beta",
   "price": 355.0,
   "flags": ["Discount"]
}
{
   "id": "01a2c32ceeff0fc6b7dd4fc4302ab",
   "title": "Gamma",
   "price": 0.0,
   "flags": ["Discount"]
}

The product mapping type would look like this:

PUT products
{
    "mappings": {
        "product": {
            "properties": {
                "id": {
                    "type": "string",
                    "index": "not_analyzed"
                },
                "title": {
                    "type": "string"
                },
                "price": {
                    "type": "double",
                    "null_value": 0.0
                },
                "flags": {
                    "type": "string",
                    "index": "not_analyzed"
                }
            }
        }
    }
}

Since you have the logstash jdbc input already, all you’re missing is the proper SQL query to fetch the products and associated flags.

  SELECT p.Id as id, p.Title as title, p.Price as price, GROUP_CONCAT(f.Title) as flags
    FROM Products p
    JOIN flagsProducts fp ON fp.ProductId = p.Id
    JOIN Flags f ON fp.FlagId = f.id
GROUP BY p.Id

Which would get you rows like these:

+-------------------------------+-------+-------+---------------------+
| id                            | title | price | flags               |
+-------------------------------+-------+-------+---------------------+
| 00c8234d71c4e94f725cd432ebc04 | Alpha |   589 | Sellout,Top product |
| 018357657529fef056cf396626812 | Beta  |   355 | Discount            |
| 01a2c32ceeff0fc6b7dd4fc4302ab | Gamma |     0 | Discount            |
+-------------------------------+-------+-------+---------------------+

Using Logstash filters you can then split the flags into an array and you’re good to go.

More Related Contents:

  1. Im trying to create some temporary tables in mysql using php
  2. the update query just update the last row and affect other with it’s value
  3. How to insert images into a database using PHP [closed]
  4. How to change mysql to mysqli?
  5. php mysqli_connect: authentication method unknown to the client [caching_sha2_password]
  6. Importing CSV data using PHP/MySQL
  7. Import CSV file directly into MySQL
  8. mysql_fetch_array, mysql_fetch_assoc, mysql_fetch_object
  9. Storing and displaying unicode string (हिन्दी) using PHP and MySQL
  10. Using MySQLi from another class in PHP
  11. How do I display a MySQL error in PHP for a long query that depends on the user input? [duplicate]
  12. Increment value in MySQL update query
  13. Php – Your PHP installation appears to be missing the MySQL extension which is required by WordPress
  14. Whats the easiest way to determine if a user is online? (PHP/MYSQL)
  15. What is the advantage of using try {} catch {} versus if {} else {}
  16. How to convert ISO8601 to Date format in php
  17. Javascript function post and call php script
  18. while ($row = mysql_fetch_array($result)) – how many loops are being performed?
  19. Trying to access array offset on value of type bool
  20. onbeforeprint() and onafterprint() equivalent for non IE browsers
  21. How to get a query error from prepare() in PDO PHP?
  22. Fatal error: Call to a member function query() on null
  23. MySQL & PHP Parameter 1 as Resource
  24. MySQL GROUP BY and Fill Empty Rows
  25. mysql injection damages?
  26. PHP PDO how to run a multiple query request?
  27. Auto Increment skipping numbers?
  28. PDO bindParam into one statement?
  29. Convert Time String to Decimal Hours PHP [closed]
  30. How to make mysqli connect function?

Leave a Comment