package com.rongyichuang.player.repository;
|
|
import com.rongyichuang.player.entity.ActivityPlayer;
|
import org.springframework.data.jpa.repository.JpaRepository;
|
import org.springframework.data.jpa.repository.Query;
|
import org.springframework.data.repository.query.Param;
|
import org.springframework.stereotype.Repository;
|
|
import java.util.List;
|
import java.util.Optional;
|
|
/**
|
* 活动选手Repository接口
|
*/
|
@Repository
|
public interface ActivityPlayerRepository extends JpaRepository<ActivityPlayer, Long> {
|
|
/**
|
* 根据活动ID查找参赛选手
|
*/
|
List<ActivityPlayer> findByActivityId(Long activityId);
|
|
/**
|
* 根据选手ID查找参赛记录
|
*/
|
List<ActivityPlayer> findByPlayerId(Long playerId);
|
|
/**
|
* 根据活动ID和选手ID查找参赛记录
|
*/
|
Optional<ActivityPlayer> findByActivityIdAndPlayerId(Long activityId, Long playerId);
|
|
/**
|
* 根据阶段ID查找参赛选手
|
*/
|
List<ActivityPlayer> findByStageId(Long stageId);
|
|
/**
|
* 根据区域ID查找参赛选手
|
*/
|
List<ActivityPlayer> findByRegionId(Long regionId);
|
|
/**
|
* 根据活动ID和区域ID查找参赛选手
|
*/
|
List<ActivityPlayer> findByActivityIdAndRegionId(Long activityId, Long regionId);
|
|
/**
|
* 根据状态查找参赛选手
|
*/
|
List<ActivityPlayer> findByState(Integer state);
|
|
/**
|
* 查找活动的参赛选手数量
|
*/
|
@Query("SELECT COUNT(ap) FROM ActivityPlayer ap WHERE ap.activityId = :activityId AND ap.state = 1")
|
Long countByActivityId(@Param("activityId") Long activityId);
|
|
/**
|
* 查找区域的参赛选手数量
|
*/
|
@Query("SELECT COUNT(ap) FROM ActivityPlayer ap WHERE ap.regionId = :regionId AND ap.state = 1")
|
Long countByRegionId(@Param("regionId") Long regionId);
|
|
/**
|
* 根据活动ID查找参赛选手，包含选手和区域信息
|
*/
|
@Query("SELECT ap FROM ActivityPlayer ap " +
|
"LEFT JOIN FETCH ap.player p " +
|
"LEFT JOIN FETCH ap.region r " +
|
"WHERE ap.activityId = :activityId AND ap.state = 1 " +
|
"ORDER BY ap.createTime DESC")
|
List<ActivityPlayer> findByActivityIdWithPlayerAndRegion(@Param("activityId") Long activityId);
|
|
/**
|
* 根据区域统计各活动的参赛人数
|
*/
|
@Query("SELECT ap.activityId, ap.regionId, COUNT(ap) " +
|
"FROM ActivityPlayer ap " +
|
"WHERE ap.state = 1 " +
|
"GROUP BY ap.activityId, ap.regionId")
|
List<Object[]> countByActivityAndRegion();
|
|
/**
|
* 查找排名前N的选手
|
*/
|
@Query("SELECT ap FROM ActivityPlayer ap " +
|
"WHERE ap.activityId = :activityId AND ap.state = 1 AND ap.rank IS NOT NULL " +
|
"ORDER BY ap.rank ASC")
|
List<ActivityPlayer> findTopRankedPlayers(@Param("activityId") Long activityId);
|
|
/**
|
* 根据状态统计报名数量
|
*/
|
long countByState(Integer state);
|
}
|
