我正在使用Elasticsearch在我正在开发的"Reddit clone"Web应用程序中实现搜索功能。我想支持搜索线程、用户和子版块，但是当我输入搜索查询并搜索上述 3 个没有任何匹配项的类别之一时，我得到一个意外的"操作错误"而不是一组空的结果。
如我包含的代码所示，我尝试使用 sqlalchemy.orm.query.Query.all() 函数，该函数返回以下错误：
OperationalError: (sqlite3.OperationalError) near "END": syntax error
[SQL: SELECT user.id AS user_id, user.username AS user_username, user.email AS user_email, user.password_hash AS user_password_hash, user.last_sign_in AS user_last_sign_in
FROM user
WHERE 1 != 1 ORDER BY CASE user.id END]
(Background on this error at: http://sqlalche.me/e/e3q8)
我研究了其他 StackOverflow 帖子，发现 first() 函数在内部处理数据库结果，如果未找到结果，则返回 None，但是当我切换到该函数时，我遇到了这个错误：
OperationalError: (sqlite3.OperationalError) near "END": syntax error
[SQL: SELECT user.id AS user_id, user.username AS user_username, user.email AS user_email, user.password_hash AS user_password_hash, user.last_sign_in AS user_last_sign_in
FROM user
WHERE 1 != 1 ORDER BY CASE user.id END
LIMIT ? OFFSET ?]
[parameters: (1, 0)]
(Background on this error at: http://sqlalche.me/e/e3q8)
检查 SqlAlchemy 的文档，我在两个函数中都没有看到任何提及此错误的内容，并且阅读 Operational Error 的含义，我担心我的数据库设置可能不正确。
app/routes.py：这是处理对以下 URL 发出的搜索请求的路由：http://localhost:5000/search?q=&index=
@app.route('/search', methods=['GET'])
def search():
print 'Hit the /search route!'
if not g.search_form.validate():
return redirect(url_for('index'))
page = request.args.get('page', 1, type=int)
target_index = request.args.get('index', 'thread')
if target_index == 'thread':
results, total = Thread.search(g.search_form.q.data, page, app.config['POSTS_PER_PAGE'])
print 'Called Thread.search(), total results = {}'.format(total['value'])
elif target_index == 'user':
results, total = User.search(g.search_form.q.data, page, app.config['POSTS_PER_PAGE'])
print 'Called User.search(), total results = {}'.format(total['value'])
elif target_index == 'subreddit':
results, total = Subreddit.search(g.search_form.q.data, page, app.config['POSTS_PER_PAGE'])
print 'Called Subreddit.search(), total results = {}'.format(total['value'])
else:
return render_template('404.html')
try:
results = results.all()
except OperationalError:
results = [None]
total = total['value']
next_url = url_for('search', index=target_index, q=g.search_form.q.data, page=page + 1) if total > page * app.config['POSTS_PER_PAGE'] else None
prev_url = url_for('search', index=target_index, q=g.search_form.q.data, page=page - 1) if page > 1 else None
results_list = zip(results, [None] * len(results)) # Temporarily to match expected input for template
return render_template('search.html', title=_('Search'), results_list=results_list, next_url=next_url, prev_url=prev_url, query=g.search_form.q.data, index=target_index)
应用/型号.py：
class SearchableMixin(object):
@classmethod
def search(cls, expression, page, per_page):
ids, total = query_index(cls.__tablename__, expression, page, per_page)
if total == 0:
return cls.query.filter_by(id=0), 0
when = []
for i in range(len(ids)):
when.append((ids[i], i))
return cls.query.filter(cls.id.in_(ids)).order_by(
db.case(when, value=cls.id)), total
@classmethod
def before_commit(cls, session):
session._changes = {
'add': list(session.new),
'update': list(session.dirty),
'delete': list(session.deleted)
}
@classmethod
def after_commit(cls, session):
for obj in session._changes['add']:
if isinstance(obj, SearchableMixin):
add_to_index(obj.__tablename__, obj)
for obj in session._changes['update']:
if isinstance(obj, SearchableMixin):
add_to_index(obj.__tablename__, obj)
for obj in session._changes['delete']:
if isinstance(obj, SearchableMixin):
remove_from_index(obj.__tablename__, obj)
session._changes = None
@classmethod
def reindex(cls):
for obj in cls.query:
add_to_index(cls.__tablename__, obj)
db.event.listen(db.session, 'before_commit', SearchableMixin.before_commit)
db.event.listen(db.session, 'after_commit', SearchableMixin.after_commit)
# Below is one model that implements SearchableMixin to allow searching # for users. Thread and Subreddit models follow the same logic.
class User(db.Model, UserMixin, SearchableMixin):
__searchable__ = ['username']
id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(64), index=True, unique=True)
# <Remaining User model fields here...>
app/search.py：(包含用于查询 Elasticsearch 索引的底层搜索函数)
def add_to_index(index, model):
if not app.elasticsearch:
return
payload = {}
for field in model.__searchable__:
payload[field] = getattr(model, field)
app.elasticsearch.index(index=index, doc_type=index, id=model.id,
body=payload)
def remove_from_index(index, model):
if not app.elasticsearch:
return
app.elasticsearch.delete(index=index, doc_type=index, id=model.id)
def query_index(index, query, page, per_page):
if not app.elasticsearch:
return [], 0
search = app.elasticsearch.search(
index=index,
body={'query': {'multi_match': {'query': query, 'fields': ['*']}},
'from': (page - 1) * per_page, 'size': per_page})
ids = [int(hit['_id']) for hit in search['hits']['hits']]
return ids, search['hits']['total']
正如我包含的应用程序/路由.py所示，我通过捕获 OperationalError 并将其视为未找到结果的指标来解决此问题，但由于 all() 文档没有提及它，我没想到会引发此异常。
通过将检索到的所有字段隐藏在星号后面，我简化了生成的查询。
SELECT user.*
FROM user
WHERE 1 != 1
ORDER BY CASE user.id END
首先，只要1 != 1是 where 子句，此查询就不会返回任何值，因为根据定义，这是假的。有可能ids是空的吗？这也可以很好地解释格式不正确的CASE语句，这是错误的根源。通常，case(dict(a=1, b=2), value=User.name)应该导致CASE WHEN name = 'a' THEN 1 WHEN name = 'b' THEN 2 END，这将正确执行。