It is necessary to select records according to several criteria, one of which is the belonging of the record to several headings, one of which is the parent 9 and the other children 10,11.

There is such a request:

$query_string1 = "select p.* from $wpdb->posts as p, $wpdb->postmeta as m1, $wpdb->term_relationships as m2, $wpdb->postmeta as m3 WHERE p.ID = m2.object_id and p.ID = m1.post_id and p.ID = m3.post_id and p.post_type='place' and m2.term_taxonomy_id in ('9,10,11') and m3.meta_value='38' and m3.meta_key='school' and (m1.meta_value LIKE \"%,$multi_city_id,%\") and m1.meta_key = 'post_city_id' and p.post_status='publish' order by p.post_title ASC";

The problem is that the query only works when the entry belongs to category 9, alone, or in combination with any other rubric. But if the record belongs exclusively to the child categories, the query does not select anything. What could be the reason?

  • m2 you have no connection with the posts. show its structure. And format the code. Unreadable. Suspicious quotes '9,10,11' - Yura Ivanov
  • Without quotes does not work at all. And why did you decide that m2 has nothing to do with posts when this is a table of headings? - zink
  • M2 is fine, yes. difficult to take a request in one line. by rubrics: the query does not divide rubrics into "parental" and "affiliated", see the m2 table, posts are selected for which the rubrics have 9.10 or 11. by quotes: the usual sql would accept this as one value, and not lower case . try to debug the request in phpmyadmin, at second glance there are no errors ... - Yura Ivanov
  • there is still an option that you look for by term_taxonomy_id and you need by term_id, which are 9,10,11. they often coincide, but not necessarily ... in general, one should look at the contents of the tables in order to understand what and how. - Yura Ivanov
  • The term_relationships table is the only one with which you can link by record Id, and in it there is only the term_taxonomy_id field. In phpmyadmin, the query works the same way - if there is a rubric 9, then records are selected, if it is not there, then it simply does not see the records. Even already broke his head .. - zink

1 answer 1

Such a connection seems to be true.

 SELECT wposts.* FROM $wpdb->posts wposts LEFT JOIN $wpdb->term_relationships ON (wposts.ID = $wpdb->term_relationships.object_id) LEFT JOIN $wpdb->term_taxonomy ON ($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id) WHERE $wpdb->term_taxonomy.taxonomy = 'category' AND $wpdb->term_taxonomy.term_id in (9,10,11) AND wposts.post_status = 'publish' AND wposts.post_type = 'post' ORDER BY wposts.ID DESC LIMIT $post_num
  • Already better)) There is a zadubenie output records, in how many categories indicated, so many times and displays. And I also needed to impose a condition on the postmeta table, but here it is not there at all. - zink pm
  • naturally. This is a query illustrating the constraints; constraints need to be added as needed. in the documentation they write about three tables in which the heading logic is described, respectively, those id that you take as id categories should correspond to categories, not id from link tables ... As for the distinct, well, yes, you just wrote that the request was not it works, but how exactly it wasn’t specified ... - Yura Ivanov