backend/com.tecknobit.pandoro.services.projects.repositories/ProjectsRepository/getCompleteProjectsList

get Complete Projects List

@Query(value = "SELECT * FROM " + PROJECTS_KEY + " WHERE " + AUTHOR_KEY + "=:" + AUTHOR_KEY + " AND " + NAME_KEY + " LIKE %:" + NAME_KEY + "%" + " AND COALESCE(:" + FILTERS_KEY + ") IS NULL" + " OR " + PROJECT_VERSION_KEY + " IN (:" + FILTERS_KEY + ")" + " UNION SELECT " + PROJECTS_KEY + ".* FROM " + PROJECTS_KEY + " AS " + PROJECTS_KEY + " LEFT JOIN " + PROJECTS_GROUPS_TABLE + " ON " + PROJECTS_KEY + "." + IDENTIFIER_KEY + " = " + PROJECTS_GROUPS_TABLE + "." + PROJECT_IDENTIFIER_KEY + " LEFT JOIN " + GROUPS_KEY + " ON " + PROJECTS_GROUPS_TABLE + "." + GROUP_IDENTIFIER_KEY + " = " + GROUPS_KEY + "." + IDENTIFIER_KEY + " LEFT JOIN " + GROUP_MEMBERS_TABLE + " ON " + GROUPS_KEY + "." + IDENTIFIER_KEY + " = " + GROUP_MEMBERS_TABLE + "." + GROUP_MEMBER_KEY + " WHERE " + GROUP_MEMBERS_TABLE + "." + IDENTIFIER_KEY + " =:" + AUTHOR_KEY + " AND " + GROUP_MEMBERS_TABLE + "." + INVITATION_STATUS_KEY + " = 'JOINED' AND " + GROUPS_KEY + "." + AUTHOR_KEY + " !=:" + AUTHOR_KEY + " AND " + PROJECTS_KEY + "." + NAME_KEY + " LIKE %:" + NAME_KEY + "%" + " AND COALESCE(:" + FILTERS_KEY + ") IS NULL" + " OR " + PROJECTS_KEY + "." + PROJECT_VERSION_KEY + " IN (:" + FILTERS_KEY + ")" + " ORDER BY " + CREATION_DATE_KEY + " DESC ", nativeQuery = true )

abstract fun getCompleteProjectsList(@Param(value = "author") userId: String, @Param(value = "name") name: String, @Param(value = "filters") versions: Set<String>): List<Project>

Method to execute the query to select the list of a Project

Return

the list of projects as List of Project

Parameters

userId

The user identifier

name

The project name to use as filter

versions

The version to use as filters