Ordering of subquery flowing to main query? [SOLVED]

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
matthijs
DevNet Master
Posts: 3360
Joined: Thu Oct 06, 2005 3:57 pm

Ordering of subquery flowing to main query? [SOLVED]

Post 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..
Last edited by matthijs on Thu Jan 31, 2008 1:46 pm, edited 1 time in total.
matthijs
DevNet Master
Posts: 3360
Joined: Thu Oct 06, 2005 3:57 pm

Re: Ordering of subquery flowing to main query?

Post 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...
Post Reply