GORM multi-column fuzzy query and deletion mark conflict: How to avoid querying deleted records?

Solutions to conflict between GORM multi-column fuzzy query and soft deletion

When using GORM for database operations, multiple columns of fuzzy queries and soft deletion filtering are often required at the same time. If handled incorrectly, it may cause the query results to contain deleted records. This article will explain how to effectively avoid this situation.

Question: Suppose you need to fuzzy match username and nickname columns according to the keywords, and exclude records with is_del of 1 (deleted). Using where and or methods directly to combine queries may result in errors.

Error example:

 _db = _db.Where("username like ?", "%" keyword "%").Or("nickname like ?", "%" keyword "%")

The above code uses Or method, resulting in that even if is_del is 1, the record will be queryed as long as username or nickname matches the keyword.

Solution: Merge all the conditions into one Where statement:

 _db = _db.Where(" (username LIKE ? OR nickname LIKE ?) AND is_del = ?", "%" keyword "%", "%" keyword "%", 0)

The improved code combines the fuzzy matching conditions of username and nickname using brackets and concatenates with is_del = 0 using AND conditions. In this way, only records that meet the fuzzy matching and undeletion conditions will be queried, effectively avoiding the incorrect check of soft deleted records.

Through this method, it is possible to ensure that GORM multi-column fuzzy query and soft deletion conditions take effect at the same time, thereby obtaining accurate query results. Remember to use brackets to ensure operator priority and avoid logical errors.

The above is the detailed content of GORM multi-column fuzzy query and deletion mark conflict: How to avoid querying deleted records?. For more information, please follow other related articles on the PHP Chinese website!