Design for relationships across indices in ES?

Hi!

I have a database with quite a lot of relations across tables that I want
to replicate it to ES to make it searchable, and avoiding having to make a
search engine towards the database as it'd be really heavy from time to
time trying to be dynamic on what one can search..

For instance, it contains a table for users, groups and group_members.

The tables would look something like this:

Users
| id | name |

Groups
| id | name |

Group_members
| group_id | user_id |

A typical search would then be "List all users not in group".

*Note: *This is a really simple example, other searches will be "worse"
than this.

Is it even possible to do this in ES?

Another solution I'm thinking of is just having a memberof param on the
user document in ES which would be updated regurarly. Would this be a
better solution than even thinking of trying to do realtionships?

--
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/d701b0ec-ac46-488e-a94c-2b860da922ba%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Hi!

Probably the simplest approach would be to store your info into
Elasticsearch in a completely denormalized form.

For example:

select u.first_name, u.last_name, g.name as group_name
from users u
join group_members gm
on gm.user_id = u.id
join groups g
ob g.id = gm.group_id

Then just replicate any changes into elastic search using regular time
intervals.

Regards,

Ogo

Dne sobota, 28. februar 2015 18.42.36 UTC+1 je oseba Jo Emil Holen napisala:

Hi!

I have a database with quite a lot of relations across tables that I want
to replicate it to ES to make it searchable, and avoiding having to make a
search engine towards the database as it'd be really heavy from time to
time trying to be dynamic on what one can search..

For instance, it contains a table for users, groups and
group_members.

The tables would look something like this:

Users
| id | name |

Groups
| id | name |

Group_members
| group_id | user_id |

A typical search would then be "List all users not in group".

*Note: *This is a really simple example, other searches will be "worse"
than this.

Is it even possible to do this in ES?

Another solution I'm thinking of is just having a memberof param on the
user document in ES which would be updated regurarly. Would this be a
better solution than even thinking of trying to do realtionships?

--
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/a3f6b9cd-5f82-42d8-adda-66d30e4d8286%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Yeah, that seems like the best approach. Thanks for your input :slight_smile:

søndag 1. mars 2015 08.57.04 UTC+1 skrev Ognjen Antonič følgende:

Hi!

Probably the simplest approach would be to store your info into
Elasticsearch in a completely denormalized form.

For example:

select u.first_name, u.last_name, g.name as group_name
from users u
join group_members gm
on gm.user_id = u.id
join groups g
ob g.id = gm.group_id

Then just replicate any changes into elastic search using regular time
intervals.

Regards,

Ogo

Dne sobota, 28. februar 2015 18.42.36 UTC+1 je oseba Jo Emil Holen
napisala:

Hi!

I have a database with quite a lot of relations across tables that I want
to replicate it to ES to make it searchable, and avoiding having to make a
search engine towards the database as it'd be really heavy from time to
time trying to be dynamic on what one can search..

For instance, it contains a table for users, groups and
group_members.

The tables would look something like this:

Users
| id | name |

Groups
| id | name |

Group_members
| group_id | user_id |

A typical search would then be "List all users not in group".

*Note: *This is a really simple example, other searches will be "worse"
than this.

Is it even possible to do this in ES?

Another solution I'm thinking of is just having a memberof param on
the user document in ES which would be updated regurarly. Would this be a
better solution than even thinking of trying to do realtionships?

--
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/628d1a95-5030-4fc0-855c-000460ecbba4%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.