英文:
Using jsonb_array_elements in sqlalchemy select_from and join
问题
我正在使用SQLAlchemy ORM，并尝试生成类似以下的PostgreSQL查询：
SELECT resources.*
FROM histories, jsonb_array_elements_text(histories.reported_resources) as report_resource_name
JOIN resources ON resources.resource_name = report_resource_name
WHERE histories.id = :id
到目前为止，我得到了这个：
query = (
select([
Resource
])
.select_from(
History,
func.jsonb_array_elements(History.reported_resources).alias('report_resource_name'))
.join(Resource, Resource.resource_name == text('report_resource_name'))
.where(History.id == 1)
)
但是出现了错误：
InvalidRequestError: 无法确定要从哪个FROM子句加入，有多个可以加入到此实体的FROM子句。请使用
.select_from()方法来建立明确的左侧，并提供一个明确的ON子句（如果尚不存在）以帮助解决歧义。
如何在SQLAlchemy的.select_from()中将资源表连接到jsonb_array_elements结果？
最小模型表和输入数据如下：
class History(Base):
__tablename__ = 'histories'
id = Column(Integer, primary_key=True)
reported_resources = Column(JSONB)
class Resource(Base):
__tablename__ = 'resources'
id = Column(Integer, primary_key=True)
resource_name = Column(String)
Resource
id | resource_name
--------
1 | machine1
2 | machine2
3 | operator1
4 | operator4
History
id | reported_resources
-------
1 | ['machine2', 'operator4']
请注意，我已经将代码中的错误History.id = 1修正为History.id == 1。
英文:
I am using SQLAlchemy ORM and trying to figure out how to produce a PostgreSQL query like the following:
SELECT resources.*
FROM histories, jsonb_array_elements_text(histories.reported_resources) as report_resource_name
JOIN resources ON resources.resource_name = report_resource_name
WHERE histories.id = :id
So far I got this:
query = (
select([
Resource
])
.select_from(
History,
func.jsonb_array_elements(History.reported_resources).alias('report_resource_name'))
.join(Resource, Resource.resource_name == text('report_resource_name'))
.where(History.id = 1)
)
But the error says:
> InvalidRequestError: Can't determine which FROM clause to join from, there are multiple FROMS which can join to this entity. Please use the .select_from() method to establish an explicit left side, as well as providing an explicit ON clause if not present already to help resolve the ambiguity.
How can I join the resources table ON the jsonb_array_elements result in .select_from() using SQLAlchemey?
Minimum model table and input data like following:
class History(Base):
__tablename__ = 'histories'
id = Column(Integer, primary_key=True)
reported_resources = Column(JSONB)
class Resource(Base):
__tablename__ = 'resources'
id = Column(Integer, primary_key=True)
resource_name = Column(String)
Resource
id | resource_name
--------
1 | machine1
2 | machine2
3 | operator1
4 | operator4
History
id | reported_resources
-------
1 | ['machine2', 'operator4']
答案1
得分: 1
我对ORM方言不太熟悉，但重写你的SQL语句可能有帮助：
SELECT resources.*
FROM histories
CROSS JOIN LATERAL jsonb_array_elements_text(histories.reported_resources) AS rr(report_resource_name)
JOIN resources ON resources.resource_name = rr.report_resource_name
WHERE histories.id = :id
（在这种情况下，“LATERAL”关键字是可选的。）
参考：
- 在Postgres中[FROM x, y]是什么意思？
- https://stackoverflow.com/questions/28550679/what-is-the-difference-between-a-lateral-join-and-a-subquery-in-postgresql/28557803#28557803
英文:
I am not fluid with the ORM dialect, but it should help to rewrite your SQL statement as:
SELECT resources.*
FROM histories
CROSS JOIN LATERAL jsonb_array_elements_text(histories.reported_resources) AS rr(report_resource_name)
JOIN resources ON resources.resource_name = rr.report_resource_name
WHERE histories.id = :id
(The LATERAL keyword being optional in this case.)
See:
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论