asyncpg.exceptions.AmbiguousParameterError: 无法确定参数的数据类型

Question

我正在创建一个用FastAPI进行搜索的接口，但遇到了这个错误：

asyncpg.exceptions.AmbiguousParameterError: could not determine data type of parameter $2

我在queries.py文件中定义了我的SQL查询：

GET_LOG_QUERY = """
SELECT *
FROM activities
WHERE entity = :entity
AND types_id = :types_id
AND (
    :search IS NULL OR :search = '' 
    OR full_name ILIKE :search
    OR email ILIKE :search
)

"""

这是我的数据仓库：

class LogActivitiesRepository(BaseRepository):
    def __init__(self, db):
        super().__init__(db=db)

    async def get_activities(self, types_id: str, entity: str, search: Optional[str] = None) -> List[LogActivityModel]:
        search_pattern = f"%{search}%" if search else "%"
        records = await self.db.fetch_all(
            query=GET_LOG_QUERY,
            values={
                "entity": entity_name,
                "types_id": types_id,
                "search": search_pattern
            }
        )
        if not records:
            return []
        return [LogActivityModel(**record._mapping) for record in records]

如果我不考虑搜索字段，查询集运行得非常好，就像下面这个查询集：

GET_LOG_QUERY = """ SELECT * FROM user_activities WHERE entity_name = :entity_name AND tenant_id = :tenant_id; """

Answer 1

PostgreSQL不知道:search的值是什么类型，因为在查询的任何表中都没有定义这个值：

test# select $1 is null \bind 'x' \g
ERROR:  could not determine data type of parameter $1

你可以尝试在比较的右侧使用:search时进行类型转换，这样可能会成功：

GET_LOG_QUERY = """
SELECT *
FROM activities
WHERE entity = :entity
AND types_id = :types_id
AND (
    :search::varchar IS NULL OR :search::varchar = '' 
    OR full_name ILIKE :search
    OR email ILIKE :search
)
"""

不过，最好还是在Python层面先计算这个布尔表达式，然后把:search IS NULL OR :search = ''替换成计算的结果。

GET_LOG_QUERY = """
SELECT *
FROM activities
WHERE entity = :entity
AND types_id = :types_id
AND (
    :no_pattern 
    OR full_name ILIKE :search
    OR email ILIKE :search
)
"""

...

        no_pattern = not bool(search_pattern)
        records = await self.db.fetch_all(
            query=GET_LOG_QUERY,
            values={
                "entity": entity_name,
                "types_id": types_id,
                "search": search_pattern,
                "no_pattern": no_pattern,
            }
        )