ORとかNOTとか使ってググったこと無かったんだがとりあえずちょろっと下調べをして挙動をまとめた
基本的な形は
んで
- NOTはどこで指定してもいい(NOTだけでも検索可能)
- ORは必ずANDの後
のようなので
- 出現順にパースして
- "から"までは連続するキーワード
- 条件 OR 条件 OR 条件... のような形ならその全体を括弧で囲む
- 先頭、NOTの後のORは無視
- %か_がキーワードに含まれてたらエスケープ
ぐらいのルールでOKとしてみる
全体
var getQuery = function(str, target){ if(!str || !target) return; str += " "; var arr = []; var inside = false; var inPhrase = false; var tempStr = ""; var logic = ""; for(var i = 0, i_n = str.length; i < i_n; i++){ var s = str.charAt(i); if(s.match(/\s/)){ if(inside){ if(inPhrase){ tempStr += s; }else{ if(tempStr.match(/^not$/i)){ logic = "NOT"; }else if( tempStr.match(/^or$/i)){ logic = "OR"; }else if( tempStr.match(/^and$/i)){ logic = "AND"; }else{ arr.push({ "logic" : (logic? logic : "AND"), "phrase": tempStr }); logic = ""; } tempStr = ""; inside = false; } } }else if('"' == s){ if(inside){ if(inPhrase){ inside = inPhrase = false; if(tempStr){ arr.push({ "logic" : (logic? logic : "AND"), "phrase": tempStr }); } logic = ""; tempStr = ""; }else{ //無視でいいか } }else{ inside = inPhrase = true; } }else{ if (!inside){ if ("-" == s) { if(i + 1 < i_n){ if(!str.charAt(i + 1).match(/\s/)){ logic = "NOT"; } } continue; } } tempStr += s; inside = true; } } var retVal = []; var escape = "\\"; var whereSql = " "; var onAnd = false; var first = true; for(var i = 0, i_n = arr.length; i < i_n; i++){ var logic = arr[i].logic; var phrase = arr[i].phrase; var hasSC = ( -1 != phrase.indexOf("_") || -1 != phrase.indexOf("%") ); phrase = "%" + phrase.replace("%", escape + "%").replace("_", escape + "_") + "%"; if ("AND" == logic){ if(!first){ whereSql += " AND "; } first = false; onAnd = true; if(i + 1 < i_n){ if("OR" == arr[i + 1].logic){ whereSql += "("; } } whereSql += target + " LIKE ?" + (hasSC ? " ESCAPE '\\'": ""); retVal.push(phrase); }else if("OR" == logic){ if(!onAnd){ continue; } whereSql += " OR " + target + " LIKE ?" + (hasSC ? " ESCAPE '\\'": ""); retVal.push(phrase); if(i + 1 < i_n){ if("OR" == arr[i + 1].logic){ continue; } } whereSql += ")"; }else if("NOT" == logic){ if(!first){ whereSql += " AND "; } first = false; onAnd = false; whereSql += "NOT " + target + " LIKE ?" + (hasSC ? " ESCAPE '\\'": ""); retVal.push(phrase); } } return { sql : whereSql, val : retVal }; }
var ret = getQuery("hoge hage", "title");
とすると、戻り値の中身は
ret.sql->title LIKE ? AND title LIKE ?
ret.val[0]->%hoge%
ret.val[1]->%hage%
for(var i = 0, i_n = ret.val.length; i < i_n; i++){ ret.sql = ret.sql.replace(/\?/,"'" + ret.val[i] + "'"); }
して見やすくすると
「title LIKE '%hoge%' AND title LIKE '%hage%'」
適当なSQL文 + "WHERE " + ret.sql として使う
gearsなのでret.valはexecuteの第二引数として渡せばOK
getQuery("hoge OR hage -foo", "title");
なら
(title LIKE '%hoge%' OR title LIKE '%hage%') AND NOT title LIKE '%foo%'
getQuery('-foo "hoge hage" bar', "title");
NOT title LIKE '%foo%' AND title LIKE '%hoge hage%' AND title LIKE '%bar%'
getQuery("ho_ge foo%bar", "title");
title LIKE '%ho\_ge%' ESCAPE '\' AND title LIKE '%foo\%bar%' ESCAPE '\'
複数のフィールドを対象にするときは
var whereSql = ""; var sqlValArr = []; var ret = getQuery("hoge", "title"); if(ret){ if(0 != ret.val.length){ if(whereSql){ whereSql += " AND"; } whereSql += ret.sql; sqlValArr = sqlValArr.concat(ret.val); } } var ret = getQuery("hage", "url"); if(ret){ if(0 != ret.val.length){ if(whereSql){ whereSql += " AND"; } whereSql += ret.sql; sqlValArr = sqlValArr.concat(ret.val); } } if( whereSql ){ whereSql = " WHERE" + whereSql; } try{ var rs; if(whereSql){ rs = db.execute('SELECT COUNT(*) FROM table1' + whereSql, sqlValArr); }else{ rs = db.execute('SELECT COUNT(*) FROM table1'); }
みたいな感じで