Running the following query in Symfony2

Question

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!

NHG · Accepted Answer · 2014-02-08 14:47:41Z

1

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();

answered Feb 8, 2014 at 14:47

NHG

5,8876 gold badges36 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

AnchovyLegend Over a year ago

+1, Thank you, I appreciate the help. Is there a particular reason you don't like solutions like this? it seems to be working great! :)

NHG Over a year ago

I don't like to execute raw sql queries. This can cause problems during refactoring. So, please use it carefully.

ozahorulia · Accepted Answer · 2014-02-08 18:36:52Z

1

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()
;

answered Feb 8, 2014 at 18:36

ozahorulia

10k8 gold badges53 silver badges77 bronze badges

Collectives™ on Stack Overflow

Running the following query in Symfony2

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related