Sort query result according to search conditions in CakePHP 2.

|
| By Webner

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

Leave a Reply

Your email address will not be published. Required fields are marked *