Query Builder sample with MongoDB
Hi
Is there any sample about QueryBuilder and MongoDB integration ?
Thanks
Hamdi
SIGN IN To post a reply.
1 Reply
1 reply marked as answer
MK
Mohan Kumar Ramasamy
Syncfusion Team
November 9, 2020 03:51 PM UTC
Hi Hamdi,
Thanks for your patience.
We have checked your reported query. We can integrate MongoDB in Querybuilder component. Please refer below code snippets.
|
<SfQueryBuilder @ref="qbObj" Width="70%" TValue="Product" DataSource="@DataSource">
</SfQueryBuilder>
<SfButton Content="Search" @onclick="Search"></SfButton>
@code {
SfQueryBuilder<Product> qbObj;
private List<Product> DataSource = new List<Product>();
private List<Product> GridDataSource = new List<Product>();
protected override async Task OnInitializedAsync()
{
GridDataSource = DataSource = await ProductService.GetProductsAsync();
}
private async Task Search()
{
RuleModel rule = this.qbObj.GetValidRules();
List<Product> filteredList = new List<Product>();
string mongoQuery = await JsRuntime.InvokeAsync<string>("getMongoQuery", rule);
BsonDocument doc = MongoDB.Bson.Serialization.BsonSerializer.Deserialize<BsonDocument>(mongoQuery);
MongoDB.Driver.QueryDocument queryDoc = new QueryDocument(doc);
filteredList = await ProductService.GetSearchProductsAsync(queryDoc);
GridDataSource = filteredList;
}
}
|
_Host.cshtml
|
<script>
var mongoQuery = '{';
function getMongoQuery(rule) {
mongoQuery = '{';
mongoQuery = qbRules(rule);
return mongoQuery;
}
function qbRules(rules) {
mongoQuery = '{';
if (rules.condition == "or") {
mongoQuery += '"$or":[';
mongoQuery = convertToMongoQuery(rules.rules) + ']';
} else {
mongoQuery += '"$and":[';
mongoQuery = convertToMongoQuery(rules.rules) + ']';
}
mongoQuery += '}';
return mongoQuery;
}
function convertToMongoQuery(rules) {
let i = 0;
var fieldDataQuery = ':{"$regex":';
var fieldDataQueryNotEqual = ':{"$ne":';
var fieldDateQuery = ':{"$date":';
var fieldDateQueryNotEqual = ':{"$ne":{"$date":';
var fieldDataQueryEnd = "}";
var fieldDateQueryEnd = "}}";
rules.forEach((item) => {
i++;
mongoQuery += '{';
if (item.rules != undefined) {
if (item.condition == "or") {
mongoQuery += ' "$or":[';
mongoQuery = this.convertToMongoQuery(item.rules) + ']';
} else {
mongoQuery += ' "$and":[';
mongoQuery = this.convertToMongoQuery(item.rules) + ']';
}
}
var itVal = item.type == 'String' && item.operator != 'in' && item.operator != 'notin' && item.value != null && item.value.trim() != '' ? item.value.replace(/\"/g, "\\\"") : '';
if (item.type == 'String' && (item.operator == 'in' || item.operator == 'notin') && item.value != null && item.value.length == 1) {
itVal = item.value[0].replace(/\"/g, "\\\"");
}
switch (item.operator) {
case "contains":
mongoQuery = `${mongoQuery}"${item.field.substring(0)}"${fieldDataQuery}".*${itVal}"${fieldDataQueryEnd}`;
break;
case "startswith":
mongoQuery = `${mongoQuery}"${item.field.substring(0)}"${fieldDataQuery}"^${itVal}"${fieldDataQueryEnd}`;
break;
case "endswith":
mongoQuery = `${mongoQuery}"${item.field.substring(0)}"${fieldDataQuery}"${itVal}$"${fieldDataQueryEnd}`;
break;
case "equal":
if (item.type == "String") {
mongoQuery = `${mongoQuery}"${item.field.substring(0)}":"${itVal}"`;
}
else if (item.type == "Number") {
mongoQuery = `${mongoQuery}"${item.field.substring(0)}":${item.value}`;
} else if (item.type == "Date") {
mongoQuery = `${mongoQuery}"${item.field.substring(0)}"${fieldDateQuery}"${item.value}"}`;
} else {
mongoQuery = `${mongoQuery}"${item.field.substring(0)}":${item.value.toLowerCase()}`;
}
break;
case "notequal":
if (item.type == "String") {
mongoQuery = `${mongoQuery}"${item.field.substring(0)}"${fieldDataQueryNotEqual}"${itVal}"${fieldDataQueryEnd}`;
}
else if (item.type == "Number") {
mongoQuery = `${mongoQuery}"${item.field.substring(0)}"${fieldDataQueryNotEqual}${item.value}${fieldDataQueryEnd}`;
} else if (item.type == "Date") {
mongoQuery = `${mongoQuery}"${item.field.substring(0)}"${fieldDateQueryNotEqual}"${item.value}"${fieldDateQueryEnd}`;
} else {
mongoQuery = `${mongoQuery}"${item.field.substring(0)}"${fieldDataQueryNotEqual}${item.value.toLowerCase()}${fieldDataQueryEnd}`;
}
break;
case "in":
if (item.type == "String") {
if (item.value.length > 1) {
var s = '';
for (var j = 0, iLen = item.value.length; j < iLen; j++) {
s += '"' + item.value[j] + '"' + ',';
}
s = s.endsWith(',') ? s.substring(0, s.length - 1) : s;
mongoQuery = `${mongoQuery}"${item.field.substring(0)}":{"$in":[${s}]${fieldDataQueryEnd}`;
}
else
mongoQuery = `${mongoQuery}"${item.field.substring(0)}":{"$in":['${itVal}']${fieldDataQueryEnd}`;
}
else if (item.type == "Number") {
if (item.value.length > 1) {
mongoQuery = `${mongoQuery}"${item.field.substring(0)}":{"$in":[${item.value.toString()}]${fieldDataQueryEnd}`;
}
else
mongoQuery = `${mongoQuery}"${item.field.substring(0)}":{"$in":[${item.value}]${fieldDataQueryEnd}`;
}
break;
case "notin":
if (item.type == "String") {
if (item.value.length > 1) {
var s = '';
for (var j = 0, iLen = item.value.length; j < iLen; j++) {
s += '"' + item.value[j] + '"' + ',';
}
s = s.endsWith(',') ? s.substring(0, s.length - 1) : s;
mongoQuery = `${mongoQuery}"${item.field.substring(0)}":{"$nin":[${s}]${fieldDataQueryEnd}`;
}
else
mongoQuery = `${mongoQuery}"${item.field.substring(0)}":{"$nin":["${itVal}]"${fieldDataQueryEnd}`;
}
else if (item.type == "Number") {
if (item.value.length > 1) {
mongoQuery = `${mongoQuery}"${item.field.substring(0)}":{"$nin":[${item.value.toString()}]${fieldDataQueryEnd}`;
}
else
mongoQuery = `${mongoQuery}"${item.field.substring(0)}":{"$nin":[${item.value}]${fieldDataQueryEnd}`;
}
break;
case "greaterthan":
if (item.type == "Date") {
mongoQuery = `${mongoQuery}"${item.field.substring(0)}":{"$gt":{"$date": "${item.value}"${fieldDateQueryEnd}`;
} else
mongoQuery = `${mongoQuery}"${item.field.substring(0)}":{"$gt":${item.value}${fieldDataQueryEnd}`;
break;
case "greaterthanorequal":
if (item.type == "Date") {
mongoQuery = `${mongoQuery}"${item.field.substring(0)}":{"$gte":{"$date": "${item.value}"${fieldDateQueryEnd}`;
} else
mongoQuery = `${mongoQuery}"${item.field.substring(0)}":{"$gte":${item.value}${fieldDataQueryEnd}`;
break;
case "lessthan":
if (item.type == "Date") {
mongoQuery = `${mongoQuery}"${item.field.substring(0)}":{"$lt":{"$date": "${item.value}"${fieldDateQueryEnd}`;
} else
mongoQuery = `${mongoQuery}"${item.field.substring(0)}":{"$lt":${item.value}${fieldDataQueryEnd}`;
break;
case "lessthanorequal":
if (item.type == "Date") {
mongoQuery = `${mongoQuery}"${item.field.substring(0)}":{"$lte":{"$date": "${item.value}"${fieldDateQueryEnd}`;
} else
mongoQuery = `${mongoQuery}"${item.field.substring(0)}":{"$lte":${item.value}${fieldDataQueryEnd}`;
break;
}
mongoQuery += '}';
if (rules.length != i) {
mongoQuery += ',';
}
});
return mongoQuery;
}
</script> |
producetServices.cs
|
public class ProductService
{
MainDbContext _dbContext = new MainDbContext();
public async Task<List<Product>> GetProductsAsync()
{
try
{
return await _dbContext.ProductCollection.Find(_ => true).ToListAsync().ConfigureAwait(false);
}
catch
{
throw;
}
}
public async Task<List<Product>> GetSearchProductsAsync(QueryDocument query)
{
try
{
return await _dbContext.ProductCollection.Find(query).ToListAsync().ConfigureAwait(false);
}
catch
{
throw;
}
}
}
public MainDbContext()
{
var client = new MongoClient("mongodb://localhost:27017");
_mongoDatabase = client.GetDatabase("TestDB");
} |
For your reference, we have prepared a sample based on this. Please refer below link.
Above sample, we have created TestDB database and load the collection of data in Querybuilder component.
Please let us know, if you need any further assistance.
Regards,
Mohan kumar R
Marked as answer
SIGN IN To post a reply.
- 1 Reply
- 2 Participants
- Marked answer
-
HB Hamdi Boyaci
- Oct 30, 2020 01:32 PM UTC
- Nov 9, 2020 03:51 PM UTC
