Litedb: Ordering by a property that belongs to a base class

[tabtile]

Dear Mauricio,

First, thank you very much for your wonderful project.
I'm using LiteDB 4.1 and encountered a problem when running an order by query.

I have a class Rect that derives from a class Shape, and I'm inserting Rect instances to litedb.
Now, I want to select all the rects from the db ordered by a property ("LastUpdated") that exists in the parent Shape class and it doesn't work (I get the items in random order...)

internal static List<T> AllEntriesOrderedByDescending<T>(this LiteCollection<T> collection, int limit)
{
    return collection.Find(Query.All("LastUpdated", Query.Descending), skip: 0, limit: limit).ToList();
}

Nothing is indexed and the property of LastUpdated is fetched correctly in a regular "select all" query.
I'll be happy for your help.

[mbdavid]

Hi @tabtile, thanks!! Did you have an index in LastUpdated column? Use query order must be over an index only (not over a field only).

Try do:

collection.EnsureIndex(x => x.LastUpdated);

Before run your Find

Another way to sort is use FindSort method, but it's avaiable only in LiteEngine instance: You can use like this:

var docs = db.Engine.FindSort("mycol", Query.All(), "$.LastUpdated", Query.Descending);

var entities = docs.Select(x => x.Mapper.ToObject<T>(x)).ToList();

[tabtile]

Hey Mauricio,

You've just made my day :)
It works! Thank you!

[Jacknq]

Is there Generic version of this?
Im using all over expressions with find, how can I compile expression simple way to use findsort?
I want to get perfomance fixed, normal find, ordered after with skip take at the end takes few hundred of ms. Perfomance drop from 3 to 4 visible, on this one.
and having find sort with search, sortby, sortdirection, skip, take, and count out variable would be magnificent...!

[mbdavid]

Hi @Jack85, FindSort was made as an experiment test, that why I didn't publish in LiteDatabase (generic) and LiteRepository. Will be available in next version. But basic implementation is use Mapper instance for map query/fields like this:

```C#
var query = database.Mapper.GetQuery(x => x.CustomerName == "John");

var order = database.Mapper.GetField(x => x.Age);

var docs = db.Engine.FindSort("customers", query, order, Query.Descending);

var entities = docs.Select(x => x.Mapper.ToObject(x)).ToList();
```

[Jacknq]

Thanks, Im struggling with this version, trying generic and non generic stuff..
f.ex. this query doesnt bring anything, although data are there .(

 var cc = db.GetCollection<Category>();
            cc.EnsureIndex(x => x.Name);
//returns nothing
 var find = repo.db.GetCollection("post")
                                .Include("$.Categories[*]").Find(
                              Query.StartsWith("LOWER($.Categories[*].Name)", search.ToLower().Trim());

//or this guy gets exception
   var query = repo.db.Mapper.GetQuery<Post>(x => x.Categories!=null
                     && x.Categories.Any(a=>a.Name.ToLower()==search));
//InvalidOperationException: variable 'a' of type 'code.Category' referenced from scope '', but it is not defined

[mbdavid]

Hi @Jack85, if this query is about your other issue example, you cann't search collection data when this data doesnt below to this collection. If Categories is DbRef, there no "Categories.Name" in Post collection: Includes are not like join in this case (at least in this v4).

About LINQ conversion, there is no complex LINQ expression -> BsonExpression, like this:

x.Categories.Any(a=>a.Name.ToLower()==par1) into this: LOWER($.Categories[*].Name) = par1

[Jacknq]

Ok, in that one - meaning no generic support.
And the first one on collection or Engine, why is that one with include not returning anything?
...LOWER($.Categories[*].Name)

[mbdavid]

That because Includes are runned in "result set" only. That means: after get your data, engine apply includes. It's possible see here:

https://github.com/mbdavid/LiteDB/blob/master/LiteDB/Engine/Engine/FindInclude.cs#L16

[Jacknq]

Ok, but it makes sense to filter data first and then get the data. In v3 was generic version of this working.
How would be any other query around this (filter and select on dbref, except creating extra index)?
u re using similar here with embed, make sense on dbref for me too:

// Query using multikey index (where products are an array of embedded documents)
var results = col.Find(Query.GT("Products[*].Price", 100)) 

[mbdavid]

Make sense but it's very expensive. For each document, I need populate all references and than check if you want this. When I have no optimizer, run this for all document, in all queries can be spend a lot of unused loads.

To make this works in v4 just do as v3 do:

var all = engine.FindSort("Posts", Query.All(), "$.Categories[*]")
    .Select(x => x.Mapper.ToObject<T>(x));

var filtered = all.Where(x => x.Categories.Any(....)); // now you can filter using any LINQ-To-Object predicate

return filterd

[Jacknq]

Ok, thanks.
Similar things does EF with filtering first subresult with where clause and then selecting with that criteria rest.

[lbnascimento]

Hi! With the objective of organizing our issues, we are closing old unsolved issues. Please check the latest version of LiteDB and open a new issue if your problem/question/suggestion still applies. Thanks!