Query for a property of a property

Is there a way to query for a property of a child property. Like so

   List<Dictionary<string, string>> result = new List<Dictionary<string, string>>();

            var Limit = Expression.Parameter("limit");
            var Offset = Expression.Parameter("offset");
            IOrdering propertyToOrderBy = Ordering.Expression(Expression.Property("supplier.name"));
            using (var database = new Database(DbName))
            {
                using (var query = Query.Select(SelectResult.Expression(Expression.Meta().ID),
                                                SelectResult.Expression(Expression.Property("supplier.name"))                            
                                                )
                           .From(DataSource.Database(database))
                           .OrderBy(propertyToOrderBy)
                           .Limit(Limit, Offset))
                {
                    query.Parameters.Set("limit", limit);
                    query.Parameters.Set("offset", offset);

                    using (var results = query.Run())
                    {
                       
                            foreach (var row in results.ToList())
                            {
                                Dictionary<string, string> dict = new Dictionary<string, string>();

                               
                                dict.Add("purchaseid", row.GetString("id"));
                                dict.Add("suppliername", row.GetString("supplier.name"));
                                

                                result.Add(dict);
                            }
                       

                        if (result.Count > 0)
                            return result;
                        else
                            return null;
                    }
                }
            }

Where supplier is a property of the document and name is a property of the supplier. Supplier is stored in JSON format

Do you mean to say that “supplier” is another document? Or simply an embedded dictionary? For the former you will want to look into doing a JOIN. For the latter, the syntax you have provided should work and if it does not then please file an issue on the .NET Repo describing what happens.

FYI, an example of where I test for the second case: https://github.com/couchbase/couchbase-lite-net/blob/ed9b2e7f8ff7db55392f6b1f12ecae71320af160/src/Couchbase.Lite.Tests.Shared/QueryTest.cs#L284

Hi,

The latter is what I want to achieve indeed.

Borrrden, I did it exactly like that… . Mmmm, I will investigate further. I just remembered a .NET frustration of me with JSON and “” characters… . Maybe that’s why I keep getting null values. I’ll keep you posted

@borrrden

I’m still addressing my problem but this may be of interest to you.

I have setup a Test project. I originally begun with DB19 without altering the previous code. I was getting an error on getting the results in a custom Dictionary.

 dict.Add("purchaseid", row.GetString("id"));
dict.Add("suppliername", row.GetString("supplier.name"));

I thought “hey, Borrrden changed the logic about dictionaries, maybe that’s it.” I assumed that a test where I deserialized my document dictionary to an instance of my custom class purchase after getting it with database.GetDocument() should also fail. But it didn’t… .

What fails in dict.Add("purchaseid", row.GetString("id")); is getting the string.

Reverting back to DB18 got my old result back where my id is correct but the name is null. I will work on this result Today.

update
But I’m thinking it is the way I’ve modeled it. I have a dictionary<string,object> dictionary of purchase. in there a primitive is eg. "name" => "google", but a more complex item like supplier is "supplier" => "{supplierInJsonWithNameProperty}". I use that dictionary for my document as in New Document(id, dictionary). Perhaps the database sees this as a full blow string and not an supplier disguised sich

update2
I had it right. I will work out a fix for my model and rerun my tests on DB19 to see if I still have the error. I’ll post my results asap

Ok, this progressed quickly so I guess there is nothing left for me to comment on right now, but it’s interesting so I will keep watching!

If you want to figure out what is in your result set, though, try using GetObject and see what it returns!

1 Like

“supplier” => “{supplierInJsonWithNameProperty}”

I’m not clear on this part. Do you mean that it is a serialized JSON string? If so, it’s going to be treated as a string as you found out. If you want it to be a dictionary, it needs to be an actual dictionary.

1 Like

Yes you’re right. It is fairly easy to make a Dictionary<string, object> of a complex entity. I can take eg an item and convert the property eg supplier to an object. But my problem is if a property is a List<T>… . Ideally I should find a way so I can easily create and load the item and query for for an item with a specific property in a T.

An update:

We updated relatively easy and painlessly from db18 to db20. Just had to take in account the MutableDocument change. We fell in love with unittesting and all tests that passed db18 pass db20

1 Like

I’m glad that you consider the update relatively painless. Brace yourself for more sweeping changes in db021 though :frowning: With a bit of luck though we can finally stop making so many changes like this and finally focus on QA, performance, and stability all the way until GA. Just keep in mind that the only reason we are so reckless with API changes right now is because this is the only chance we will get for probably the next few years to do so.

1 Like

No problem, we understand completely. After all, if we didn’t we had stayed with the 1.x branch.