Joins

[kennywk]

Support the ability to join records from other collections providing an output where the selected/join result is the document value.

[anidotnet]

Could you elaborate more about the feature? It would be better if you provide one example to illustrate it.

[kennywk]

Sure, for instance in mongo with spring you can use dbref for subdocuments. In spring if you annotate a class with @DBREF it will automatically join that document as a property of the annotated document. So for example:

public class Person{ private String id; private String name; private String addressId; }

public class Address{ private String id; private String street; }

Now obviously this is regarding POJOs however if i do a query to select a document by id, It would be awesome if i could join the address document to the returning document where the address document id is equal to the addressId field.

Another example could be that if i have 3 documents that represent portions of an object but in a very normalized way.

public class Point{ public String id; }

public class AbsolutePoint extends Point{ public String id; public Double distance; }

public class GeographicalPoint extends AbsolutePoint{ public String id; public Double latitude; public Double longitude;

It would be awesome if i could create a aggregated document where i can query the three stores/tables by id and create a single result that includes the fields/attributes from the three source documents where the id matches.

[anidotnet]

By design I decided not to include aggregation in an embedded database. The detailed reason I described here in issue #24 . If you are asking for something else and I did not understand it, please let me know.

[kennywk]

Anindya, Thanks, I can understand the reasoning, however my thoughts regarding my description of aggregation is not really pertaining to analytics or calculations, but more so for merging multiple documents into a single master document (aggregating fields). If you are familiar with JPA or how other ORMs store records in a relational db, the fields that are also entities represent a table of its own with a joint table linking the two. When you query based on the parent entity the ORM will pull the objects from the tables necessary to make up the parent object. I see this as the foundation of the request, however it would be nice if i could do it based on arbitrary documents as sometimes i need to work with relatively dynamic data.

…

On Mon, Nov 13, 2017 at 8:54 AM, Anindya Chatterjee < ***@***.***> wrote: By design I decided not to include aggregation in an embedded database. The detailed reason I described here in issue #24 <#24> . If you are asking for something else and I did not understand it, please let me know. — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub <#33 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AFgDOPBLy4y048j3TfSP_RnccyNoM9zYks5s1_XAgaJpZM4QbB3I> .

[anidotnet]

Does projection serve your purpose? - http://www.dizitart.org/nitrite-database/doc/2.0.0/index.html#projection
Or, are you looking for something similar to lookup operation in mongo?

[kennywk]

Projection seems to be based on a single document where you want to only have certain properties associated with the returned document. What i am thinking would be projection where a field could be a document returned from another collection based on a foreign key. For example. https://drive.google.com/file/d/1XAoVo-B8r0uUTdmBIR6XdHkaEWD7I2Pt/view?usp=sharing

…

On Mon, Nov 13, 2017 at 9:36 AM, Anindya Chatterjee < ***@***.***> wrote: Does projection serve your purpose? - http://www.dizitart.org/ nitrite-database/doc/2.0.0/index.html#projection — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub <#33 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AFgDOPSZQx_HOgokillVAFM80hpF-_N7ks5s1_91gaJpZM4QbB3I> .

[anidotnet]

It does look like you are thinking something similar to lookup operation in mongo. So if I understand correctly, if you have 2 repositories like Address, Person

public class Address {
      private String id;
      private String personId;
      private String streetAddress;
}

public class Person {
    private String id;
    private String name;
}

public class PersonDetails {
    private String id;
    private String name;
    private List<Address> addressList;
}

you want to get a result of type PersonDetails where person.id = address.personId

[kennywk]

yes, pretty much. Lookup is definitely a better explanation. Ultimately it is just joining the address and person details into a result of person details.

…

On Mon, Nov 13, 2017 at 9:55 AM, Anindya Chatterjee < ***@***.***> wrote: It does look like you are thinking something similar to *lookup* operation in mongo. So if I understand correctly, if you have 2 repositories like Address, Person public class Address { private String id; private String personId; private String streetAddress; } public class Person { private String id; private String name; } public class PersonDetails { private String id; private String name; private List<Address> addressList; } you want to get a result of type PersonDetails where person.id = address.personId — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub <#33 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AFgDOOygkPDJBCOPi4lN2I43Qmj8KPUYks5s2APjgaJpZM4QbB3I> .

[anidotnet]

Just out of curiosity, what is stopping you to create a repository of PersonDetails in the first place and get the Person and Address objects out of it using projection? Unlike sql databases, here you can persists embedded object directly to the database.

[kennywk]

Good question, So i'm working on a project where the source data model is really normalized. And the data is transmitted via database replication. Each object has its own table and FKs represent the attribute children objects. There are also levels of inheritance where Object A extends Object B but instead of just receiving Object B that contains the fields of Object A, i get the entire inheritance hierarchy. Object B does not contain the fields of Object A. So my thoughts were that i could use Nitrite initially as a staging db for these records, then using the querying, joining and projection to create the denormalized representation and create a new denormalized domain model. I cannot control the source and by having a staging environment such as describe would save me from having to explicitly mapping each table to a new domain model... or at least reduce the amount necessary.

…

On Mon, Nov 13, 2017 at 10:19 AM, Anindya Chatterjee < ***@***.***> wrote: Just out of curiosity, what is stopping you to create a repository of PersonDetails in the first place and get the Person and Address objects out of it using projection? Unlike sql databases, here you can persists embedded object directly to the database. — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub <#33 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AFgDOBNsu5Glk0a3psy3pxSO7Gr_cCWBks5s2AmmgaJpZM4QbB3I> .

[anidotnet]

Interesting use case. If the idea of look up helps you, I'll try to add the feature

[kennywk]

I think that a lookup would be great and very helpful in my case. I think that others could use it as well, so it would not be specific to my use case. I'm attempting to build the source now, will see what else i can come up with. Ultimately, the same thing could be achieved manually, however if it could be automated via the code that would be awesome.

…

On Mon, Nov 13, 2017 at 10:47 AM, Anindya Chatterjee < ***@***.***> wrote: Interesting use case. If the idea of look up helps you, I'll try to add the feature — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub <#33 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AFgDOMjfEnUgGdTlIf2qRcSTaxgSd6A5ks5s2BAhgaJpZM4QbB3I> .

[anidotnet]

I have come up with a basic design as follows:

Lookup lookup = new Lookup();
lookup.localField = "id";
lookup.foreignField = "personId";
lookup.targetField = "addressList";

Cursor cursor = collection1.find().join(collection2.find(), lookup); // for documents
Cursor<PersonDetails> cursor = repo1.find().join(repo2.find(), lookup); // for objects

Let me know your thoughts

[kennywk]

Looks good to me if the output is as expected.... Perhaps using a builder pattern may make it flow better Join join = new Join.Builder().where("id").eq("otherId").from(collection1.find()).applyTo("addressList"); Cursor cursor = collection1.find().join(join) Ideally it should work for both collections and single objects/documents. Thanks for your effort!

…

On Mon, Nov 13, 2017 at 1:05 PM, Anindya Chatterjee < ***@***.***> wrote: I have come up with a basic design as follows: Lookup lookup = new Lookup(); lookup.localField = "id"; lookup.foreignField = "personId"; lookup.targetField = "addressList"; Cursor cursor = collection1.find().join(collection2.find(), lookup); // for documentsCursor<PersonDetails> cursor = repo1.find().join(repo2.find(), lookup); // for objects Let me know your thoughts — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub <#33 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AFgDOM3LqRrVM1GQvTsrrB6KBnkDgQ-vks5s2DCagaJpZM4QbB3I> .

[anidotnet]

I am not inclined to introducing builder patterns right now, as other constructs like query, projection does not have any corresponding builders yet. May be later when I introduce builders for other constructs, I'll definitely add one for it. But for the first step I want to keep it as simple as possible.

[kennywk]

Understand completely. Thanks for your efforts!

…

On Mon, Nov 13, 2017 at 1:33 PM, Anindya Chatterjee < ***@***.***> wrote: I am not inclined to introducing builder patterns right now, as other constructs like query, projection does not have any corresponding builders yet. May be later when I introduce builders for other constructs, I'll definitely add one for it. But for the first step I want to keep it as simple as possible. — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub <#33 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AFgDONYgvPJfJHCHxtXvrbp4Kaz_Feshks5s2DcngaJpZM4QbB3I> .

[anidotnet]

I have made a commit on master. Please build it locally and test. Let me know if it serves your purpose. Once confirmed, I'll do a release, then you will be able to refer it from maven central.

[kennywk]

Anindya, Thanks, i will do that as soon as possible.

…

On Tue, Nov 14, 2017 at 7:50 AM, Anindya Chatterjee < ***@***.***> wrote: I have made a commit on master. Please build it locally and test. Let me know if it serves your purpose. Once confirmed, I'll do a release, then you will be able to refer it from maven central. — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub <#33 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AFgDOMO9PTY0ScYqyEFLpGdy5z6pApG2ks5s2TgcgaJpZM4QbB3I> .

[anidotnet]

Let me know the outcome so I can close this issue.

[kennywk]

I’ve tested the issue. It works as expected. Thanks!

…

Sent from my iPhone

On 16. Nov 2017, at 06:45, Anindya Chatterjee ***@***.***> wrote: Let me know the outcome so I can close this issue. — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub, or mute the thread.

[anidotnet]

Thanks for confirmation. Closing this issue.