Litedb: Question, Query for a element by its contained subdocument id

[buronix]

Hey!!

First of all Great work are you doing, this is amazing!!

I have a question about queries, I have a struct like this:

product {id, blabla}

customer {id, products: [product1, product2, ...]}

So I want to query for a customer with a specific product id, how can I do it?.

Thanks in advance!!

[ghost]

Have same question)

[amgadfahmi]

var customers= db.GetCollection<Customers>("customers");
var products = customers
    .Include(x => x.Products)
    .FindById(1);

Maybe this will help

When you query a document with a cross collection reference, you can auto load references using Include method before query.

https://github.com/mbdavid/LiteDB/wiki/DbRef

[buronix]

var customers= db.GetCollection<Customers>("customers");
var products = customers
    .Include(x => x.Products)
    .FindById(1);`

that will give me the customer with id 1, and full products information, but I only need to check with the id, which is included by default.

I don´t need to include all the data T_T, but thanks!!

Right now I can only get all the document in the collection and check manually.
But I only need to check the first one (the customer->product is not my real data, is an example) cause I want to check if I can delete the product, but only if there is no customer with a reference of the product. (Sql had also good things), other solution I though is to add a reference in products of the customers who has the product. (but then I will also perform additional checks if I want to delete a customer xD)

[caverna]

Thumbs up for this request!
Entity framework works just like that: you can test a subitem property without "including" entire subitem to query...

[mbdavid]

Hi guys! Queries in NoSQL database works only in same collection. There is no index for cross collections. If, in this example, Products, are from another collection (and linked with DbRef) data are not in Customer. So, no queries in Customer with filter by products.

Works in EF because EF implements SQL query behide the scene. Relation database works with projections so this indexes are possible.

In this case, you can have 2 options:

• Add, in customer document, the information about product you and and index this field (some like this):

public class Customer
{
    ...
    public List<Products> Products { get; set; }
    // this is a get-only field that will contains data in customer document, so can be indexed
    public int ProductsCount => Products.Count;
}

• The second way is use LINQ for objects (with no index, just LINQ object).

customers.FindAll().Where(x => x.Products.Count > 10).ToList();

If you have any filter about customer that you can use in Find(x => x.Name == "John").Where(x => x.Products.Count > 0)

MongoDB has a great documentation about data modeling. LiteDB works like here:

https://docs.mongodb.com/manual/core/data-modeling-introduction/

[buronix]

Thanks David,
But in my scenario I need to know if there is a customer who owns a specific product, I will try to explain myself with a linq example:

var product = customers
            .SelectMany(c => c.products)
            .FirstOrDefault(p => p.Id == "The_ProductId_I_was_looking_For");

or

var customer = customers
            .FirstOrDefault(c => c.products
            .FirstOrDefault(p.Id == "The_ProductId_I_was_looking_For") != null);

So, in first scenario, if there is a product with id I was looking for, associated to a customer then I cant delete the product.
In second scenario if there is a customer with the product I was looking for, then I cant delete the product.

Both of them are valid operations to my check, I don´t need the data from the product collection, cause the ids of the products are stored in the customer collection.

[mbdavid]

Hi @buronix, I understand now. In your case, customer document has product $id. In theory, could be possible index this. But, current version do not support multikey indexes (when you have an index in array structure, like this in mongo: https://docs.mongodb.com/v3.0/core/index-multikey/) This is an old feature request but not implemented yet (it's bit hard to implement).

I thinking in implement this outside current data pages structures with a custom method to create/access this data. Something like this:

db.MultiKey.EnsureIndex("myCol", "customer.$id");
db.MultiKey.Find("myCol", "customer.$id", 1234); 

[buronix]

Hey!

That would be awesome, only to confirm if we are on the same page, it would be something like:

public class Product {
    public int Id { get; set; }
    public string Name { get; set; }
}

public class Customer {
    public int Id { get; set; }
    public string Name { get; set; }
    public List<Product> Products { get; set; }
}

var customerCol = db.GetCollection<Customer>("customers");

var productsCol = db.GetCollection<Customer>("products");

BsonMapper.Global.Entity<Customer>()
    .DbRef(x => x.Products, "products");

customerCol.MultiKey.EnsureIndex("productId", "Products.$id");
Customer customer = customerCol.MultiKey.FindOne("productId", 1234); 

[tanwarsatya]

this functionality is critical ( able to query based on subdocument field ), again thx for all the good work.

[mbdavid]

Hi @tanwarsatya, just to clarify, subdocument field query are supported, something like Customer.Address.Street works fine. Here, the problem is about multi key: and a field returns multi values (and all values must be indexed).

@buronix, yes, thats the idea. Works with multi key index as an plugin (like FileStorage, to store files). The final api I don't decide yet, but will be some like you wrote. To implement as a plugin, I will create an internal collection with all values per document, like this:

{
    "_id": ObjectId()
    "$id": documentReferenceId,
    "value": documentValue
}

When you query

db.MultiKey("CustomerCollection").Find(Query.EQ("Product.$id", 1234))

internal I will made

db.Find("_CustomerCollection_Product.$id_multikey", Query.EQ("value", 1234)) - For each result, search for document _id.

[mbdavid]

It's done! Still under v3 dev branch because will change datafile structure. Will be release in new v3. All changes are made internal, so it's complete transparent for devs. Just create an index in your field. If your field are in an array (or subdorcument inside an array) all values will be indexed. Then, do normal query to find.

Take a look on this examples (still using LiteEngine, new class "lower level" to access data). Final version will back with LiteDatabase and LiteCollection.

https://github.com/mbdavid/LiteDB/blob/dev/LiteDB.Tests/Engine/MultiKeyTest.cs