work.py

from collections import defaultdict
from mbdata import models
from sqlalchemy.orm import joinedload
from brainzutils import cache
from critiquebrainz.frontend.external.musicbrainz_db import mb_session, DEFAULT_CACHE_EXPIRATION


"""Example Function to get information related to work"""
def get_work_by_id(id, relations=None):
  
  if relations is None:
    relations = []
  
  key = cache.gen_key(id)
  work = cache.get(key)
  if not work:
    with mb_db.mb_engine.connect() as connection:
       result = connection.execute(sqlalchemy.text("""
        SELECT work.id,
        work.name,
        wt.id as type,
        iswc.iswc
        FROM work
  INNER JOIN work_type wt
        ON work.type = wt.id
  INNER JOIN iswc
        ON work.id = iswc.work
        WHERE work.gid = :work_id
        GROUP BY work.id, wt.id, iswc.work
      """),{
         "work_id" = id 
       })
       row = result.fetchone()
       work = dict(row)
       # The data fetched needs to be properly arranged
       # in dictionaries.
       if not row:
          raise NoDataFoundError("No data Found")

          
    if "url-rels" in relations:
        with mb_db.db_engine.connect() as connection:
            result = connection.execute(sqlalchemy.text("""
            SELECT url.id,
                   url.gid,
                   url.url,
                   link_type.name,
                   work.gid,
                   work.name
              FROM l_work_url
        INNER JOIN url
                ON url.id = l_work_url.entity1
        INNER JOIN work
                ON l_work_url.entity1 = work.id
        INNER JOIN link
                ON l_work_url.link = link.id
        INNER JOIN link_type
           ON link.link_type=link_type.id
        WHERE work.gid = :id
        """), {
            "id": id,
        })
        url_relations = result.fetchall()
        # Urls can be arranged then in a dictionary as it is being presently done.
        if url_relations:
            work["url-relations"] = url_relations

    if "tags" in relations:

        ## We can use helpers for things common between entities.
        ## This is similar to what Search Server actually does.
        tags = db_helpers.get_tags(id, 'work', 'work_tag', 'work')
        if tags:
            work["tags"] = tags

    ## Cache results as it is done presently
    cache.set(key=key, val=work, time=DEFAULT_CACHE_EXPIRATION)