I have four tables:

• Page
  • wikiPageID
  • url
• Collection
  • collectionID
  • wikiPageID
• PageReference (joining table)
  • wikiPageID
  • referenceID
• Reference
  • referenceID

For any given URL corresponding to a row in the page table, I want to return all the references assigned to any sibling pages which might belong to the same collection.

I feel there may be a more efficient wawy of doing this. So far I've broken it into a set of queries and am only half way there.

    # 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

On my last query I'm attempting to nest the previous query results. As I'm attempting to filter the results by the IDs passed from the previous result. I feel I'm going about this all wrong and can't quite figure where to go from here.

CodePudding user response：

Fixed it:

I ended up doing a series of if statements on the results of the prior queries. Still most likely inefficient. But it works.

    # 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

CodePudding user response：

You should use join:

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

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