raex_back/src/main/java/com/izouma/nineth/repo/UserAssetSummaryRepo.java

package com.izouma.nineth.repo;

import com.izouma.nineth.domain.UserAssetSummary;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;

import javax.transaction.Transactional;
import java.util.List;

public interface UserAssetSummaryRepo extends JpaRepository<UserAssetSummary, Long>, JpaSpecificationExecutor<UserAssetSummary> {

    List<UserAssetSummary> findByUserId(Long userId);

    @Transactional
    @Modifying
    void deleteByUserId(Long userId);
    @Query(value = "SELECT " +
            "  a.created_at created_at, " +
            "  a.id id, " +
            "  a.id asset_id, " +
            "  a.user_id, " +
            "  a.prefix_name, " +
            "  a.pic, " +
            "  a.minter, " +
            "  a.opened, " +
            "  a.type, " +
            "  a.status, " +
            "  a.number, " +
            "  a.name, " +
            "  a.public_show, " +
            "  a.consignment, " +
            "  IF(b.auctioning_num IS NULL, 0, b.auctioning_num) auctioning_num,  " +
            "  IF(c.consignment_num IS NULL, 0, c.consignment_num) consignment_num,  " +
            "  IF(d.open_show_num IS NULL, 0, d.open_show_num) open_show_num,  " +
            "  IF(e.close_show_num IS NULL, 0, e.close_show_num) close_show_num,  " +
            "  IF(a.num IS NULL, 0, a.num) num  " +
            "FROM " +
            "  ( " +
            "  SELECT " +
            "    id, " +
            "    user_id, " +
            "    prefix_name, " +
            "    pic, " +
            "    minter, " +
            "    opened, " +
            "    type, " +
            "    status, " +
            "    number, " +
            "    name, " +
            "    public_show, " +
            "    consignment, " +
            "    count(*) num,  " +
            "    max(created_at) created_at  " +
            "  FROM " +
            "    asset  " +
            "  WHERE " +
            "    user_id = ?1  " +
            "    AND STATUS IN ( 'NORMAL', 'TRADING', 'GIFTING', 'MINTING', 'AUCTIONING' )  " +
            "    AND type IN ( 'BLIND_BOX', 'DEFAULT' )  " +
            "    AND id NOT IN ( SELECT id FROM asset WHERE type = 'BLIND_BOX' AND opened = 0 )  " +
            "  GROUP BY " +
            "    prefix_name  " +
            "  ) a " +
            "  LEFT JOIN ( " +
            "  SELECT " +
            "    user_id userId, " +
            "    prefix_name prefixName, " +
            "    count(*) auctioning_num  " +
            "  FROM " +
            "    asset  " +
            "  WHERE " +
            "    user_id = ?1  " +
            "    AND STATUS IN ( 'AUCTIONING' )  " +
            "    AND type IN ( 'BLIND_BOX', 'DEFAULT' )  " +
            "    AND id NOT IN ( SELECT id FROM asset WHERE type = 'BLIND_BOX' AND opened = 0 )  " +
            "  GROUP BY " +
            "    prefix_name  " +
            "  ) b ON a.user_id = b.userId  " +
            "  AND a.prefix_name = b.prefixName " +
            "  LEFT JOIN ( " +
            "  SELECT " +
            "    user_id userId, " +
            "    prefix_name prefixName, " +
            "    count(*) consignment_num  " +
            "  FROM " +
            "    asset  " +
            "  WHERE " +
            "    user_id = ?1  " +
            "    AND STATUS IN ( 'NORMAL', 'TRADING', 'GIFTING', 'MINTING', 'AUCTIONING' )  " +
            "    AND type IN ( 'BLIND_BOX', 'DEFAULT' )  " +
            "    AND consignment = 1  " +
            "    AND id NOT IN ( SELECT id FROM asset WHERE type = 'BLIND_BOX' AND opened = 0 )  " +
            "  GROUP BY " +
            "    prefix_name  " +
            "  ) c ON a.user_id = c.userId  " +
            "  AND a.prefix_name = c.prefixName " +
            "  LEFT JOIN ( " +
            "  SELECT " +
            "    user_id userId, " +
            "    prefix_name prefixName, " +
            "    count(*) open_show_num  " +
            "  FROM " +
            "    asset  " +
            "  WHERE " +
            "    user_id = ?1  " +
            "    AND STATUS IN ( 'NORMAL', 'TRADING', 'GIFTING', 'MINTING', 'AUCTIONING' )  " +
            "    AND type IN ( 'BLIND_BOX', 'DEFAULT' )  " +
            "    AND consignment = 0  " +
            "    AND public_show = 1  " +
            "    AND id NOT IN ( SELECT id FROM asset WHERE type = 'BLIND_BOX' AND opened = 0 )  " +
            "  GROUP BY " +
            "    prefix_name  " +
            "  ) d ON a.user_id = d.userId  " +
            "  AND a.prefix_name = d.prefixName " +
            "  LEFT JOIN ( " +
            "  SELECT " +
            "    user_id userId, " +
            "    prefix_name prefixName, " +
            "    count(*) close_show_num  " +
            "  FROM " +
            "    asset  " +
            "  WHERE " +
            "    user_id = ?1  " +
            "    AND STATUS IN ( 'NORMAL', 'TRADING', 'GIFTING', 'MINTING', 'AUCTIONING' )  " +
            "    AND type IN ( 'BLIND_BOX', 'DEFAULT' )  " +
            "    AND consignment = 0  " +
            "    AND public_show = 0  " +
            "    AND id NOT IN ( SELECT id FROM asset WHERE type = 'BLIND_BOX' AND opened = 0 )  " +
            "  GROUP BY " +
            "    prefix_name  " +
            "  ) e ON a.user_id = e.userId  " +
            "  AND a.prefix_name = e.prefixName", nativeQuery = true)
    List<UserAssetSummary> find(Long userId);
}