I am trying to generate a query where I can test if it will output the same results with the view I created.
So I created a view where I filter a custom post type with a taxonomy
This is what I come up with but it showing nothing, I'm not sure with the tables
Please hide this part:
Select all Proof Points with selected Accelerator categories/taxonomies
SELECT posts_proof_point.ID AS proof_point_ID,
posts_proof_point.post_date AS proof_point_post_date
FROM wpab_posts AS posts_proof_point
INNER JOIN (SELECT name, object_id as id FROM wpab_terms AS proof_point_taxonomy_accelerator_tbl_terms INNER JOIN wpab_term_taxonomy AS proof_point_taxonomy_accelerator_tbl_termtaxonomy ON proof_point_taxonomy_accelerator_tbl_termtaxonomy.term_id = proof_point_taxonomy_accelerator_tbl_terms.term_id AND proof_point_taxonomy_accelerator_tbl_termtaxonomy.taxonomy = 'accelerator' INNER JOIN wpab_term_relationships AS rel_proof_point_taxonomy_accelerator_tbl ON proof_point_taxonomy_accelerator_tbl_termtaxonomy.term_taxonomy_id = rel_proof_point_taxonomy_accelerator_tbl.term_taxonomy_id) AS proof_point_taxonomy_accelerator_tbl
ON proof_point_taxonomy_accelerator_tbl.name IS NOT NULL
AND proof_point_taxonomy_accelerator_tbl.id = 'posts_proof-point.ID'
WHERE 1=1
AND posts_proof_point.post_type = 'proof-point'
Is there a way I can see the query created by the view? Or can you point out what I did wrong or miss?
Is there a way I can see the query created by the view?
Yes, you can go to Toolset > Settings > Frontend Content and activate debug mode. Reload the page on the front-end of the site where your View is included. You will see a popup window with information about each View on the page, including the query criteria. If you do not see a popup, you may have to disable your browser's popup blocker temporarily.
Or can you point out what I did wrong or miss?
Well I'm not great with SQL queries, but I can show you how to use WP_Query to do something like this in PHP instead. This bypasses those pesky SQL joins and table names:
function execute_test_query_func($atts) {
$titles = '';
$args = array(
'post_type' => 'proof-point',
'tax_query' => array(
'relation' => 'AND',
array(
'taxonomy' => 'accelerator',
'field' => 'term_id',
'terms' => array( 123, 456, 789 ),
),
),
);
$the_query = new WP_Query( $args );
// The Loop
if ( $the_query->have_posts() ) {
$titles .= '<ul>';
while ( $the_query->have_posts() ) {
$the_query->the_post();
$titles .= '<li>' . get_the_title() . '</li>';
}
$titles .= '</ul>';
/* Restore original Post Data */
wp_reset_postdata();
} else {
// no posts found
}
return $titles;
}
add_shortcode("execute_test_query", "execute_test_query_func");
Replace 123, 456, 789 with a comma-separated list of term IDs you want to include in the filter. Then place the shortcode in a page on your site to see a list of proof-point posts associated with those terms.
More info about taxonomy queries in WP_Query:
https://codex.wordpress.org/Class_Reference/WP_Query#Taxonomy_Parameters
Hi,
I need to see the database query through PHPMyAdmin. Can you guide me what tables I should see/check?
I can try. What specific information do you need to locate? Looking at each SELECT / FROM in your query:
SELECT posts_proof_point.ID...,posts_proof_point.post_date...
FROM wpab_posts...
This looks good to me.
SELECT name, object_id as id FROM wpab_terms
There is not a standard column 'object_id' in the wpab_terms table, unless you have added one. I think you need "term_id" here.
I just need to get the (number of) Proof Points from one taxonomy.
SELECT posts_proof_point.ID AS proof_point_ID,
posts_proof_point.post_date AS proof_point_post_date
FROM wpab_posts AS posts_proof_point
<b>INNER JOIN (SELECT name, object_id as id FROM wpab_terms AS proof_point_taxonomy_accelerator_tbl_terms INNER JOIN wpab_term_taxonomy AS proof_point_taxonomy_accelerator_tbl_termtaxonomy ON proof_point_taxonomy_accelerator_tbl_termtaxonomy.term_id = proof_point_taxonomy_accelerator_tbl_terms.term_id AND proof_point_taxonomy_accelerator_tbl_termtaxonomy.taxonomy = 'accelerator' INNER JOIN wpab_term_relationships AS rel_proof_point_taxonomy_accelerator_tbl ON proof_point_taxonomy_accelerator_tbl_termtaxonomy.term_taxonomy_id = rel_proof_point_taxonomy_accelerator_tbl.term_taxonomy_id) AS proof_point_taxonomy_accelerator_tbl
ON proof_point_taxonomy_accelerator_tbl.name IS NOT NULL
AND proof_point_taxonomy_accelerator_tbl.id = 'posts_proof-point.ID'</b>
WHERE 1=1
AND posts_proof_point.post_type = 'proof-point'
The bolded part is where I am not sure. How many tables are connected between a custom post type and a taxonomy?
Term details are in wp_terms.
Terms are related to a taxonomy in wp_term_taxonomy.
Terms are related to posts in wp_term_relationships.
Post details are in wp_posts.
Try this code:
SELECT posts.ID as id
FROM wpab_posts
AS posts
INNER JOIN (
SELECT wpab_term_relationships.object_id as id
FROM wpab_term_relationships
INNER JOIN wpab_terms
ON wpab_terms.term_id = wpab_term_relationships.term_taxonomy_id
INNER JOIN wpab_term_taxonomy
ON wpab_term_taxonomy.term_id = wpab_term_relationships.term_taxonomy_id
AND wpab_term_taxonomy.taxonomy = 'accelerator'
)
AS relposts
ON posts.ID = relposts.id
WHERE posts.post_type = 'proof-point'
This should give you a list of proof-point posts by ID.
Hi Christian,
Your query worked.
One more question, how can I get the wpcf value.
I'm trying to get the field "wpcf-proof-point-date-last-updated"
Tables used:
1. wpab_posts
2. wpab_postmeta
SELECT posts.ID AS id,
CONCAT('<a href="',posts.guid,'">',posts.post_title,'</a>') AS title,
YEAR(updated_tbl.meta_value) AS date_last_updated
FROM wpab_posts AS posts
INNER JOIN (
SELECT tbl_posts.ID as id, meta_value, meta_key
FROM wpab_postmeta AS tbl_postmeta
INNER JOIN wpab_posts AS tbl_posts
ON tbl_postmeta.post_id = tbl_posts.ID
AND tbl_posts.post_type = 'proof-point')
AS updated_tbl
ON updated_tbl.meta_key = 'wpcf-proof-point-date-last-updated' AND updated_tbl.id = posts.ID
WHERE posts.post_type = 'proof-point'
The code above doesn't output date, instead, number. Please see attached image. Tried to cast the date but no output.
The code above doesn't output date, instead, number.
Correct, a date custom field value is stored in the database as a Unix timestamp. You can format that timestamp however you would like with PHP, for example:
$date = date('m/d/Y', $timestamp);
php.net/manual/en/function.date.php
Or you can use a timestamp conversion tool online to inspect the values individually:
hidden link
Hi Christian,
I used from_unixtime() to convert it to human readable.
SELECT posts.ID as id
FROM wpab_posts
AS posts
INNER JOIN (
SELECT wpab_term_relationships.object_id as id
FROM wpab_term_relationships
INNER JOIN wpab_terms
ON wpab_terms.term_id = wpab_term_relationships.term_taxonomy_id
INNER JOIN wpab_term_taxonomy
ON wpab_term_taxonomy.term_id = wpab_term_relationships.term_taxonomy_id
AND wpab_term_taxonomy.taxonomy = 'accelerator'
)
AS relposts
ON posts.ID = relposts.id
WHERE posts.post_type = 'proof-point'
Btw, I got an error here. How can I make the ID unique? Show once only.
Used DISTINCT. Thank you so much!
