Sort query result according to search conditions in CakePHP 2.

How to sort query result according to various search conditions in fetch query of CakePHP 2.

Problem: Priority based sorting of records

Example: Search a string (“Proposalways StudySection”) and display records in following order:

Contain all words (“Proposalways StudySection”) in course_name field.
Contain all words (“Proposalways StudySection”) in tag_name field.
Contain all words (“Proposalways StudySection”) in summary field.

[conditions] => Array(
    [OR] => Array(
            [0] => Array(
                    [0] => lower(course_name) LIKE '%proposalways%'
                    [1] => lower(course_name) LIKE '%studysection%'
                )
            [1] => Array(
                    [0] => lower(tag_name) LIKE '%proposalways%'
                    [1] => lower(tag_name) LIKE '%studysection%'
                )
            [2] => Array(
                    [0] => lower(summary) LIKE '%proposalways%'
                    [1] => lower(summary) LIKE '%studysection%'
                )
        )
)

[order] => CASE WHEN lower(course_name) LIKE ‘%proposalways%’ AND lower(course_name) LIKE ‘%studysection%’ then 1 WHEN lower(tag_name) LIKE ‘%proposalways%’ AND lower(tag_name) LIKE ‘%studysection%’ then 2 ELSE 3 END ASC

Output:

Order	Course Name	Tag Name	Summary
1	Proposalways, StudySection	E-SIGN CONTRACTS, CLOSE DEALS FASTER, Online test.	Webner Solution Company Products.
2	Online certification	Proposalways, StudySection	Test your Knowledge, Earn Certificates
3	Online certification	E-SIGN CONTRACTS	Proposalways, StudySection
4	E-SIGN CONTRACTS	E-SIGN CONTRACTS	Proposalways, StudySection