Adding / removing where clauses based on conditions

dhutton@creativeone.com

I'm using editor, I'd like to fill my table with a filtered list of results based on multiple conditions that may or may not exist at the time of table draw (users don't have to include options so some where clauses may or may not be relevant). So once the controller is hit (using .Net core), I'd like to be able to add / remove where clauses to my response (see below for a basic response via docs for reference)

I've already built something like this in C#, where add or ignore some of the additional where clauses based on conditions, like:

string team; // delivered to api in query string
IQueryable<type> query = (from b in db select b).Where(m => m.dateofeventdatetime > DateTime.Now);
if (team != "all") {
query = query.Where(m => m.team == team);
}
var results = query.ToList();

Ideally I'd like to build a query first similar to above and then feed it to the response, or make a custom endpoint that can deliver the data I need to datatables. I tried to direct the HttpGet to an endpoint that already had the correct logic in it and adjusting the datatables' ajax in js to something like:
{ url: '/api/endpoint', dataSrc: '' },

and this seemed to work fine for datatable but this seemed to break Editor - all text fields were read only.

Any help or direction on how to proceed? Again I just need a way of building my query to add / remove where clauses based on conditional logic.

Thanks!

For reference:

using (var db = new Database(dbType, dbConnection))
{
var response = new Editor(db, "table", "column")
.Where(q => q.Where("dateofevent", "09/17/2019", ">"))
.Where(q => q.Where("pm", "(SELECT pm FROM table WHERE lower(team) LIKE '" + teamName + "')", "IN", false))
.Model<SeminarsModel>()
.TryCatch(false)
.Process(Request)
.Data();
return Json(response);

allan

Sounds like you just need to break the Editor chain. - e.g.:

var editor = new Editor(db, "table", "column");

if ( dateofevent != "" ) {
  editor.Where(q => q.Where("dateofevent", "09/17/2019", ">"));
}

if ( team != "all" ) {
  editor.Where(q => q.Where("pm", "(SELECT pm FROM table WHERE lower(team) LIKE '" + teamName + "')", "IN", false));
}

editor
  .Model<SeminarsModel>()
  .TryCatch(false)
  .Process(Request)
  .Data();

return Json(response);

Allan

dhutton@creativeone.com

Nice I'll try that tomorrow thanks Allan