How to get subquery values

[Virab]

Hi, I need to get subquery structure. I can’t understand how to get it.
I have such structure:

Item:
id
fild1
fild2
fild n

properties:
item_id
prop1
prop2
prop n

I need to get such result: Item: fild1, fild2, …, fild n + prop { prop1, prop2, …, prop n}

class structure:

class props {
    public string prop1 { get; set; }
    public string prop2 { get; set; }
}

class items {
    public string fild1 { get; set; }
    public string fild2 { get; set; }
    public List<props> props { get; set; }
}

thanks

[mythz]

Complex properties are blobbed by default so it should automatically be populated with the result, so I’m not seeing where the issue is. What’s the actual OrmLite query that’s not working as expected?

[Virab]

it is my db classes:

i need such result:

it is my query:

it is sample data:

I need:

[mythz]

So the data model provided earlier wasn’t an OrmLite data model, which means the props aren’t going to get blobbed with the items table.

These are the only options for selecting columns from multiple tables, i.e. you wont be able to select them into your custom complex POCO directly but looks like you should be able to use SelectMulti for this, e.g:

var q = db.From<items>()
  .LeftJoin<items,props>((item,prop) => item.id = prop.item_id)
  .LeftJoin<items,img>((item,img) => item.id = img.item_id);

List<Tuple<items,props,imgs>> results = db.SelectMulti<items,props,imgs>(q);

[Virab]

Demis thanks, everything is working.
But i have one more question, colud you help me:

for example we have an element:

item 1{
id = “1”,
name = “item1”,
code = “a01”

props{
price = “1000”,
stock = “10”,
is new = “yes”
}

imgs{
url1,
url2
}
}

and i get the request with such filter, where described fields, that i should return

fields {
name
props {
price
}
}

and I have to return the response in such format:

item 1{
name = “item1”

props{
price = “1000”
}
}

when use sql query, i collect query text with fileds that i need. how can i get it by lamba exspression

[mythz]

What’s the difference between fields and props? they just look like different column names.

You can just select the fields you want in:

var q = db.From<items>()
  .LeftJoin<items,props>((item,prop) => item.id = prop.item_id)
  .LeftJoin<items,img>((item,img) => item.id = img.item_id);
  .Select(new[]{ "name", "price" });

And then access the results using any of the Dynamic ResultSets APIs, e.g:

var results = db.Select<Dictionary<string,object>>(q);

Also when you get a chance you may want to checkout AutoQuery it has a lot of these features inc customizable fields built in without requiring any dev effort and benefits from a strong typed API.

[Virab]

thanks!

and one more question please,

I need to get all user sessions and I get it by following method:

How can I get the sessions directly without handle and filtering all keys ?

[mythz]

You can’t sessions are stored against a random key. The only way to know inspect a Session is to access it individually.

But I wouldn’t recommend scanning all User Sessions like this, if you need all User Sessions I would register a OnAuthenticated Session or Auth Event and maintain a list of Sessions Ids for each user yourself.

[Virab]

thanks Demis !
I will do so.

[Virab]

Demis, it somehow doesn’t work.

I’m doing the following query:

And I get such response, but it should return some of them, not all:

I need to get such result with already filtered and grouped fields:

[mythz]

SelectMulti creates its own custom SELECT, you can’t combine it with a custom Select().

You need to access unstructured results with a Dynamic ResultSet API (mentioned previously).

[Virab]

i understood but there is problem

r1 is working correct and show correct result but r2 is note correct, it is bug or I do something wrong ?

[mythz]

Yeah that’s a bug that should be now resolved with this commit.

This change is available from v4.0.61 that’s now available on MyGet.

[Virab]

Thanks a lot Demis !