我有四個表：

• 頁
  • 維基頁面ID
  • 網址
• 收藏
  • 集合ID
  • 維基頁面ID
• PageReference（連接表）
  • 維基頁面ID
  • 參考ID
• 參考
  • 參考ID

對于與page表中一行相對應的任何給定 URL ，我想回傳分配給可能屬于同一集合的任何兄弟頁面的所有參考。

我覺得可能有更有效的方法來做到這一點。到目前為止，我已將其分解為一組查詢，并且只完成了一半。

    # Get all ReferenceData from Reference table for ID
    wikiPageID = ""
    url = request.json['url']

    # Get WikiPageID from Page table where Page.url = request.json['url']
    for item in db.session.query(PageModel).filter(PageModel.url == url).values('wikiPageID'):
        wikiPageID = item[0]
        #print(wikiPageID)

    for item in 
    db.session.query(CollectionModel).filter(CollectionModel.wikiPageID == wikiPageID).values('collectionID'):
        collectionID = item[0]
        #print(collectionID)

    # Get all WikiPageID's from Collection table where WikiPageID EXISTS
    result1 = db.session.query(CollectionModel).filter(CollectionModel.collectionID == collectionID).values('wikiPageID')

    ## THIS IS WHERE IT STARTS GOING PEAR SHAPED
    # Get all ReferenceID from PageReference where WikiPageID EXISTS
    result = db.session.query(PageReferenceModel).filter(PageReferenceModel.wikiPageID.in_(result1) )

    schema = CollectionSchema()
    return schema.dump(result, many=True), 200

在我的最后一個查詢中，我試圖嵌套以前的查詢結果。因為我試圖通過從上一個結果傳遞的 ID 來過濾結果。我覺得我在做這一切都是錯誤的，無法弄清楚從哪里開始。

SQLAlchemy 模型

class CollectionModel(db.Model):
    __tablename__ = "Collection"

    collectionID = db.Column(db.Integer, primary_key=True)
    wikiPageID = db.Column(db.Integer, db.ForeignKey('Page.wikiPageID'), nullable=False)
    Page = db.relationship("PageModel")

class PageModel(db.Model):
    __tablename__ = "Page"

    wikiPageID = db.Column(db.Integer, primary_key=True)
    langCode = db.Column(db.String, nullable=True)
    title = db.Column(db.String, nullable=True)
    url = db.Column(db.String, nullable=True)
    summary = db.Column(db.String, nullable=True)
    lastUpdated = db.Column(db.String, nullable=True)

class PageReferenceModel(db.Model):
    __tablename__ = "PageReference"

    pageReferenceID = db.Column(db.Integer, primary_key=True)
    wikiPageID = db.Column(db.Integer, db.ForeignKey('Page.wikiPageID'), nullable=False)
    referenceID = db.Column(db.Integer, db.ForeignKey('Reference.referenceID'), nullable=False)
    Page = db.relationship("PageModel")
    Reference = db.relationship("ReferenceModel")


class ReferenceModel(db.Model):
    __tablename__ = "Reference"

    referenceID = db.Column(db.Integer, primary_key=True)
    langCode = db.Column(db.String, nullable=False)
    isGov = db.Column(db.String, nullable=False)
    type = db.Column(db.String, nullable=True)
    title = db.Column(db.String, nullable=True)
    domain = db.Column(db.String, nullable=True)
    suffix = db.Column(db.String, nullable=True)
    description = db.Column(db.String, nullable=True)
    publishDate = db.Column(db.String, nullable=True)
    url = db.Column(db.String, nullable=True)
    youtubeId = db.Column(db.String, nullable=True)
    langCheck = db.Column(db.Boolean, nullable=True)

uj5u.com熱心網友回復：

解決它：

我最終對先前查詢的結果做了一系列 if 陳述句。仍然很可能效率低下。但它有效。

收藏 GET

    # Get all ReferenceData from Reference table for ID
    wikiPageID = ""
    url = request.json['url']


   # Get all ReferenceData from Reference table for ID
    wikiPageID = ""
    url = request.json['url']

    # Get WikiPageID from Page table where Page.url = request.json['url']
    for item in db.session.query(PageModel).filter(PageModel.url == url).values(PageModel.wikiPageID):
        wikiPageID = item[0]
        #print(wikiPageID)

    for item in db.session.query(CollectionModel).filter(CollectionModel.wikiPageID == wikiPageID).values(CollectionModel.collectionID):
        collectionID = item[0]
        #print(collectionID)

    # Get all WikiPageID's from Collection table where WikiPageID EXISTS
    wikiPageIDResult = db.session.query(CollectionModel).filter(CollectionModel.collectionID == collectionID).values(CollectionModel.wikiPageID)

    wikiPageArray = []
    for item in wikiPageIDResult:
        wikiPageArray.append(item[0])
        print("Wiki Page Array: ", wikiPageArray)

    #Get all ReferenceID from PageReference where WikiPageID EXISTS
    wikiPageReferenceResults = db.session.query(PageReferenceModel).filter(PageReferenceModel.wikiPageID.in_(wikiPageArray)).values(PageReferenceModel.referenceID)

    wikiPageReferenceArray = []
    for item in wikiPageReferenceResults:
        wikiPageReferenceArray.append(item[0])
        print("Wiki Page Reference Array: ", wikiPageReferenceArray)

    referenceResult = db.session.query(ReferenceModel).filter(ReferenceModel.referenceID.in_(wikiPageReferenceArray))

    schema = ReferenceSchema()
    return schema.dump(referenceResult, many=True), 200

uj5u.com熱心網友回復：

你應該使用加入：

    result = db.session.query(PageReferenceModel)\
             .join(CollectionModel, CollectionModel.wikiPageId == PageReferenceModel.wikiPageId)\
             .join(PageModel, PageModel.wikiPageId == PageReferenceModel.wikiPageId)\
             .filter(PageModel.url == url)

    schema = CollectionSchema()
    return schema.dump(result, many=True), 200

標籤：烧瓶 sqlalchemy

上一篇：GoogleCloud網路應用不顯示自定義Python代碼

下一篇：通過多重論證昏迷值燒瓶sqlalchemypostgresql過濾