AutoQuery can't query nested object

Question

AutoQuery could not find field

I have the following clases:

[Route("/query/domains")]
public class QueryDomains : QueryDb<Domain, DomainList>
{
    public int MajesticApiDataTF { get; set; }
}

public class DomainList
{
    public string DomainName { get; set; }
    public int MajesticApiDataTF { get; set; }
}

[Route("/domain/{Id}")]
public class Domain : IReturn<DomainResponse>
{
    [AutoIncrement]
    public int Id { get; set; }

    [Index(Unique = true)]
    [StringLength(70)]
    public string DomainName { get; set; }  

    [Reference]
    public MajesticApiData MajesticApiData { get; set; }

}

public class MajesticApiData
{
    [AutoIncrement]
    public int Id { get; set; }
    public int TF { get; set; }
    [ForeignKey(typeof(Domain), OnDelete = "CASCADE")]
    public int DomainId { get; set; }
}

When I try querying in AutoQuery viewer by TF like this:

http://localhost:5000/query/domains?MajesticApiDataTF%3E=1&MajesticApiDataTF%3E=1

All domain results come back without filtering and every TF value shows 0.

I tried adding IJoin:

public class QueryDomains : QueryDb<Domain, DomainList>, IJoin<Domain, MajesticApiData>

But then the query returns no results.

I have checked in database and TF does have a value. I am using PostgreSql provider.

I want it to show the TF value and be filterable. What did I get wrong?

Edit:

OK so the issue appears to be that the greater than operator is not supported.

If I do:

query/domains?MajesticApiDataTF>5&include=Total

Then debug output shows

2018-07-07 01:54:00.1555||DEBUG|OrmLiteReadCommandExtensions|SQL: SELECT COUNT(*) "COUNT(*)"
FROM "domain" INNER JOIN "majestic_api_data" ON
("domain"."id" = "majestic_api_data"."domain_id")
WHERE "majestic_api_data"."tf" = :0
PARAMS: :0=0

But if I do:

query/domains?MajesticApiDataTF=5&include=Total

Then there is a param value passed.

2018-07-07 01:57:08.7809||DEBUG|OrmLiteReadCommandExtensions|SQL: SELECT COUNT(*) "COUNT(*)"
FROM "domain" INNER JOIN "majestic_api_data" ON
("domain"."id" = "majestic_api_data"."domain_id")
WHERE "majestic_api_data"."tf" = :0
PARAMS: :0=5

Is it possible to use greater than/less than on a joined property? If not how do I add that in myself?

:fix

I set it to nullable int int? MajesticApiDataTF and then it worked

score 1 · Accepted Answer · answered Jul 06 '18 at 06:36

1

As you may suspect, AutoQuery does not support querying nested/referenced types.

Also Joins in AutoQuery need to follow the implicit Reference conventions that must exist in the 2 tables being joined, they do not query or change how referenced data is loaded or filtered.

AutoQuery only lets you query the target type (in this case Domain), there is no ability to filter the referenced types that are loaded.

answered Jul 06 '18 at 06:36

mythz

141,670
29
246
390

Hi Mythz, thanks for getting back to me. I am a little confused, doesn't my example follow the implicit reference convention {Table}{Field} as specified in the [AutoQuery docs](http://docs.servicestack.net/autoquery-rdbms#joining-tables)? Table = MajesticApiData, Field = TF. Combined = MajesticApiDataTF – Guerrilla Jul 06 '18 at 07:38
I need to filter by nested value. I don't understand what I did wrong in the join, as far as I can see from the docs I followed it correctly as my previous comment says. But the thing is I really need to filter by this value. How can I achieve that? – Guerrilla Jul 06 '18 at 15:08
Do any of the sample projects have an example of a working join? – Guerrilla Jul 06 '18 at 15:11
@Guerrilla As mentioned in my answer, Referenced Tables are not the same as Joined tables, i.e. Joins doesn't have any impact on references. Instead you would need to select into a custom table that includes properties from each table as documented in AutoQuery docs on Joins. You can find a number of join examples in [AutoQueryTests.cs](https://github.com/ServiceStack/ServiceStack/blob/master/tests/ServiceStack.WebHost.Endpoints.Tests/AutoQueryTests.cs). Have a look at the SQL Profiling to see what SQL is being executed to find out why it's returning the results. – mythz Jul 06 '18 at 16:17
Why do the docs say `"The Request DTO lets us query against fields across the joined tables where each field is matched with the first table containing the field." ` In your given example with rockstar and album name it is defining `RockstarAlbumName` as a query property. How is that different to what I have done? – Guerrilla Jul 06 '18 at 17:56
@Guerrilla you can query against joined tables yes, but like I've said they don't have any impact on referenced properties which can't be queried. Have a look at the SQL Profiler to see what SQL is generated. – mythz Jul 06 '18 at 18:05
Yeah so if I join the `MajesticApiData` table to the `Domain` table (`MajesticApiData` contains `DomainId`) I should be able to query with `MajesticApiDataTF` right? This isn't working. I am unable to use the profiler as I am using .net core and as far as i can see `MiniProfilerFeature` is defined in `ServiceStack.NetFramework` which is only available for .net 4.5. Is there alternative way to use? I cannot see in docs. – Guerrilla Jul 06 '18 at 18:31
@Guerrilla You can enable [debug logging](http://docs.servicestack.net/logging) to view the generated SQL or by registering a [BeforeExecFilter](https://github.com/ServiceStack/ServiceStack.OrmLite#beforeexecfilter-and-afterexecfilter-filters). – mythz Jul 06 '18 at 18:36
I have looked at debug output and can see it is because the greater than operator is not working. Can you please look at the edit I made to question. – Guerrilla Jul 07 '18 at 01:01
1

I set the property to nullable int and now it works ok – Guerrilla Jul 07 '18 at 01:16

AutoQuery can't query nested object

1 Answers1