I want to query a table Notice like this, Could you tell me how to implement it by AutoQuery? i need a way to create where condition.
SELECT top 1000 "NoticeId", "KindergartenId", "ClassId", "UserId", "NoticeType", "Title", "Content", "CreateTime", "ThumPics", "NormalPics", "ReceiverNumber", "FavoriteCount", "ReadedCount", "ConfirmedCount"
FROM "dbo"."Notice"
WHERE ("dbo"."Notice"."KindergartenId" = 35 And "dbo"."Notice"."NoticeType" IN (4,5))
Or "dbo"."Notice"."ClassId" = 112 AND "dbo"."Notice"."NoticeType" IN ('1', '2')
You shouldn’t expect anyone else to spend their own time to do your job for you, most of your questions can be answered with a simple Google search or reading the available documentation. You need to show that you’ve read the docs and have spent at least some effort attempting to resolve your problems yourself.
Your goal and value as a programmer should be to be self-sufficient, to be able to perform the necessary research and use your analytical skills and tools available to be able to solve problems. We’re here to help when you hit a roadblock along the way.
With that said, here’s some process to follow: What exactly have you tried? What were the results? What didn’t work as expected? How would you expect it to work? Have you tried debugging or logging it yourself? Which specific parts are you having trouble understanding?
I do spend a lot of time to resolve my question. But even i have solutions, i still want you give me a better solution. sorry to disturb you.
For example, I think AutoQuery don’t support (condition1 and condition2) or (condition3 and condition4), AutoQuery document only support condition1 or condition2 and condition3. i has solution to resolve this:
query two times
a. (condition1 and condition2)
b. (condition3 and condition4)
c. merge
i know auto query can switch to or and has Template
[QueryField(Type = QueryTerm.Or, Template = " {Value1} AND {Value2}",]
public string[] Condition { get; set; }
(not test this method)
custom auto query implement in the Get method, i spend some time and find it’s hard to handle null value of conditions. so i switch to 1. and then i will test 2.
I just want to know if there is better solution.
I think a lot of persons has the similar question like me. But maybe i should ask question in the stack overflow.
Should i ask these details in the forums? If i make a mistake, i will correct it.
If it gets too complicated just ignore AutoQuery and implement your Service as you would’ve without it. AutoQuery should provide rich querying functionality for free, if it’s creating friction or not appropriate for a specific Service, ignore AutoQuery and implement the Service normally.
The section on Changing Querying Behavior shows how to change the query to use OR instead of AND querying behavior. i.e. you can use [Query(QueryTerm.Or)] on the AutoQuery request to change the behavior of all fields or annotate individual fields with [QueryField(Term=QueryTerm.Or)] but this isn’t recommended Service Design since ideally each of the fields should have the same semantics.
But here you’re trying to call the same NoticeType field twice but wanting different behavior, you could try implementing this with Custom fields as you’ve noted, but ultimately if I wanted to leverage AutoQuery I would make 2 requests, e.g:
[Route("/notices")]
public QueryNotices : QueryBase<Notice> {}
And have the client deal with the results as it needs.
If you wanted to do everything in 1 request and DB Query then I’d look at creating a separate specific request for this, although because of the re-use of existing fields with different behavior I expect the resulting API to be confusing for Service consumers, which is why I’d personally avoid it.
Thanks for you replying, your words is what i wanted. I am your fans totally.
i will keep my service simple and let client handle these complicated conditions.
if client want to reduce http call i will give a new service to handle.
When i take method 2, i find a strange issue, may be is a bug.
:
reproduce steps:
call api
see sql in the mini profile
generate sql wrong
SELECT COUNT() "COUNT()"
FROM “dbo”.“Notice”
WHERE “dbo”.“Notice”.“NoticeType” IN (‘1’, ‘2’) AND “dbo”.“Notice”.“ClassId” = 112 AND “dbo”.“Notice”.“NoticeId” > 1000 AND “dbo”.“Notice”.“KindergartenId” = 35
expected:
without
AND "dbo"."Notice"."KindergartenId" = 35
This is my code:
DTO
[Authenticate]
[Route("/notices", "GET")]
public class QueryNotices : QueryBase<Notice>
{
public long? KindergartenId { get; set; }
public NoticeType[] NoticeKindergartenTypes { get; set; }
public NoticeType[] NoticeTypes { get; set; }
public long? ClassId { get; set; }
public NoticeType[] NoticeClassTypes { get; set; }
public long? Id { get; set; }
public long? IdGreaterThan { get; set; }
public long? IdLessThan { get; set; }
}
public object Get(QueryNotices request)
{
QueryResponse<Notice> r1 = null;
QueryResponse<Notice> r2 = null;
if (request.ClassId.HasValue)
{
var queryNoticesClass = new QueryNotices().PopulateWith(request);
queryNoticesClass.KindergartenId = null;
queryNoticesClass.NoticeTypes = queryNoticesClass.NoticeClassTypes;
var q2 = AutoQuery.CreateQuery(queryNoticesClass, Request.GetRequestParams());
r2 = AutoQuery.Execute(request, q2);
}
if (request.KindergartenId.HasValue)
{
var queryNoticesKindergarten = new QueryNotices().PopulateWith(request);
queryNoticesKindergarten.ClassId = null;
request.ClassId
= null;
queryNoticesKindergarten.NoticeTypes = queryNoticesKindergarten.NoticeKindergartenTypes;
var q1 = AutoQuery.CreateQuery(queryNoticesKindergarten, Request.GetRequestParams());
r1 = AutoQuery.Execute(request, q1);
}
if ((r1 != null && r2 != null)
&& r1.Results.Count > 0 && r2.Results.Count > 0)
{
r1.Results.AddRange(r2.Results);
return r1;
}
else if (r1 != null)
{
return r1;
}
else
{
return r2;
}
}
The URL you’ve provided has KindergartenId=35 in the URL which AutoQuery is using in the CreateQuery method via the Request.GetRequestParams(). Also, the code you have provided here with the URL example you actually firing 2 queries and only returning one for no reason as both ClassId and KindergartenId both have values.
The client should provide the values it wants to query on if you are using AutoQuery or if you want the logic in your service instead of the client, simply use a standard service that contains the logic you want. Eg, a service that doesn’t use AutoQuery, just a normal service with your own DTO and use OrmLite to construct the specific SQL query you need, as @mythz suggested.
[Route("/findnotices")]
public class QueryNotices: IReturn<QueryNoticesResponse>
{
//... Parameters you want you client to use to query
}
public class QueryNoticesResponse
{
public List<Notice> Result { get; set; }
}
public class MyServices : Service
{
public object Any(QueryNotices request)
{
//Your logic here
List<Notice> results = Db.Select<Notice>(...); //Your OrmLite query.
return new QueryNoticesResponse { Result = results };
}
}
