How to map a MySQL NestedSet Category Tree?

Elastic Stack Elasticsearch
1

Hello,

I have a mysql table with categories in a nested set structure
(http://en.wikipedia.org/wiki/Nested_set_model).

How can I map an entire category tree of an articel with an foreign key of
the category table?

That I have in MySQL and I would like to have this tree as a facet in
elasticsearch.
The mysql table with articels includes only one category-Id of the
category-table.

ID: 1 books (300)
ID: 2 |-- fiction (200)
ID: 3 |-- sience (100)
ID: 4 |-- mathematics (50
ID: 5 |-- biology (50)

Thx for help,
Stefan

--
You received this message because you are subscribed to the Google Groups "elasticsearch" group.
To unsubscribe from this group and stop receiving emails from it, send an email to elasticsearch+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/4bf275c8-1cec-4993-9f92-d805c593b561%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

2

Example:

{
"title" : "foo",
"author" : "bar",
"category" : {
"level1": "books",
"level2": "science",
"level3": "mathematics"
}
}

then perform nested aggregation

"aggs" : {
"level1" : {
"terms" : {
"field" : "category.level1"
},
"aggs" : {
"level2" : {
"terms": {
"field" : "category.level2"
},
"aggs" : {
"level3" : {
"terms" : {
"field" : "category.level3"
}
}
}
}
}
}
}

Jörg

On Mon, Apr 14, 2014 at 9:12 AM, Stefan stefan.glaesser@gmail.com wrote:

Hello,

I have a mysql table with categories in a nested set structure (
Nested set model - Wikipedia).

How can I map an entire category tree of an articel with an foreign key of
the category table?

That I have in MySQL and I would like to have this tree as a facet in
elasticsearch.
The mysql table with articels includes only one category-Id of the
category-table.

ID: 1 books (300)
ID: 2 |-- fiction (200)
ID: 3 |-- sience (100)
ID: 4 |-- mathematics (50
ID: 5 |-- biology (50)

Thx for help,
Stefan

--
You received this message because you are subscribed to the Google Groups
"elasticsearch" group.
To unsubscribe from this group and stop receiving emails from it, send an
email to elasticsearch+unsubscribe@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/elasticsearch/4bf275c8-1cec-4993-9f92-d805c593b561%40googlegroups.comhttps://groups.google.com/d/msgid/elasticsearch/4bf275c8-1cec-4993-9f92-d805c593b561%40googlegroups.com?utm_medium=email&utm_source=footer
.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "elasticsearch" group.
To unsubscribe from this group and stop receiving emails from it, send an email to elasticsearch+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/CAKdsXoFDsW_22-NtcNO2eP8gRS%2BNz9Qd%3DQqmW4avTcOv7n-E2A%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.

3

Hello Jörg,

thanks for your quick reply. The problem is not to search/query at the
moment, the problem is how to
implement a mapping for it.

I'm using Symfony, the Doctrine-Extensions for Tree-Model (NestedSet) and
FOSElasticBundle
for indexing my entity. How can I create a mapping which reflects the
different levels
of the category tree? Sometime I have 2 sublevels, sometime even 8.

Best regards,
Stefan

Am Montag, 14. April 2014 09:59:09 UTC+2 schrieb Jörg Prante:

Example:

{
"title" : "foo",
"author" : "bar",
"category" : {
"level1": "books",
"level2": "science",
"level3": "mathematics"
}
}

then perform nested aggregation

"aggs" : {
"level1" : {
"terms" : {
"field" : "category.level1"
},
"aggs" : {
"level2" : {
"terms": {
"field" : "category.level2"
},
"aggs" : {
"level3" : {
"terms" : {
"field" : "category.level3"
}
}
}
}
}
}
}

Jörg

On Mon, Apr 14, 2014 at 9:12 AM, Stefan <stefan....@gmail.com<javascript:>

wrote:

Hello,

I have a mysql table with categories in a nested set structure (
Nested set model - Wikipedia).

How can I map an entire category tree of an articel with an foreign key
of the category table?

That I have in MySQL and I would like to have this tree as a facet in
elasticsearch.
The mysql table with articels includes only one category-Id of the
category-table.

ID: 1 books (300)
ID: 2 |-- fiction (200)
ID: 3 |-- sience (100)
ID: 4 |-- mathematics (50
ID: 5 |-- biology (50)

Thx for help,
Stefan

--
You received this message because you are subscribed to the Google Groups
"elasticsearch" group.
To unsubscribe from this group and stop receiving emails from it, send an
email to elasticsearc...@googlegroups.com <javascript:>.
To view this discussion on the web visit
https://groups.google.com/d/msgid/elasticsearch/4bf275c8-1cec-4993-9f92-d805c593b561%40googlegroups.comhttps://groups.google.com/d/msgid/elasticsearch/4bf275c8-1cec-4993-9f92-d805c593b561%40googlegroups.com?utm_medium=email&utm_source=footer
.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "elasticsearch" group.
To unsubscribe from this group and stop receiving emails from it, send an email to elasticsearch+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/69d412ae-39eb-402f-acae-73d1367863fe%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

4

I would take the maximum level of depth (here: 8) and create level fields
for it.

Unused level fields can just be left out.

Jörg

On Mon, Apr 14, 2014 at 10:52 AM, Stefan stefan.glaesser@gmail.com wrote:

Hello Jörg,

thanks for your quick reply. The problem is not to search/query at the
moment, the problem is how to
implement a mapping for it.

I'm using Symfony, the Doctrine-Extensions for Tree-Model (NestedSet) and
FOSElasticBundle
for indexing my entity. How can I create a mapping which reflects the
different levels
of the category tree? Sometime I have 2 sublevels, sometime even 8.

Best regards,
Stefan

Am Montag, 14. April 2014 09:59:09 UTC+2 schrieb Jörg Prante:

Example:

{
"title" : "foo",
"author" : "bar",
"category" : {
"level1": "books",
"level2": "science",
"level3": "mathematics"
}
}

then perform nested aggregation

"aggs" : {
"level1" : {
"terms" : {
"field" : "category.level1"
},
"aggs" : {
"level2" : {
"terms": {
"field" : "category.level2"
},
"aggs" : {
"level3" : {
"terms" : {
"field" : "category.level3"
}
}
}
}
}
}
}

Jörg

On Mon, Apr 14, 2014 at 9:12 AM, Stefan stefan....@gmail.com wrote:

Hello,

I have a mysql table with categories in a nested set structure (
Nested set model - Wikipedia).

How can I map an entire category tree of an articel with an foreign key
of the category table?

That I have in MySQL and I would like to have this tree as a facet in
elasticsearch.
The mysql table with articels includes only one category-Id of the
category-table.

ID: 1 books (300)
ID: 2 |-- fiction (200)
ID: 3 |-- sience (100)
ID: 4 |-- mathematics (50
ID: 5 |-- biology (50)

Thx for help,
Stefan

--
You received this message because you are subscribed to the Google
Groups "elasticsearch" group.
To unsubscribe from this group and stop receiving emails from it, send
an email to elasticsearc...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/
msgid/elasticsearch/4bf275c8-1cec-4993-9f92-d805c593b561%
40googlegroups.comhttps://groups.google.com/d/msgid/elasticsearch/4bf275c8-1cec-4993-9f92-d805c593b561%40googlegroups.com?utm_medium=email&utm_source=footer
.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups
"elasticsearch" group.
To unsubscribe from this group and stop receiving emails from it, send an
email to elasticsearch+unsubscribe@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/elasticsearch/69d412ae-39eb-402f-acae-73d1367863fe%40googlegroups.comhttps://groups.google.com/d/msgid/elasticsearch/69d412ae-39eb-402f-acae-73d1367863fe%40googlegroups.com?utm_medium=email&utm_source=footer
.

For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "elasticsearch" group.
To unsubscribe from this group and stop receiving emails from it, send an email to elasticsearch+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/CAKdsXoHmT_%3DCJ1cqkJwvyBtF1vQgrOfsvxg1EgHBCD0yLDxNXw%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.