<commit_before><?php

namespace HealthCareAbroad\TermBundle\Repository;

use Doctrine\ORM\Query;

use HealthCareAbroad\TreatmentBundle\Entity\SubSpecialization;

use HealthCareAbroad\HelperBundle\Entity\City;

use HealthCareAbroad\HelperBundle\Entity\Country;

use HealthCareAbroad\TermBundle\Entity\SearchTerm;

use HealthCareAbroad\TreatmentBundle\Entity\Specialization;

use HealthCareAbroad\PagerBundle\Pager;

use HealthCareAbroad\TermBundle\Entity\TermDocument;

use HealthCareAbroad\TreatmentBundle\Entity\Treatment;

use Doctrine\ORM\EntityRepository;

class SearchTermRepository extends EntityRepository
{

    public function findAllActiveTermsGroupedBySpecialization()
    {
        $parameters = array('treatmentType' => TermDocument::TYPE_TREATMENT, 'activeSearchTermStatus' => SearchTerm::STATUS_ACTIVE);
        // get the treatment_ids that are available in search terms
        $sql = "SELECT a.documentId FROM TermBundle:SearchTerm a
        WHERE a.type = :treatmentType AND a.status = :activeSearchTermStatus
        GROUP BY a.documentId, a.type";

        $query = $this->getEntityManager()->createQuery($sql)
            ->setParameters($parameters);

        // find a way to flatten this result without looping
        $result = $query->getArrayResult();
        $treatmentIds = array();
        foreach ($result as $row) {
            $treatmentIds[] = $row['documentId'];
        }

        $qb = $this->getEntityManager()->createQueryBuilder();
        $qb->select('sp, tr, sub_sp')
            ->from('TreatmentBundle:Specialization', 'sp')
            ->innerJoin('sp.treatments', 'tr')
            ->leftJoin('tr.subSpecializations', 'sub_sp')
            ->where($qb->expr()->in('tr.id', ':treatmentIds'))
            ->orderBy('sp.name, tr.name')
            ->setParameter('treatmentIds', $treatmentIds);

        return $qb->getQuery()->getResult();
    }

    public function findActiveCountriesWithCities()
    {
        $status = SearchTerm::STATUS_ACTIVE;

        $connection = $this->getEntityManager()->getConnection();<commit_msg>Fixes issue #677.<commit_after><?php

namespace HealthCareAbroad\TermBundle\Repository;

use Doctrine\ORM\Query;

use HealthCareAbroad\TreatmentBundle\Entity\SubSpecialization;

use HealthCareAbroad\HelperBundle\Entity\City;

use HealthCareAbroad\HelperBundle\Entity\Country;

use HealthCareAbroad\TermBundle\Entity\SearchTerm;

use HealthCareAbroad\TreatmentBundle\Entity\Specialization;

use HealthCareAbroad\PagerBundle\Pager;

use HealthCareAbroad\TermBundle\Entity\TermDocument;

use HealthCareAbroad\TreatmentBundle\Entity\Treatment;

use Doctrine\ORM\EntityRepository;

class SearchTermRepository extends EntityRepository
{

    public function findAllActiveTermsGroupedBySpecialization()
    {
        $parameters = array('treatmentType' => TermDocument::TYPE_TREATMENT, 'activeSearchTermStatus' => SearchTerm::STATUS_ACTIVE);
        // get the treatment_ids that are available in search terms
        $sql = "SELECT a.documentId FROM TermBundle:SearchTerm a
        WHERE a.type = :treatmentType AND a.status = :activeSearchTermStatus
        GROUP BY a.documentId, a.type";

        $query = $this->getEntityManager()->createQuery($sql)
            ->setParameters($parameters);

        // find a way to flatten this result without looping
        $result = $query->getArrayResult();
        $treatmentIds = array();
        foreach ($result as $row) {
            $treatmentIds[] = $row['documentId'];
        }

        $qb = $this->getEntityManager()->createQueryBuilder();
        $qb->select('sp, tr, sub_sp')
            ->from('TreatmentBundle:Specialization', 'sp')
            ->innerJoin('sp.treatments', 'tr')
            ->leftJoin('tr.subSpecializations', 'sub_sp')
            ->where($qb->expr()->in('tr.id', ':treatmentIds'))
            ->andWhere('sp.status = :specializationStatus')
            ->andWhere('tr.status = :treatmentStatus')
            ->andWhere('sub_sp.status = :subSpecializationStatus')

            ->orderBy('sp.name, tr.name')
            ->setParameter('treatmentIds', $treatmentIds)
            ->setParameter('specializationStatus', Specialization::STATUS_ACTIVE)
            ->setParameter('treatmentStatus', Treatment::STATUS_ACTIVE)
            ->setParameter('subSpecializationStatus', SubSpecialization::STATUS_ACTIVE);

        return $qb->getQuery()->getResult();
    }

    public function findActiveCountriesWithCities()
    {
        $status = SearchTerm::STATUS_ACTIVE;

        $connection = $this->getEntityManager()->getConnection();
{'fraction_non_alphanumeric': 0.12327868852459016, 'fraction_numerical': 0.0006557377049180328, 'mean_word_length': 3.544190665342602, 'pattern_counts': {'":': 0, '<': 5, '<?xml version=': 0, '>': 51, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, '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': '7250231', 'n_tokens_mistral': 1329, 'n_tokens_neox': 1241, 'n_words': 229}