async def get(user_id: int) -> List[NoteInDB]:
#TODO the pagination should be tested. We need to investigate the nested queries
note_items = []
notes_in_db = await db.fetch_all(
notes.select().where(notes.c.user_id==user_id)
)
for note_in_db in notes_in_db:
note_items.append({**note_in_db,
"tags": await NoteTagTableHandler.get_note_tags(note_in_db.get('id'))
})
return paginate(note_items)
async def get_user_notes_by_tag(user_id: int, tag: dict) -> List[NoteInDB]:
note_rows = await db.fetch_all(
notes.select().where(notes.c.user_id == user_id)
)
note_ids = [note_row.get('id') for note_row in note_rows]
tagged_note_ids = await db.fetch_all(
note_tag.select().where(note_tag.c.tag_id == tag.get('id')).where(note_tag.c.note_id.in_(note_ids))
)
return [
await NotesTableHandler.get_note_by_id(note_id.get('note_id')) for note_id in tagged_note_ids
]
async def update(note: NoteUpdate, user_id: int):
existing = await db.fetch_one(
notes.select().where(notes.c.id == note.id).where(notes.c.user_id == user_id)
)
if existing:
note_data = {k:v for k, v in note.data.dict().items() if v is not None and k != 'tags'}
await db.execute(
notes.update().values(**note_data).where(notes.c.id == note.id).where(notes.c.user_id == user_id)
)
if note.data.tags is not None and len(note.data.tags) > 0:
await NoteTagTableHandler.clean_up_tags(note.id)
for tag in note.data.tags:
tag_record = await TagsTableHandler.add_or_get_tag(tag)
await NoteTagTableHandler.add_tag_for_note(note.id, tag_record.get('id'))
note_in_db = await db.fetch_one(
notes.select().where(notes.c.id == note.id).where(notes.c.user_id == user_id)
)
tags = await NoteTagTableHandler.get_note_tags(note.id)
return {**note_in_db, "tags": tags}
else:
return None
async def search(keyword: str, user_id: int):
note_items = []
notes_in_db = await db.fetch_all(
notes.select().where(notes.c.user_id == user_id).where(
or_(
notes.c.title.ilike(f"%{keyword}%"),
notes.c.body.ilike(f"%{keyword}%")
)
)
)
if notes_in_db:
for note_in_db in notes_in_db:
note_items.append({**note_in_db,
"tags": await NoteTagTableHandler.get_note_tags(note_in_db.get('id'))
})
like_tags = await TagsTableHandler.search(keyword)
if len(like_tags) > 0:
for tag in like_tags:
user_notes = await NoteTagTableHandler.get_user_notes_by_tag(user_id, tag)
for user_note in user_notes:
note_items.append({**user_note,
"tags": await NoteTagTableHandler.get_note_tags(user_note.get('id'))
})
return paginate(note_items)
async def get_note_by_id(note_id: int) -> NoteInDB:
return await db.fetch_one(
notes.select().where(notes.c.id == note_id)
)