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

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.

CodePudding user response：

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,
    )