Changing the order of results: set_order() & find()

Friday, June 11. 2004

Problem: I want to change the order in which my records are returned, in SQL this is 'order by my_field ASC'. I see that the pxdb_collection class has the set_order() method, but when I try this it doesn't seem to work. My code is:

        $Records = &new pxdb_collection(DATATYPE_DOCUMENT);
        $Records->set_order('sunrise DESC');
        echo $Records->get_sql();

This prints the following SQL query to the browser:

SELECT DISTINCT
    r.approved, r.created, r.datatype, r.id, r.modified, r.`name` AS `title`, r.`sunrise`, r.`sunset`,
    ...
FROM
    records r
WHERE
    r.datatype IN (5)
    AND r.approved = 1					
ORDER BY r.name DESC

Why is this not changing the order from the default 'r.name' to 'r.sunrise DESC'? The answer is that you have to run pxdb_collection's internal/default filter before it will add the set_order() filter to the base SQL. Thus, you need to call the pxdb_collection::find() method before the set_order() will take affect on the SQL:

$Records = &new pxdb_collection(DATATYPE_DOCUMENT);
$Records->set_order('sunrise DESC');
$Records->find();
echo $Records->get_sql();

Posted by ncowham in Under the Hood at 11:14 | Comments (0) | Trackbacks (0)

Trackbacks

Trackback specific URI for this entry

No Trackbacks

Comments

Display comments as (Linear | Threaded)

No comments

Add Comment

Name
Email
Homepage
In reply to
Comment	Enclosing asterisks marks text as bold (word), underscore are made via _word_. Standard emoticons like :-) and ;-) are converted to images. To prevent automated Bots from commentspamming, please enter the string you see in the image below in the appropriate input box. Your comment will only be submitted if the strings match. Please ensure that your browser supports and accepts cookies, or your comment cannot be verified correctly. Enter the string from the spam-prevention image above:
	Remember Information? Subscribe to this entry

About SyntaxCMS

SyntaxCMS is an Open Source Content Management System project sponsored by Forum One Communications. Forum One is a firm based in Washington DC with the singular mission of applying significant technologies to public policy issues of importance.