CODEWITHSUNDEEP
phpmysqlpropel
Liam Sorsby
Liam Sorsby

Reputation: 2982

Propel using a single query to get a result and change the order

I've got a simple Propel query below which is to filter based on the country code (ordered alphabetically). However, I need to be able to do a filter to make a certain countrycode view at the top. Is this possibly using a single Propel query?

My Query (at present):

$query = CountryQuery::create()
    ->filterByCountrycode($countryCode)
    ->find();

Now that would only find the single item as my results only have one result per countrycode (no duplicates). However, I would have thought that the next step would to do another query and find all of the results and merge them together, however doing two queries seems counter-productive. Is there a better way of doing this?

Example:

If the country code is GB then the query above will output United Kingdom. However, the desired results will give the list of Countrys in alphabetic order, however the first item would be United Kingdom.

Upvotes: 1

Views: 1045

Answers (1)

Alexander Guz
Alexander Guz

Reputation: 1364

As @halfer suggested, you can add a virtual column and order by it. Here is the query:

$countryCode = 'ru';
$countries = \CountryQuery::create()
    ->addAsColumn('order_field', "CASE WHEN code = '$countryCode' THEN 1 ELSE 0 END")
    ->select(array('Id', 'Name', 'Code'))
    ->orderBy('order_field', \Criteria::DESC)
    ->orderById(\Criteria::ASC)
    ->find();

Upvotes: 2

Related Questions