如何使用 SQLAlchemy 的表达式语言编写条件子句? [英] How can I write conditional clauses using SQLAlchemy's expression language?

问题描述

因此，我编写了一个相当复杂的查询，用于从两个表中获取并连接一堆数据.

So, I wrote quite a complicated query that fetches and joins a bunch of data from two tables.

SELECT
    /* Common attributes */
    carrier.name,
    carrier.notes,
    carrier.turnaround,

    /* Either per-reseller price, generic reseller price or default price */
    IFNULL(
        rsu.price,
        IF(
            (
                carrier.reseller_price != IS NOT NULL AND
                carrier.reseller_price != 0
            ),
            carrier.reseller_price,
            carrier.price
        )
    ) AS price,
    IFNULL(
        rsu.price_barred,
        IF(
            (
                carrier.reseller_price_barred IS NOT NULL AND
                carrier.reseller_price_barred != 0
            ),
            carrier.reseller_price_barred,
            carrier.price_barred
        )
    ) AS price_barred
FROM
    `core_carrier` AS carrier
LEFT OUTER JOIN
    `core_resellerunlock` AS rsu ON (
        rsu.carrier_id = carrier.id AND
        rsu.reseller_id = 1
    )

有人可以建议一种使用 SQLAlchemy 查询构建器重写它的方法吗?我不确定那些 SELECT ... IF 子句是否可行.

Can someone suggest a way of rewriting this using the SQLAlchemy query builder? I'm not sure if it's even possible with those SELECT ... IF clauses.

我不想使用 ORM 来执行此操作(据我所知，仅使用 SQLAlchemy ORM 执行此操作是不可能的).我只是在寻找一种使用 SQLAlchemy 的核心或多或少可移植的方法.

I don't want to do this using ORM (and as far as I know, executing this using SQLAlchemy ORM purely is not possible). I'm just looking for a more or less portable way of doing this using SQLAlchemy's core.

推荐答案

您可以使用 case 函数.这不会生成与您提供的完全相同的 SQL，但可以生成功能等效的内容.

You can include a conditional in your query using the case function. This won't produce exactly the SQL you presented, but can produce something functionally equivalent.

这是一个简单的例子，carrier 和 rsu 变量引用 Table 对象，case 引用到上述功能:

Here's a simple example, with the carrier and rsu variables referring to Table objects, and case referring to the aforementioned function:

join = carrier.join(rsu, (rsu.c.carrier_id == carrier.c.id) & (rsu.c.reseller_id == 1))
query = join.select([
    carrier.c.name,
    carrier.c.notes,
    carrier.c.turnaround,
    case([
        (
            rsu.c.price_barred == None,
            case([
                (
                    (carrier.c.reseller_price != None) & (carrier.c.reseller_price != 0),
                    carrier.c.reseller_price
                )
            ],
            else_=carrier.c.price
        )
    ]
])

您可能会发现创建一些辅助函数来表示更高级别的 IFNULL 和 IF 函数以提高查询的可读性会很方便.例如:

You might find it convenient to make some helper functions to represent your higher-level IFNULL and IF functions to improve the readability of your query. For example:

def if_(expr, then, else_):
    return case([
        (expr, then)
    ], else_=else_)

这篇关于如何使用 SQLAlchemy 的表达式语言编写条件子句?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！