In standard SQL, I might use a subquery with filter:
code = func.json_value(User.meta, '$.code').label('code')
q = db.query(code).subquery()
db.query(q).filter(q.c.code!=None).all()
Le vendredi 22 juin 2018 10:08:04 UTC+3, yoch....@gmail.com a écrit :
>
> Yes, I tried that. It produces the following SQL:
>
> SELECT json_value(user.meta, :json_value_1) AS code
> FROM user
> HAVING json_value(user.meta, :json_value_1) IS NOT NULL
>
> This make sense in general, because in MySQL you cannot refer to an alias
> in the WHERE part.
> But I want to refer directly to the alias, which is possible in HAVING
> clauses (MySQL extension).
>
>
> Le vendredi 22 juin 2018 03:40:23 UTC+3, Mike Bayer a écrit :
>>
>> Did you try using the code object itself? There's some dialect-specific
>> rules for when it rewrites the expression vs. uses the label name in the
>> expression but it may work (am traveling and can't check the code right now)
>>
>> On Thu, Jun 21, 2018, 10:10 AM <yoch....@gmail.com> wrote:
>>
>>> Hi,
>>>
>>> I want to translate this MariaDB query to sqlalchemy :
>>>
>>> SELECT JSON_VALUE(user.meta, '$.code') AS code
>>> FROM user
>>> HAVING code IS NOT NULL;
>>>
>>> (Note: the use of HAVING is because I want to filter on the alias
>>> `code`.)
>>>
>>> I don't find a simple way to use the alias in comparison, currently I
>>> wrote this code :
>>>
>>> code = func.json_value(User.meta, '$.code').label('code')
>>> db.query(code).having(literal_column(code.name)!=None).all()
>>>
>>> Is this correct ? Is there any better way to do that ?
>>>
>>> Thank you
>>>
>>> --
>>> SQLAlchemy -
>>> The Python SQL Toolkit and Object Relational Mapper
>>>
>>> http://www.sqlalchemy.org/
>>>
>>> To post example code, please provide an MCVE: Minimal, Complete, and
>>> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
>>> description.
>>> ---
>>> You received this message because you are subscribed to the Google
>>> Groups "sqlalchemy" group.
>>> To unsubscribe from this group and stop receiving emails from it, send
>>> an email to sqlalchemy+...@googlegroups.com.
>>> To post to this group, send email to sqlal...@googlegroups.com.
>>> Visit this group at https://groups.google.com/group/sqlalchemy.
>>> For more options, visit https://groups.google.com/d/optout.
>>>
>>
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
