Custom query to order by multiple taxonomies

Question

I got this website from a customer to fix some bugs. They are a car dealership.

The previous developer created a custom post type (vehicle) and there are two taxonomies: make and model. (they are not custom fields, they are taxonomies)

If I do something like this, I can easily order by the vehicle maker

SELECT distinct(wp_posts.id), wp_posts.post_title, wp_terms.name 
FROM wp_posts, wp_postmeta, wp_terms 
where wp_posts.id = wp_postmeta.post_id 
and wp_postmeta.meta_value = wp_terms.term_id 
and wp_postmeta.meta_key = "make" 
and wp_posts.post_status = "publish" 
and wp_posts.post_type = "vehicle" 
order by wp_posts.id, wp_terms.name;

But how can I order by both maker AND model at the same time?

Answer 1

Well, in case anyone is searching and trying to find a way to do what I did here, I figured it out myself.

My task was: I have a custom post type called vehicle and there are two taxonomies, the vehicle maker and the vehicle model. I needed to show all the posts alphabetically ordered first by maker, then by model, so on the list it makes sense, exempla: Audi A1, Audi A4, BMW 120, etc.

It is easy to order by one taxonomy only, but if I just order by maker, my posts would show like Audi A4, Audi A1, Audi A3.

I solved this by doing this.

First, I'll do a regular get_results so I can get an alphabetical order of all my makers:

 $makers_list = $wpdb->get_results("SELECT 
GROUP_CONCAT(pm.post_id ) as id_carro, tm.name as maker
    FROM wpav_postmeta pm
    INNER JOIN wpav_terms tm ON tm.term_id = pm.meta_value
    WHERE pm.meta_key='make'
    GROUP BY tm.name
    ORDER BY tm.name
    ");

The "GROUP_CONTACT" function of mysql will return me a list of post IDs, something like 19,49,59,83,58,395,394 etc

Then, I do a foreach on each maker and get an order list of vehicles for that particular maker, order by the model taxonomy.

foreach ( $makers_list as $maker ) {

$vehicle_list = $wpdb->get_results("SELECT tm.name as model , pm.post_id
FROM wpav_postmeta pm
INNER JOIN wpav_terms tm ON tm.term_id = pm.meta_value
WHERE pm.post_id IN (" . $fabr->vehicle_id . ") and pm.meta_key='model'
ORDER BY model
");

Then it is just a matter of doing another foreach for each of those cars

foreach ( $vehicle_list as $vehicle ) {

$status_post = get_post_status($vehicle->post_id);
$type_post = get_post_type($vehicle->post_id);

if ( $status_post == 'publish' && $type_post == 'vehicle') {
$post = get_post($vehicle->post_id);

As you can see I am checking if the post is published and it is the correct post type, then I set up the post variable and it's done, normal wordpress loop after that.

Hope I could be of any help.