# How to map a MySQL NestedSet Category Tree?
**URL:** https://discuss.elastic.co/t/how-to-map-a-mysql-nestedset-category-tree/16996
**Category:** Elasticsearch
**Created:** [April 14, 2014, 7:12am UTC](https://discuss.elastic.co/t/how-to-map-a-mysql-nestedset-category-tree/16996 "2014-04-14T07:12:13Z")
**Posts on this page:** 5
**Page:** 1
### Author:  [@Stefan1](https://discuss.elastic.co/u/Stefan1)
#### Post date: [April 14, 2014, 7:12am UTC](https://discuss.elastic.co/t/how-to-map-a-mysql-nestedset-category-tree/16996/1 "2014-04-14T07:12:13Z")
Hello,
I have a mysql table with categories in a nested set structure
([http://en.wikipedia.org/wiki/Nested\_set\_model](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](mailto: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](https://groups.google.com/d/msgid/elasticsearch/4bf275c8-1cec-4993-9f92-d805c593b561%40googlegroups.com).
For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout).
---
### Author:  [@jprante](https://discuss.elastic.co/u/jprante)
#### Post date: [April 14, 2014, 7:59am UTC](https://discuss.elastic.co/t/how-to-map-a-mysql-nestedset-category-tree/16996/2 "2014-04-14T07:59:09Z")
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](mailto:stefan.glaesser@gmail.com) wrote:
> Hello,
>
> I have a mysql table with categories in a nested set structure (
> [Nested set model - Wikipedia](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](mailto: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](https://groups.google.com/d/msgid/elasticsearch/4bf275c8-1cec-4993-9f92-d805c593b561%40googlegroups.com)[https://groups.google.com/d/msgid/elasticsearch/4bf275c8-1cec-4993-9f92-d805c593b561%40googlegroups.com?utm\_medium=email&utm\_source=footer](https://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](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](mailto: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](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](https://groups.google.com/d/optout).
---
### Author:  [@Stefan1](https://discuss.elastic.co/u/Stefan1)
#### Post date: [April 14, 2014, 8:52am UTC](https://discuss.elastic.co/t/how-to-map-a-mysql-nestedset-category-tree/16996/3 "2014-04-14T08:52:51Z")
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](mailto:stefan....@gmail.com)\
>
> > wrote:
>
> > Hello,
> >
> > I have a mysql table with categories in a nested set structure (
> > [Nested set model - Wikipedia](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 [elasticsearc...@googlegroups.com](mailto:elasticsearc...@googlegroups.com) \.
> > To view this discussion on the web visit
> > [https://groups.google.com/d/msgid/elasticsearch/4bf275c8-1cec-4993-9f92-d805c593b561%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/4bf275c8-1cec-4993-9f92-d805c593b561%40googlegroups.com)[https://groups.google.com/d/msgid/elasticsearch/4bf275c8-1cec-4993-9f92-d805c593b561%40googlegroups.com?utm\_medium=email&utm\_source=footer](https://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](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](mailto: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](https://groups.google.com/d/msgid/elasticsearch/69d412ae-39eb-402f-acae-73d1367863fe%40googlegroups.com).
For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout).
---
### Author:  [@jprante](https://discuss.elastic.co/u/jprante)
#### Post date: [April 14, 2014, 1:16pm UTC](https://discuss.elastic.co/t/how-to-map-a-mysql-nestedset-category-tree/16996/4 "2014-04-14T13:16:49Z")
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](mailto: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](mailto:stefan....@gmail.com) wrote:
> >
> > > Hello,
> > >
> > > I have a mysql table with categories in a nested set structure (
> > > [Nested set model - Wikipedia](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 [elasticsearc...@googlegroups.com](mailto:elasticsearc...@googlegroups.com).
> > >
> > > To view this discussion on the web visit [https://groups.google.com/d/](https://groups.google.com/d/)
> > > msgid/elasticsearch/4bf275c8-1cec-4993-9f92-d805c593b561%
> > > [40googlegroups.com](http://40googlegroups.com)[https://groups.google.com/d/msgid/elasticsearch/4bf275c8-1cec-4993-9f92-d805c593b561%40googlegroups.com?utm\_medium=email&utm\_source=footer](https://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](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](mailto: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](https://groups.google.com/d/msgid/elasticsearch/69d412ae-39eb-402f-acae-73d1367863fe%40googlegroups.com)[https://groups.google.com/d/msgid/elasticsearch/69d412ae-39eb-402f-acae-73d1367863fe%40googlegroups.com?utm\_medium=email&utm\_source=footer](https://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](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](mailto:elasticsearch+unsubscribe@googlegroups.com).
To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/CAKdsXoHmT\_%3DCJ1cqkJwvyBtF1vQgrOfsvxg1EgHBCD0yLDxNXw%40mail.gmail.com](https://groups.google.com/d/msgid/elasticsearch/CAKdsXoHmT_%3DCJ1cqkJwvyBtF1vQgrOfsvxg1EgHBCD0yLDxNXw%40mail.gmail.com).
For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout).
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [July 6, 2017, 1:35am UTC](https://discuss.elastic.co/t/how-to-map-a-mysql-nestedset-category-tree/16996/5 "2017-07-06T01:35:57Z")