Multi nesting in sql query does not work - SELECT * from Products where supplierID=1 and ((categoryId=1 and price=18) or (categoryId=2 and price=22))
xuyongweb opened this issue · 7 comments
Title
// The or query was resolved in 4.5.6, but it reappears in 4.5.8
Description
//
Select * from Table_Name where Column1=value1 and (Column2=value2 or Column3=value3);
Select * from Table_Name where (Column1=value1 and Column2=value2 and Column3 = value3 and Column4 = value4) or (Column2=value1 and Column1=value2 and Column3 = value3 and Column4 = value4);
Select * from Table_Name where Column3 = value3 and Column4 = value4 and ((Column2=value1 and Column1 = value2) or (Column1=value1 and Column2=value2))
Note: Description should contains the query, the current output & the expected output. You can use idbstudio to generate the example.
select({
from: "Products",
where: [{
supplierId: 1,
}, {
categoryId: 1,
price: 18,
}, {
or: {
categoryId: 2,
price: 22,
}
}]
});
https://ujjwalguptaofficial.github.io/idbstudio/?db=Demo&query=select(%7B%0D%0A%20%20%20%20from%3A%20%22Products%22%2C%0D%0A%20%20%20%20where%3A%20%5B%7B%0D%0A%20%20%20%20%20%20%20%20supplierId%3A%201%2C%0D%0A%20%20%20%20%7D%2C%20%7B%0D%0A%20%20%20%20%20%20%20%20categoryId%3A%201%2C%0D%0A%20%20%20%20%20%20%20%20price%3A%2018%2C%0D%0A%20%20%20%20%7D%2C%20%7B%0D%0A%20%20%20%20%20%20%20%20or%3A%20%7B%0D%0A%20%20%20%20%20%20%20%20%20%20%20%20categoryId%3A%202%2C%0D%0A%20%20%20%20%20%20%20%20%20%20%20%20price%3A%2039%2C%0D%0A%0D%0A%20%20%20%20%20%20%20%20%7D%0D%0A%20%20%20%20%7D%5D%0D%0A%7D)%3B
@ujjwalguptaofficial
select({
from: "Products",
where: [{
supplierId: 1,
}, {
categoryId: 1,
price: 18,
}, {
or: {
categoryId: 2,
price: 22,
}
}]
});
Select * from Table_Name where supplierId= 1 and ((categoryId=1 and price= 18) or (categoryId=2 and price =22))
seems good to me @xuyongweb
I have executed this on w3schools and this is what i received
which is same in jsstore. Let me know if you are expecting something else.
The result is different @ujjwalguptaofficial
I see - i will have a look. Thanks for the explanation.
if you will write sql query
SELECT * from Products where supplierID=1 and (categoryId=1 and price=18) or (categoryId=2 and price=22)
then in jsstore it will be -
select({
from: "Products",
where: [{
supplierId: 1,
}, {
categoryId: 1,
price: 18,
}, {
or: {
categoryId: 2,
price: 22,
}
}]
});
both have same results.
but when you add one more nesting to sql query -
SELECT * from Products where supplierID=1 and ((categoryId=1 and price=18) or (categoryId=2 and price=22))
it returns one result. Currently multi level nesting is not supported in jsstore, so will have to work. I will work on this and let you know.
@xuyongweb i have released a new version - 4.5.9 which have features of nested where query to deep level -
Here are some examples -
Sql
SELECT * from Products where supplierID=1 and ((categoryId=1 and price=18) or (categoryId=2 and price=22))
JsStore
select({
from: "Products",
where: [{
supplierId: 1,
},
[{
categoryId: 1,
price: 18,
}, {
or: {
categoryId: 2,
price: 22,
}
}]
]
})
Sql
SELECT * from Products where supplierID=1 or ((categoryId=1 and price=18) or (categoryId=2 and price=22))
JsStore
select({
from: "Products",
where: [{
supplierId: 1,
}, {
or: [{
categoryId: 1,
price: 18,
}, {
or: {
categoryId: 2,
price: 22,
}
}]
}]
})