Or query, with many parameters, does it require writing each one twice
xuyongweb opened this issue · 15 comments
Discussed in #330
Originally posted by xuyongweb July 7, 2023
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))
May I ask how to write it using jsstore syntax?
Query 1
Select * from Table_Name where Column1=value1 and (Column2=value2 or Column3=value3);
Jsstore
select({
from: table_name,
where: [{
column1: value1
}, {
column2: value2,
or: {
column3: value3
}
}]
})
Thanks，
Select * from Table_Name where Column1=value1 and ((Column2=value2 and Column4=value4) or (Column3=value3 and Column5=value5));
`
select({
from: table_name,
where: [{
column1: value1,
}, {
column2: value2,
Column4=value4,
or: {
column3: value3,
Column5=value5
}
}]
})
`
Is it correct to write this way?
yes this is correct.
Okay, thank you. I'll test it and get back to you later
Take a look at this link - https://jsstore.net/tutorial/adv-sql-example/
Select * from Products where supplierId = 1 and((categoryId = 1 and price = 18) or(categoryId = 2 and price = 39));select({
from: "Products",
where: [{
supplierId: 1,
}, {
categoryId: 1,
price: 18,
or: {
categoryId: 2,
price: 39,
}
}]
});The result is incorrect!
Are you still there? @ujjwalguptaofficial
Try this -
select({
from: "Products",
where: [{
supplierId: 1,
}, {
categoryId: 1,
price: 18,
}, {
or: {
categoryId: 2,
price: 39,
}
}]
});
Treat every parenthis in sql as object query here.
Thanks! Problem solved
Nice :). I am also working on making documentation better 🎉 .
// 新增
connection.insert = async function(body) {
// if(body['into']== 'Members') console.log('开始插入',body)
// console.log('开始插入',body)
if (!isOpen()) await openDb();
//获取表格字段
let tableItems = initTable.filter(el => {
return el.name == body['into']
})
// console.log('tableItems',tableItems)
const tableItem = {...tableItems[0]['columns']}
Object.keys(tableItem).forEach(mkk => {
// 如果是主键自增字段，删除数据，让他自增长
if ('autoIncrement' in tableItem[mkk] && tableItem[mkk]['autoIncrement']) {
delete tableItem[mkk];
return;
}
//如果设置了联合索引，删除字段不做处理
if ('keyPath' in tableItem[mkk] ) {
delete tableItem[mkk];
return;
}
})
let sqlStr = ``;
Object.keys(body).forEach(key => {
if (key == 'into') {
if ('upsert' in body && body['upsert']) {
// 如果存在旧数据与新数据冲突，删除旧数据插入新数据
sqlStr += `REPLACE INTO ${body[key]} `
} else {
// 与主键冲突的数据会忽略插入
sqlStr += `INSERT IGNORE INTO ${body[key]} `
}
}
if (key == 'values') {
let keys = "";
let values = "";
if (Array.isArray(body[key])) {
Object.keys(tableItem).forEach(m => {
keys += m + ',';
})
keys = keys.replace(/^(\s|,)+|(\s|,)+$/g, '');
body[key].forEach(n => {
values += `(`;
Object.keys(tableItem).forEach(mk => {
if (mk in n) {
if (tableItem[mk]['dataType'] == 'number') {
if (Number(n[mk]) == 0 || Number(n[mk])) {
values += `'${ Number(n[mk])}',`;
} else {
values += '-1,';
}
} else {
values += `'${ n[mk]? sqliteEscape(n[mk]) : ""}',`;
}
} else {
if (tableItem[mk]['dataType'] == 'number') {
values += '-1,';
} else {
values += '"",';
}
}
})
values = values.replace(/^(\s|,)+|(\s|,)+$/g, '');
values += `),`;
})
// console.log('值',values)
keys = keys.replace(/^(\s|,)+|(\s|,)+$/g, '');
values = values.replace(/^(\s|,)+|(\s|,)+$/g, '');
sqlStr += `(${keys}) VALUES ${values}`;
} else {
// console.log('进入222')
Object.keys(body[key]).forEach(s => {
keys += s + ',';
// values+=body[key][s]+',';
if (typeof body[key][s] == 'string') {
values += `'${sqliteEscape(body[key][s]) }',`;
} else {
values += `${body[key][s]},`;
}
});
keys = keys.replace(/^(\s|,)+|(\s|,)+$/g, '');
values = values.replace(/^(\s|,)+|(\s|,)+$/g, '');
sqlStr += `(${keys}) VALUES(${values})`;
}
}
})
// if(body['into']== 'Members') console.log('新增sql语句',sqlStr)
// console.log('新增sql语句',sqlStr)
return new Promise((resolve, reject) => {
plus.sqlite.executeSql({
name: DbName,
sql: sqlStr,
success: async (e) => {
// console.log('插入成功...' + sqlStr)
if (body['return']) {
// console.log(body['into'])
let rowId = await getLastId(body['into']);
resolve(rowId);
} else {
resolve(e);
}
},
fail(e) {
console.log('e----', e)
console.log('sqlStr', sqlStr)
reject(e);
}
})
})
}To be compatible with sqlite, I loop through the query objects of the jsstore and concatenate them into sqlite syntax. Would it be easier to implement using sqlweb?
No, I think this is fine. In SqlWeb - syntax might be little different but feel free to experiment it.
Because the way to loop jsstore query objects and concatenate them into sqlite syntax is a bit complicated, if sqlweb could pass in the sqlite syntax directly, then I wouldn't have to loop concatenate syntax。
One more question, is batch updating with a transaction faster than direct updating?