# repositories/search_repo.py
from typing import List, Dict, Any, Optional
from datetime import datetime, timedelta
from sqlalchemy import delete, select, func, or_, and_, desc
from sqlalchemy.ext.asyncio import AsyncSession
from models import SearchRecord, Share, User, Room, RoomMember, utcnow
class SearchRepository:
def __init__(self, db: AsyncSession):
self.db = db
async def add_search_record(self, keyword: str, user_id: int, search_time: datetime) -> None:
record = SearchRecord(keyword=keyword.strip(), user_id=user_id, search_time=search_time)
self.db.add(record)
await self.db.flush()
async def get_hot_keywords(self, start_time: datetime, limit: int = 10) -> List[Dict]:
stmt = (
select(
SearchRecord.keyword,
func.count(SearchRecord.id).label("search_count")
)
.where(SearchRecord.search_time >= start_time)
.group_by(SearchRecord.keyword)
.order_by(desc("search_count"))
.limit(limit)
)
result = await self.db.execute(stmt)
return [dict(row) for row in result.mappings().all()]
async def get_user_history(self, user_id: int, days: int = 30, limit: int = 50) -> List[Dict]:
cutoff = utcnow() - timedelta(days=days)
stmt = (
select(SearchRecord.id, SearchRecord.keyword, SearchRecord.search_time)
.where(
SearchRecord.user_id == user_id,
SearchRecord.search_time >= cutoff
)
.order_by(desc(SearchRecord.search_time))
.limit(limit)
)
result = await self.db.execute(stmt)
return [dict(row) for row in result.mappings().all()]
async def get_history_by_id(self, history_id: int) -> Optional[Dict]:
stmt = select(SearchRecord.id, SearchRecord.user_id, SearchRecord.keyword).where(
SearchRecord.id == history_id
)
result = await self.db.execute(stmt)
row = result.mappings().fetchone()
return dict(row) if row else None
async def delete_history(self, history_id: int) -> bool:
stmt = delete(SearchRecord).where(SearchRecord.id == history_id)
result = await self.db.execute(stmt)
return result.rowcount > 0
async def clear_user_history(self, user_id: int) -> None:
stmt = delete(SearchRecord).where(SearchRecord.user_id == user_id)
await self.db.execute(stmt)
async def search_shares(self, keyword: str, is_public: bool, author_id: int, skip: int = 0, limit: int = 10) -> tuple[int, List[Dict]]:
from models import Character
stmt = (
select(
Share.id, Share.title, Share.type,
Share.like_count, Share.view_count, Share.comment_count,
Share.created_at,
User.account.label("author_account"),
Character.name.label("ai_char_name")
)
.join(User, Share.author_id == User.id)
.outerjoin(Character, Share.ai_character_id == Character.id)
.where(or_(
Share.title.ilike(f"%{keyword}%"),
Share.content.ilike(f"%{keyword}%")
))
.where(or_(
Share.is_public == True,
Share.author_id == author_id
))
.order_by(Share.created_at.desc())
.offset(skip)
.limit(limit)
)
count_stmt = select(func.count(Share.id)).where(or_(
Share.title.ilike(f"%{keyword}%"),
Share.content.ilike(f"%{keyword}%")
)).where(or_(
Share.is_public == True,
Share.author_id == author_id
))
total = (await self.db.execute(count_stmt)).scalar()
result = await self.db.execute(stmt)
shares = [dict(row) for row in result.mappings().all()]
return total, shares
async def recommend_shares_by_keywords(self, keywords: List[str], limit: int, exclude_user_id: int, department_id: int) -> List[Dict]:
from models import Character
if not keywords:
return []
like_clauses = or_(*[Share.title.ilike(f"%{k}%") | Share.content.ilike(f"%{k}%") for k in keywords])
stmt = (
select(
Share.id, Share.title, Share.type,
Share.like_count, Share.view_count, Share.comment_count,
Share.created_at,
User.account.label("author_account"),
Character.name.label("ai_char_name")
)
.join(User, Share.author_id == User.id)
.outerjoin(Character, Share.ai_character_id == Character.id)
.where(like_clauses)
.where(Share.author_id != exclude_user_id)
.order_by(Share.like_count.desc(), Share.created_at.desc())
.limit(limit * 2) # 多取一些用于去重
)
result = await self.db.execute(stmt)
return [dict(row) for row in result.mappings().all()]
async def get_dept_hot_shares(self, dept_id: int, limit: int, exclude_user_id: int) -> List[Dict]:
from models import Character
stmt = (
select(
Share.id, Share.title, Share.type,
Share.like_count, Share.view_count, Share.comment_count,
Share.created_at,
User.account.label("author_account"),
Character.name.label("ai_char_name")
)
.join(User, Share.author_id == User.id)
.outerjoin(Character, Share.ai_character_id == Character.id)
.where(User.department_id == dept_id)
.where(Share.author_id != exclude_user_id)
.order_by(Share.view_count.desc(), Share.like_count.desc())
.limit(limit * 2)
)
result = await self.db.execute(stmt)
return [dict(row) for row in result.mappings().all()]
async def get_global_hot_shares(self, limit: int, exclude_user_id: int) -> List[Dict]:
from models import Character
stmt = (
select(
Share.id, Share.title, Share.type,
Share.like_count, Share.view_count, Share.comment_count,
Share.created_at,
User.account.label("author_account"),
Character.name.label("ai_char_name")
)
.join(User, Share.author_id == User.id)
.outerjoin(Character, Share.ai_character_id == Character.id)
.where(Share.author_id != exclude_user_id)
.order_by(Share.view_count.desc(), Share.like_count.desc())
.limit(limit * 2)
)
result = await self.db.execute(stmt)
return [dict(row) for row in result.mappings().all()]
async def search_users(self, keyword: str, current_user_id: int, is_admin: bool, skip: int = 0, limit: int = 10) -> tuple[int, List[Dict]]:
stmt = (
select(
User.id, User.account, User.role,
User.created_at,
func.coalesce(func.max(RoomMember.room_id), 0).label("in_same_room") # 是否同聊过天
)
.outerjoin(RoomMember, User.id == RoomMember.user_id)
.where(or_(
User.account.ilike(f"%{keyword}%")
))
.group_by(User.id)
.order_by(User.created_at.desc())
.offset(skip)
.limit(limit)
)
count_stmt = select(func.count(User.id)).where(User.account.ilike(f"%{keyword}%"))
total = (await self.db.execute(count_stmt)).scalar()
result = await self.db.execute(stmt)
users = []
for row in result.mappings():
user_data = {
"id": row.id,
"account": row.account,
"role": row.role,
"created_at": row.created_at,
"can_view_profile": is_admin or row.id == current_user_id or row.in_same_room > 0
}
users.append(user_data)
return total, users
search_repo的许多功能都没有用到，设计一下相关的接口
本文介绍如何在Android应用中实现分享图片功能及创建账户管理服务。通过定义特定的Intent Filter，应用程序能响应系统的分享请求并展示指定内容。此外，还介绍了如何配置账户管理服务，实现账户的添加与管理。
扫一扫
612
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
