Page 1 of 1

Ordering of subquery flowing to main query? [SOLVED]

Posted: Thu Jan 31, 2008 11:11 am
by matthijs
Ok, I have this query using a subquery. The problem is, I want to sort the results by something in the subquery. Something which isn't used in the result of the main query.

Code: Select all

 
        $mainquery = "SELECT $wpdb->posts.*, $wpdb->postmeta.*
                        FROM  $wpdb->posts
                        INNER JOIN $wpdb->postmeta 
                        ON $wpdb->posts.ID = $wpdb->postmeta.post_id
                        
                        WHERE $wpdb->postmeta.meta_key='Excerpt'
                        AND $wpdb->posts.post_parent = '24'
                        AND $wpdb->posts.ID IN (
                            SELECT post_id 
                            FROM $wpdb->postmeta 
                            WHERE $wpdb->postmeta.meta_value LIKE '". $safesearchletter ."%' 
                            AND $wpdb->postmeta.meta_key='Surname'
                            ORDER BY $wpdb->postmeta.meta_value ASC
                            )
                        ";  
 
You can see that in the last line I have the ORDER BY clause. Running the subquery on it's own gives me the correct results. But since Surname is not used in the results in the mainquery, the end results are ordered on something else (the posts.ID) in this case.

To give a bit of context: there are 2 tables. posts and posts_meta. Each post has a meta_key 'Excerpt' and a meta_key 'Surname'. So what the purpose is of this query is first search the posts_meta for a Surname with a certain letter. This subquery returns a list of postID's. Then the main query returns some data from the posts and the meta data Excerpts belonging to those posts.

Is it somehow possible to use the order of the IN clause to get the same order in the end result?

Or can I rewrite the whole query in such a way that I get the same effect?

I also tried:

Code: Select all

 
        $mainquery = "SELECT DISTINCT $wpdb->posts.ID, $wpdb->postmeta.*
                        FROM  $wpdb->posts
                        INNER JOIN $wpdb->postmeta 
                        ON $wpdb->posts.ID = $wpdb->postmeta.post_id
                        
                        WHERE $wpdb->posts.post_parent = '24'
                        AND $wpdb->posts.ID IN (
                            SELECT post_id 
                            FROM $wpdb->postmeta 
                            WHERE $wpdb->postmeta.meta_value LIKE '". $safesearchletter ."%' 
                            AND $wpdb->postmeta.meta_key='Surname'
                            ORDER BY $wpdb->postmeta.meta_value ASC
                            )
                        ";  
 
Now I get many duplicate results. I should get unique posts.ID. In this case the DISTINCT doesn't work..

Re: Ordering of subquery flowing to main query?

Posted: Thu Jan 31, 2008 1:46 pm
by matthijs
Well, if nobody can help me I'm the only one left to do that :)

Code: Select all

 
        $mainquery = "SELECT $wpdb->posts.* , $wpdb->postmeta.*
                        FROM $wpdb->posts   
                        JOIN $wpdb->postmeta        
                        ON   $wpdb->posts.ID = $wpdb->postmeta.post_id
                        WHERE $wpdb->posts.post_parent = '24'
                        AND $wpdb->postmeta.meta_key = 'Surname'
                        AND $wpdb->posts.ID IN (
                            SELECT post_id
                            FROM $wpdb->postmeta 
                            WHERE $wpdb->postmeta.meta_value LIKE '". $safesearchletter ."%' 
                            AND $wpdb->postmeta.meta_key='Surname'
                            )
                        GROUP BY $wpdb->posts.ID
                        ORDER BY $wpdb->postmeta.meta_value ASC
                        ";  
 
Can you imagine this is like the 5th query I have ever written 8O My brain hurts...