Wordpress custom QUERY

Question

I need to do select from Post table, each post is related to one category and on tag. I need to select specific tag/s and/or category/s. Terms table have custom field "menu_order", it is used to set category order. I want to sort posts by that field. If i do plain select:

SELECT * from wp_posts

LEFT
JOIN wp_term_relationships
ON ( wp_term_relationships.object_id = wp_posts.ID )

LEFT
JOIN wp_terms
ON ( wp_terms.term_id = wp_term_relationships.term_taxonomy_id )

WHERE 1=1
AND wp_posts.post_type = 'post'
AND (
wp_posts.post_status = 'publish'
OR wp_posts.post_status = 'private'
)
LIMIT 0, 20

I get result:

+----------------+----------------+----------+---------------------+
|    POST ID     |    Term name   |   order  |        type         |
+----------------+----------------+----------+---------------------+
|       2        |      Fire      |    1     |      category       |
+----------------+----------------+----------+---------------------+
|       2        |      blue      |    0     |         tag         |
+----------------+----------------+----------+---------------------+

That is just the intro.

If I do WHERE $wpdb->terms.term_id = 170 what is a TAG I have only one return.

+----------------+----------------+----------+---------------------+
|    POST ID     |    Term name   |   order  |        type         |
+----------------+----------------+----------+---------------------+
|       2        |      blue      |    0     |         tag         |
+----------------+----------------+----------+---------------------+

I need to select POSTS, filter out by category or/and tag, then get TERM_ORDER from category attached to POST.

So, no matter what results I have, order is always pulled from related category.

As I am not so strong in MYSQL, this gets me baffled. Any input would be helpful :)

UPDATE --------------------------------------------------

So, after crazy coding I managed to get what I wanted. Do not hold your breath as this probably is ugly and slow, but works.

First, I create view where all products are collected.

$qqquery = "
CREATE VIEW samsam AS
SELECT 
ID, post_content, post_title, post_excerpt, post_status, post_name, guid, post_type, $wpdb->term_relationships.term_taxonomy_id as cat_term_taxonomy_id, tt1.term_taxonomy_id as tag_term_taxonomy_id

FROM $wpdb->posts

LEFT                
JOIN $wpdb->term_relationships
ON ( $wpdb->term_relationships.object_id = $wpdb->posts.ID )

LEFT 
JOIN $wpdb->term_relationships AS tt1 
ON ( tt1.object_id = $wpdb->posts.ID ) 

WHERE 1=1 
AND $wpdb->posts.post_type = 'post' 
AND (
$wpdb->posts.post_status = 'publish'
OR $wpdb->posts.post_status = 'private'
) 
AND (                       wp_term_relationships.term_taxonomy_id IN ( $query_cat ) // ID's of categories, seperated by coma
AND tt1.term_taxonomy_id IN (' . $add_query_tag_id . ') //ID's of POSTS seperated by coma
) 
ORDER BY $wpdb->term_relationships.term_taxonomy_id DESC
";
$wpdb->query($qqquery);

After that I do query where I select view and join relationships and terms to get category order by filtering out order > 0, then group BY id to filter out duplicates.

$querystr = "
SELECT * from samsam

LEFT 
JOIN $wpdb->term_relationships 
ON (samsam.ID = $wpdb->term_relationships.object_id) 

LEFT
JOIN $wpdb->terms
ON ($wpdb->terms.term_id = $wpdb->term_relationships.term_taxonomy_id) 
AND ($wpdb->terms.menu_order > 0)

WHERE menu_order > 0

GROUP BY ID

ORDER BY menu_order ASC

LIMIT $item_limit_offset, $item_limit_pp"; // first is calculation of products to skip, second is how many posts per page

$pageposts = $wpdb->get_results($querystr, OBJECT);

What it does:

Category 1 (have order 1) post post post

Category 3 (have order 2) post post post

Category 2 (have order 3) post post post

Can be limited, offseted, paginated, filtered out by tags and/or categories.

I used plugin "Term Menu Order" that adds extra field to order categories.

Have a nice day.

Wordpress custom QUERY

Answers (1)

Related Questions