nl.tno.bim.mapping.repositories.MappingSetRepository Maven / Gradle / Ivy

Go to download
package nl.tno.bim.mapping.repositories;

import java.util.List;

import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.data.repository.query.Param;

import nl.tno.bim.mapping.domain.MappingSet;

public interface MappingSetRepository extends CrudRepository {

    List findByProjectId(String projectId);

    @Query("Select ms,msm From MappingSet ms INNER JOIN ms.mappingSetMaps msm where  ms.id = :id group by ms,msm HAVING msm.mappingRevisionId = max(msm.mappingRevisionId)")
    List findMappingSetWithLatestRevisionById(@Param("id") Long id);

    /**
     * query to return the mappingset with only the latest mappingsetmap (and mapping references) 
     * @param projectId
     * @return list of objects arrays with 
     * [0] mapping_set id
     * [1] mapping_set date
     * [2] mapping_Set project_id
     * [3] mapping_set_map id
     * [4] mapping_set_map guid
     * [5] mapping_set_map max revision id for the guid
     * [6] mapping_set_map mapping_id
     */
    @Query(nativeQuery = true, value = 
    		"SELECT a.id as aid, a.date as adate, a.project_id as aprojectid, \r\n" + 
    		" c.id as cid, c.element_guid as celementguid, c.mapping_revision_id as cmappingrevisionid, \r\n" + 
    		" c.mapping_id as cmappingid, c.mapping_set_id as cmappingsetid \r\n" + 
    		" FROM mapping_set a \r\n" + 
    		" INNER JOIN mapping_set_map c ON a.id = c.mapping_set_id \r\n" + 
    		" INNER JOIN ( \r\n" + 
    		"     SELECT mapping_set_id, MAX(mapping_revision_id) max_mapping_revision_id  \r\n" + 
    		"     FROM mapping_set_map \r\n" + 
    		"     GROUP BY element_guid, mapping_set_id \r\n" + 
    		"     ORDER BY max_mapping_revision_id desc ) b \r\n" + 
    		" ON a.id = b.mapping_set_id AND c.mapping_revision_id = b.max_mapping_revision_id \r\n" + 
    		" WHERE a.project_id=:projectId"
			)
    List findMappingSetWithLatestRevisionByProjectId(@Param("projectId") String projectId);
}