CODEWITHSUNDEEP
symfonydoctrine-ormdoctrine
AnchovyLegend
AnchovyLegend

Reputation: 12537

Running the following query in Symfony2

I am trying to run a query as follows in Symfony:

SELECT * FROM company 
WHERE name LIKE '%$search%'
ORDER BY CASE WHEN name LIKE '$search%'  THEN 1        
              WHEN name LIKE '%$search'  THEN 2      
              WHEN name LIKE '%$search%' THEN 3 
END
LIMIT 0,10

There seems to be a lot of the limitations with createQuery() and findBy() I was wondering if there was a way to query the DB in such a way with Symfony2?

Note, the company table is also set up as an entity.

This is what I tried which doesn't accomplish my whole query:

$this->getDoctrine()
   ->getRepository('testMyBundle:Company') 
   ->findBy(array(),array(),10,($current-1)*$numItemsPerPage);

I appreciate any suggestions, thanks in advance!

Upvotes: 0

Views: 34

Answers (2)

ozahorulia
ozahorulia

Reputation: 10084

This DQL should work:

$query = $this->getDoctrine()
    ->createQuery('
        SELECT c
        FROM testMyBundle:Company c
        WHERE c.name LIKE :search
        ORDER BY CASE WHEN c.name LIKE :search  THEN 1
        WHEN c.name LIKE :search  THEN 2
        WHEN c.name LIKE :search THEN 3
        END
    ')
    ->setParameter('search', '%search%')
    ->setMaxResults(10)
    ->setFirstResult(0)
    ->execute()
;

Upvotes: 1

NHG
NHG

Reputation: 5877

I don't like solutions like that, but should working:

// CompanyRepository.php
public function getSth() {
    $query = 'your sql query here';
    $em = $this->getEntityManager();
    $connection = $em->getConnection();

    $stmt = $connection->prepare($query);
    $stmt->execute();

    return $stmt->fetchAll();
}

And in your controller:

$this->getDoctrine()
   ->getRepository('testMyBundle:Company')->getSth();

Upvotes: 1

Related Questions