SQLAlchemy query renders .filter() as "WHERE false"

Question

My issues comes down to the following code runned with Python3.8 and SQLAlchemy1.3:

subquery = (
    session.query(
        Article.id,
        SubscriberArticle.subscriber_id,
        SubscriberArticle.data,
        SubscriberArticle.is_activated,
    )
    .join(SubscriberArticle)
    .filter(and_(
        SubscriberArticle.subscriber_id == 1234,
        Article.is_activated is True
    ))
    .subquery()
)
query = (
    session.query(
        Article.id,
        Article.name,
        subquery.c.subscriber_id,
        subquery.c.data,
        subquery.c.is_activated,
    )
    .join(subquery, subquery.c.id == Article.id)
)

The where clause is not what i expected, here is the SQL request i expected:

SELECT
    article.id,
    article.name,
    tmp.subscriber_id,
    tmp.data,
    tmp.is_activated
FROM
    script_pixel
LEFT JOIN
    (
        SELECT
                article.id,
                subscriber_article.subscriber_id,
                subscriber_article.data,
                subscriber_article.is_activated
        FROM
            script_pixel
        LEFT JOIN
            subscriber_script_pixel
        ON
            subscriber_article.script_pixel_id = article.id
        WHERE
            subscriber_article.subscriber_id = 1234
            AND
            article.is_activated = true
    ) as tmp
ON
    tmp.id = article.id;

But when i do a print("query", str(query)) the result is completely different:

SELECT 
  script_pixel.id AS script_pixel_id, 
  script_pixel.name AS script_pixel_name, 
  anon_1.subscriber_id AS anon_1_subscriber_id,
  anon_1.data AS anon_1_data,
  anon_1.is_activated AS anon_1_is_activated 
FROM 
  script_pixel 
  JOIN (
    SELECT 
      script_pixel.id AS id, 
      subscriber_script_pixel.subscriber_id AS subscriber_id, 
      subscriber_script_pixel.data AS data, 
      subscriber_script_pixel.is_activated AS is_activated 
    FROM 
      script_pixel 
      JOIN subscriber_script_pixel ON script_pixel.id = subscriber_script_pixel.script_pixel_id 
    WHERE 
      false
  ) AS anon_1 ON anon_1.id = script_pixel.id

As you can see in my subquery i don't have my where clause, instead i have a where false comes out of nowhere.

ljmc · Accepted Answer · 2022-06-07T19:41:31.423

3

Your issue is with Article.is_activated is True, this cannot be compiled by SQLAlchemy, resulting in WHERE false or WHERE 1=0.

    .filter(and_(
        SubscriberArticle.subscriber_id == 1234,
        Article.is_activated is True
    ))

You can use Article.is_activated == True instead. Also, you don't realy need the and_ function as Query.filter() accepts any number of criterion and will join them using and_ for you.

    .filter(
        SubscriberArticle.subscriber_id == 1234,
        Article.is_activated == True,
    )

edited Jun 07 '22 at 19:41

answered Jun 07 '22 at 19:35

ljmc

4,830
2
7
26

You can also use the `is_` operator `Article.is_activated.is(True)` - [docs](https://docs.sqlalchemy.org/en/14/core/sqlelement.html#sqlalchemy.sql.expression.ColumnOperators.is_) – snakecharmerb Jun 08 '22 at 08:44
2

Note `IS ` created by `is_(True/False)` is not compatible with all platforms, quoting docs: However, explicit usage of IS may be desirable if comparing to boolean values **on certain platforms**. – ljmc Jun 08 '22 at 10:47
1

Some platforms can also have issues with a bare "boolean" value, e.g., `.where(Article.is_activated)` which renders as `WHERE foo.is_activated` vs. `.where(Article.is_activated == True)` which renders as `WHERE foo.is_activated = true`. In theory the former would be the preferred approach, but I've seen cases where the latter was required (despite what the purists say). – Gord Thompson Jun 08 '22 at 12:55

SQLAlchemy query renders .filter() as "WHERE false"

1 Answers1