CODEWITHSUNDEEP
sqlpostgresqlsymfonydoctrine-ormquery-builder
Mohammad Fareed
Mohammad Fareed

Reputation: 1972

Symfony2 Orderby specific field value first

Symfony2 When Case is not working.

Want to Ordering by specific field value first 

$query->select('s.firstName as first_name,s.lastName as last_name,sts.name as status_name')
            ->leftJoin('s.studentStatus', 'sts')
            ->where($query->expr()->orx(
                $query->expr()->like('s.internalId', ':internalId'),
            ))
            ->addSelect('(CASE WHEN s.storeID = :location THEN 1 else 2) AS HIDDEN storeOrder')
            ->setParameter('internalId', "%{$internalId}")
            ->setParameter('location', "{$location}")
            ->orderBy('storeOrder', 'ASC')
            ->setMaxResults((int) $limit);

Want to order by a column specific value first, other values come after that. I have SQL Query but its not working with below Syntax

Upvotes: 1

Views: 712

Answers (2)

Mohammad Fareed
Mohammad Fareed

Reputation: 1972

The CASE construction is vendor specific and not part of Doctrine 2 by default. However you can have a look into the DoctrineExtensions. You can use the IfElse function for your use case.

Upvotes: 0

Veve
Veve

Reputation: 6758

You can add an hidden field storeOrder then order by this field:

$query->select('s.firstName as first_name,s.lastName as last_name,sts.name as status_name')
           ->leftJoin('s.studentStatus', 'sts')
            ->where($query->expr()->orx(
                $query->expr()->like('s.internalId', ':internalId'),
            ))
            ->addSelect('(CASE WHEN s.storeID = :location THEN 1 else 2) AS HIDDEN storeOrder')
            ->setParameter('name', "%{$term}%")
            ->setParameter('internalId', "%{$internalId}")
            ->setParameter('location', "{$location}")
            ->orderBy('storeOrder', 'ASC')
            ->setMaxResults((int) $limit);

Upvotes: 1

Related Questions