def get(self, id=None): db = database.connect() db.row_factory = database.row_factory(['album', 'cover']) if id: album = db.execute("SELECT Album.*, '#', Photo.* FROM albums as Album LEFT JOIN photos as Photo ON Album.cover = Photo.id WHERE Album.id=:id", {'id':id}).fetchone() print(album) item = album['album'] if album['cover']['id']: item['cover'] = photos.preparePhoto(album['cover']) return success(item) albums = db.execute("SELECT Album.*, '#', Photo.* FROM albums as Album LEFT JOIN photos as Photo ON Album.cover = Photo.id").fetchall() db.close() data = [] for album in albums: item = album['album'] if album['cover']['id']: item['cover'] = photos.preparePhoto(album['cover']) data.append(item) data.append({ 'title':'unsorted', 'id':'unsorted', 'cover':None }) return success(data)
def get(self, id=None): db = database.connect() db.row_factory = database.row_factory(['album', 'cover']) if id: album = db.execute( "SELECT Album.*, '#', Photo.* FROM albums as Album LEFT JOIN photos as Photo ON Album.cover = Photo.id WHERE Album.id=:id", { 'id': id }).fetchone() print(album) item = album['album'] if album['cover']['id']: item['cover'] = photos.preparePhoto(album['cover']) return success(item) albums = db.execute( "SELECT Album.*, '#', Photo.* FROM albums as Album LEFT JOIN photos as Photo ON Album.cover = Photo.id" ).fetchall() db.close() data = [] for album in albums: item = album['album'] if album['cover']['id']: item['cover'] = photos.preparePhoto(album['cover']) data.append(item) data.append({'title': 'unsorted', 'id': 'unsorted', 'cover': None}) return success(data)
def smart(self, type): db = database.connect() cur = db.execute( """SELECT * FROM photos WHERE id NOT IN (SELECT albums_photos.photo_id FROM albums_photos)""" ).fetchall() db.close() return success([photos.preparePhoto(dict(a)) for a in cur])
def photos(self, id): db = database.connect() cur = db.execute("""SELECT photos.* FROM albums INNER JOIN albums_photos AS r ON r.album_id = albums.id INNER JOIN photos ON r.photo_id = photos.id WHERE albums.id=:id""", {'id':id}).fetchall() db.close() return success([photos.preparePhoto(dict(a)) for a in cur])
def photos(self, id): db = database.connect() cur = db.execute( """SELECT photos.* FROM albums INNER JOIN albums_photos AS r ON r.album_id = albums.id INNER JOIN photos ON r.photo_id = photos.id WHERE albums.id=:id""", { 'id': id }).fetchall() db.close() return success([photos.preparePhoto(dict(a)) for a in cur])
def smart(self, type): db = database.connect() cur = db.execute("""SELECT * FROM photos WHERE id NOT IN (SELECT albums_photos.photo_id FROM albums_photos)""").fetchall() db.close() return success([photos.preparePhoto(dict(a)) for a in cur])