I'm using the MySQLicious type schema described here for a simple tagging system. I've read some alternative implementations of tagging schema in 4 different SO threads, and this suits my needs best.
我正在为一个简单的标记系统使用这里描述的MySQLicious类型模式。我读过4个不同的SO线程中的标签模式的一些替代实现，这最适合我的需要。
A collection of entries have the tags "apple banana orange" and "strawberry banana lemon", and I'm trying to find the Elixir/SQLAlchemy equivalent statement to
一组条目的标签有“苹果香蕉橙”和“草莓香蕉柠檬”，我试图找到与之对应的Elixir/SQLAlchemy语句
SELECT * FROM table WHERE tags LIKE "%banana%";
I haven't been able to find any such way to structure a Class.query.filter/filter_by() command, and can't see a similar method in the documentation for either module. Is there a simple way to do this? Or should I just use raw SQL.
我还没有找到任何这样的方法来构造Class.query.filter/filter_by()命令，而且在这两个模块的文档中都看不到类似的方法。有简单的方法吗?或者我应该使用原始SQL。
Extra Question: A disadvantage of the MySQLicious schema is the case where I may wish to search for "%apple%" but have "pineapple" returned. Is there a high level way to deal with this test case? Or should I just include a leading space in each query?
额外问题:MySQLicious模式的一个缺点是，我可能希望搜索“%apple%”，但返回“菠萝”。是否有一个高级的方法来处理这个测试用例?还是应该在每个查询中包含一个前导空间?
n.B: For those who care, this is my first experience with databases, so I may be overlooking core advantages of the schema mentioned in other threads. My application is for logging a sentence or two about a task completed, with columns [TaskID, Tags, Notes, StartTime, StopTime, TimeTaken], a bit like a simple journal. Mostly for tutorial purposes. I want to be able to search by individual tags to find out roughly how much time I spend on a given task.
n。B:对于那些关心的人来说，这是我第一次使用数据库，所以我可能忽略了其他线程中提到的模式的核心优势。我的应用程序是记录一两个完成任务的句子，包含列[TaskID、标记、注释、开始时间、停止时间、所花费的时间]，有点像一个简单的日志。主要用于教程。我希望能够通过单独的标记搜索，以大致了解我在给定任务上花费了多少时间。
3 个解决方案
#1
86
Each column has .like method, which can be used as filter clause.
每个列都有.like方法，可以用作筛选子句。
>>> Note.query.filter(Note.message.like("%somestr%")).all()
[]
#2
5
Adding to the above answer, whoever looks for a solution, you can also try 'match' operator instead of 'like'. Do not want to be biased but it perfectly worked for me in Postgresql.
除了上面的答案，无论谁在寻找解决方案，你也可以尝试“match”运算符而不是“like”。我不想有偏见，但它在Postgresql中非常适合我。
Note.query.filter(Note.message.match("%somestr%")).all()
It inherits database functions such as CONTAINS and MATCH. However, it is not available in SQLite.
它继承了包含和匹配的数据库函数。但是，它在SQLite中不可用。
For more info go Common Filter Operators
要了解更多信息，请使用常用的过滤器操作符。
#3
3
try this code
试试这个代码
output = dbsession.query(<model_class>).filter(<model_calss>.email.ilike('%' + < email > + '%'))
#1
86
Each column has .like method, which can be used as filter clause.
每个列都有.like方法，可以用作筛选子句。
>>> Note.query.filter(Note.message.like("%somestr%")).all()
[]
#2
5
Adding to the above answer, whoever looks for a solution, you can also try 'match' operator instead of 'like'. Do not want to be biased but it perfectly worked for me in Postgresql.
除了上面的答案，无论谁在寻找解决方案，你也可以尝试“match”运算符而不是“like”。我不想有偏见，但它在Postgresql中非常适合我。
Note.query.filter(Note.message.match("%somestr%")).all()
It inherits database functions such as CONTAINS and MATCH. However, it is not available in SQLite.
它继承了包含和匹配的数据库函数。但是，它在SQLite中不可用。
For more info go Common Filter Operators
要了解更多信息，请使用常用的过滤器操作符。
#3
3
try this code
试试这个代码
output = dbsession.query(<model_class>).filter(<model_calss>.email.ilike('%' + < email > + '%'))