<commit_before>/*
 * This program is part of the OpenLMIS logistics management information system platform software.
 * Copyright © 2017 VillageReach
 *
 * This program is free software: you can redistribute it and/or modify it under the terms
 * of the GNU Affero General Public License as published by the Free Software Foundation, either
 * version 3 of the License, or (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY;
 * without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
 * See the GNU Affero General Public License for more details. You should have received a copy of
 * the GNU Affero General Public License along with this program. If not, see
 * http://www.gnu.org/licenses.  For additional information contact <EMAIL>.
 */

package org.openlmis.referencedata.repository;

import static org.openlmis.referencedata.repository.RepositoryConstants.FROM_ORDERABLES_CLAUSE;
import static org.openlmis.referencedata.repository.RepositoryConstants.JOIN_WITH_LATEST_ORDERABLE;
import static org.openlmis.referencedata.repository.RepositoryConstants.ORDER_BY_PAGEABLE;
import static org.openlmis.referencedata.repository.RepositoryConstants.SELECT_ORDERABLE;

import java.util.List;
import java.util.UUID;
import org.openlmis.referencedata.domain.Code;
import org.openlmis.referencedata.domain.Orderable;
import org.openlmis.referencedata.domain.VersionIdentity;
import org.openlmis.referencedata.repository.custom.OrderableRepositoryCustom;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

/**
 * Persistence repository for saving/finding {@link Orderable}.
 */
@SuppressWarnings({"PMD.TooManyMethods"})
public interface OrderableRepository extends
    JpaRepository<Orderable, VersionIdentity>, OrderableRepositoryCustom,
    BaseAuditableRepository<Orderable, VersionIdentity> {

  @Override
  <S extends Orderable> S save(S entity);

  <S extends Orderable> S findByProductCode(Code code);

  // DATAJPA-1130
  @Query(value = "SELECT o.id, o.versionNumber"
      + FROM_ORDERABLES_CLAUSE
      + " WHERE o.id = ?1"
      + " LIMIT 1",
      nativeQuery = true
  )
  boolean existsById(UUID id);

  boolean existsByProductCode(Code code);
  
  @Query(value = "SELECT o.*"
      + FROM_ORDERABLES_CLAUSE
      + JOIN_WITH_LATEST_ORDERABLE
      + " WHERE o.id in :ids"
      + ORDER_BY_PAGEABLE,
      countQuery = "SELECT COUNT(1)"
      + FROM_ORDERABLES_CLAUSE
      + JOIN_WITH_LATEST_ORDERABLE
      + " WHERE o.id in :ids"
      + ORDER_BY_PAGEABLE,
      nativeQuery = true
  )
  Page<Orderable> findAllLatestByIds(@Param("ids") Iterable<UUID> ids, Pageable pageable);

  @Query(value = SELECT_ORDERABLE
      + FROM_ORDERABLES_CLAUSE
      + JOIN_WITH_LATEST_ORDERABLE
      + " JOIN"
      + " (SELECT MAX(lastupdated) AS lastUpdated"
      + " FROM ("
      + SELECT_ORDERABLE
      + FROM_ORDERABLES_CLAUSE
      + " WHERE o.id IN :ids)"
      + " AS passedOrderables"
      + " ) AS maxLastUpdated"
      + " ON o.lastupdated = maxLastUpdated.lastupdated"
      + ORDER_BY_PAGEABLE,
      nativeQuery = true
  )
  List<Orderable> findOrderableWithLatestModifiedDateByIds(@Param("ids") Iterable<UUID> ids,
      Pageable pageable);

  @Query(value = SELECT_ORDERABLE
      + FROM_ORDERABLES_CLAUSE
      + JOIN_WITH_LATEST_ORDERABLE
      + " JOIN"
      + " (SELECT MAX(lastupdated) AS lastUpdated"
      + FROM_ORDERABLES_CLAUSE
      + " ) AS maxLastUpdated"
      + " ON o.lastupdated = maxLastUpdated.lastupdated"
      + ORDER_BY_PAGEABLE,
      nativeQuery = true
  )
  List<Orderable> findOrderableWithLatestModifiedDateOfAllOrderables(Pageable pageable);

  @Query(value = "SELECT o.*"
      + FROM_ORDERABLES_CLAUSE
      + JOIN_WITH_LATEST_ORDERABLE
      + "   JOIN referencedata.orderable_identifiers oi" 
      + "     ON oi.orderableid = o.id AND oi.orderableversionnumber = o.versionnumber"
      + " WHERE oi.key = :key" 
      + "   AND oi.value = :value",
      nativeQuery = true
  )
  List<Orderable> findAllLatestByIdentifier(@Param("key") String key, @Param("value") String value);
  <commit_msg>OLMIS-6476 Fixed query for finding orderable with latest modified date with pagination<commit_after>/*
 * This program is part of the OpenLMIS logistics management information system platform software.
 * Copyright © 2017 VillageReach
 *
 * This program is free software: you can redistribute it and/or modify it under the terms
 * of the GNU Affero General Public License as published by the Free Software Foundation, either
 * version 3 of the License, or (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY;
 * without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
 * See the GNU Affero General Public License for more details. You should have received a copy of
 * the GNU Affero General Public License along with this program. If not, see
 * http://www.gnu.org/licenses.  For additional information contact <EMAIL>.
 */

package org.openlmis.referencedata.repository;

import static org.openlmis.referencedata.repository.RepositoryConstants.FROM_ORDERABLES_CLAUSE;
import static org.openlmis.referencedata.repository.RepositoryConstants.JOIN_WITH_LATEST_ORDERABLE;
import static org.openlmis.referencedata.repository.RepositoryConstants.ORDER_BY_PAGEABLE;
import static org.openlmis.referencedata.repository.RepositoryConstants.SELECT_ORDERABLE;

import java.util.List;
import java.util.UUID;
import org.openlmis.referencedata.domain.Code;
import org.openlmis.referencedata.domain.Orderable;
import org.openlmis.referencedata.domain.VersionIdentity;
import org.openlmis.referencedata.repository.custom.OrderableRepositoryCustom;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

/**
 * Persistence repository for saving/finding {@link Orderable}.
 */
@SuppressWarnings({"PMD.TooManyMethods"})
public interface OrderableRepository extends
    JpaRepository<Orderable, VersionIdentity>, OrderableRepositoryCustom,
    BaseAuditableRepository<Orderable, VersionIdentity> {

  @Override
  <S extends Orderable> S save(S entity);

  <S extends Orderable> S findByProductCode(Code code);

  // DATAJPA-1130
  @Query(value = "SELECT o.id, o.versionNumber"
      + FROM_ORDERABLES_CLAUSE
      + " WHERE o.id = ?1"
      + " LIMIT 1",
      nativeQuery = true
  )
  boolean existsById(UUID id);

  boolean existsByProductCode(Code code);
  
  @Query(value = "SELECT o.*"
      + FROM_ORDERABLES_CLAUSE
      + JOIN_WITH_LATEST_ORDERABLE
      + " WHERE o.id in :ids"
      + ORDER_BY_PAGEABLE,
      countQuery = "SELECT COUNT(1)"
      + FROM_ORDERABLES_CLAUSE
      + JOIN_WITH_LATEST_ORDERABLE
      + " WHERE o.id in :ids"
      + ORDER_BY_PAGEABLE,
      nativeQuery = true
  )
  Page<Orderable> findAllLatestByIds(@Param("ids") Iterable<UUID> ids, Pageable pageable);

  @Query(value = SELECT_ORDERABLE
      + FROM_ORDERABLES_CLAUSE
      + JOIN_WITH_LATEST_ORDERABLE
      + " JOIN"
      + " (SELECT MAX(lastupdated) AS lastUpdated"
      + " FROM ("
      + SELECT_ORDERABLE
      + FROM_ORDERABLES_CLAUSE
      + " WHERE o.id IN :ids)"
      + " AS passedOrderables"
      + " ) AS maxLastUpdated"
      + " ON o.lastupdated = maxLastUpdated.lastupdated"
      + ORDER_BY_PAGEABLE,
      nativeQuery = true
  )
  List<Orderable> findOrderableWithLatestModifiedDateByIds(@Param("ids") Iterable<UUID> ids,
      Pageable pageable);

  @Query(value = SELECT_ORDERABLE
      + FROM_ORDERABLES_CLAUSE
      + " JOIN"
      + " (SELECT MAX(lastupdated) AS lastUpdated"
      + " FROM ("
      + SELECT_ORDERABLE
      + FROM_ORDERABLES_CLAUSE
      + JOIN_WITH_LATEST_ORDERABLE
      + ORDER_BY_PAGEABLE
      + ") AS allOrderables"
      + ") AS maxLastUpdated"
      + " ON o.lastupdated = maxLastUpdated.lastupdated",
      nativeQuery = true
  )
  List<Orderable> findOrderableWithLatestModifiedDateOfAllOrderables(Pageable pageable);

  @Query(value = "SELECT o.*"
      + FROM_ORDERABLES_CLAUSE
      + JOIN_WITH_LATEST_ORDERABLE
      + "   JOIN referencedata.orderable_identifiers oi" 
      + "     ON oi.orderableid = o.id AND oi.orderableversionnumber = o.versionnumber"
      + " WHERE oi.key = :key" 
      + "   AND oi.value = :value",
      nativeQuery = true
  )
  List<Orderable> findAllLatestByIdentifier(@Param("key") String key, @Param("value") String value);
  
{'fraction_non_alphanumeric': 0.08753375337533753, 'fraction_numerical': 0.0031503150315031502, 'mean_word_length': 3.9273835920177382, 'pattern_counts': {'":': 0, '<': 25, '<?xml version=': 0, '>': 25, 'https://': 0, 'lorem ipsum': 0, 'www.': 2, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '4743118', 'n_tokens_mistral': 2723, 'n_tokens_neox': 2658, 'n_words': 653}