.NET - recordsTotal wrong when using Where Conditions

.NET - recordsTotal wrong when using Where Conditions

guidolsguidols Posts: 38Questions: 14Answers: 1

Hello,

I have some fields where I need to perform particular filterings, for instance an "exact match" search (but I have also other, more complex, use cases). Therefore I marked the fields as searchable: false in the JS definition and I'm handling the search server side (.NET):

For instance:

var searchedId = Request["columns[0][search][value]"];
if (!string.IsNullOrEmpty(searchedId) && int.TryParse(searchedId, out int n))
editor = editor.Where("Main.Id", n, "=");

So far, so good.

The problem is that the query that counts the recordsTotal is now different from the normal behaviour:
- Normally the query is something like: SELECT COUNT( Main.Id ) as 'cnt' FROM [Main]
- When performing the where clause in .NET, the query is something like: SELECT COUNT( Main.Id ) as 'cnt' FROM [Main] LEFT JOIN [Projects] ON [Projects].[Id] = [Main].[Project_Id] LEFT JOIN [Tasks] ON [Tasks].[Id] = [Main].[Task_Id] LEFT JOIN ... WHERE [Main].[Id] = @where_0

Basically every linked table is added to the recordsTotal query.

The query then returns 1, so in the editor is impossible to see the total number of records.

Seems a bug to me, but maybe I'm doing something wrong.

Thanks!

Answers

  • allanallan Posts: 62,099Questions: 1Answers: 10,183 Site admin

    Just to confirm, are you using SearchBuilder here? Can you show me your full controller code please?

    Thanks,
    Allan

  • guidolsguidols Posts: 38Questions: 14Answers: 1

    Hi,

    sure:

    public JsonResult Test()
    {
        var request = System.Web.HttpContext.Current.Request;
    
        using (var db = new Database("sqlserver", System.Configuration.ConfigurationManager.ConnectionStrings["MyContext"].ConnectionString))
        {
            var editor = new Editor(db, "Main", "Main.Id")
                .Model<Main>("Main")
                .Model<LinkedProject>("Projects")
                // other linked tables here
    
            var searchedId = Request["columns[" + SpecialSearchFields["Id"] + "][search][value]"];
    
            if (!string.IsNullOrEmpty(searchedId) && int.TryParse(searchedId, out int n))
                editor = editor.Where("Main.Id", n, "=");
    
            editor = editor.Field(new Field("Main.Id").Set(false))
            // other fields here
    
            var response = editor.Process(request).Data();  
            return Json(response);
    

    btw, I now added:

    response.recordsTotal = _db.Main.Count();

    and now it's working correctly, but this seems to me a workaround

  • allanallan Posts: 62,099Questions: 1Answers: 10,183 Site admin

    Great to hear you've got a workaround.

    Can I just check - does this issue only happen when you are using SearchBuilder? Or does it happen even if you aren't using SearchBuilder in your DataTable? I'm wondering if there might be a grouping error in the SQL condition.

    If you could add .Debug(true) just before the .Process(request) call and then show me the Ajax response when the error happens, that would be really useful.

    Thanks,
    Allan

  • guidolsguidols Posts: 38Questions: 14Answers: 1

    Seems to me that this happens only with SearchBuilder. If I don't use it everything seems correct.

    How can I post the reposnse?

  • allanallan Posts: 62,099Questions: 1Answers: 10,183 Site admin

    I've got an idea why it might be happening - I think there might be a grouping issue with the query condition. Let me experiment with that a little can get back to you.

    Allan

  • allanallan Posts: 62,099Questions: 1Answers: 10,183 Site admin

    Turns out my idea wasn't right - so I'm not yet sure what is causing this I'm afraid (I'd wondered about the grouping of where conditions).

    Could you add the .Debug(true) call just before .Process(request) and then show me the Ajax response when the error happens please?

    Thanks,
    Allan

Sign In or Register to comment.