Hi Everyone,

I need to convert a Wordpress site into a Joomla site and I'm having trouble getting a query to work properly.

Right now I'm able to import the post and the categories form Wordpress into Joomla but what happen is that whenever I import the posts they lose the link with the category with they belong so a post that used to be in the category "Social Media" has now no category at all. So all my imported posts have no category in Joomla.

I think I know where I'm having the problem but I don't know how to solve it.
So here is what I think the problem is.
In the Wordpress database, the category is linked with the post ID but since we can assign multiple categories to a post it has a relation many to many and it has two other tables between wp_terms (categories) and wp_posts (posts) which are wp_term_relationships and wp_term_taxonomy.
So here is how they are linked.
Table wp_posts, field ID has a relation 1 to many with table wp_term_relationships field object_id and field term_taxonomy_id has a relation 1 to may with table wp_term_taxonomy field term_taxonomy_id and finally field term_id has a relation 1 to many with table wp_terms field term_id.
You can find the database structure here: http://codex.wordpress.org/images/8/83/WP_27_dbsERD.png

What I need to do is select all fields from table wp_posts and the term_id from table wp_terms that is related to this post, like this when I import the posts they have a category assigned.

Does anyone know how to create a query that would allow me to do that?

Thank you very much in advance for your precious help.

I've been searching over the internet but no success.

It's becoming frustrating because I feel I'm so close to solve the problem and I can't because of a query :P

Please does any one know how I can achieve this query?

Thank you in advance for your precious help.

Hi Everyone,

So after many searches I was able to get the query to work, well mostly

So here is my query:

$query = "SELECT wposts.*, " . $this->_config['prefix'] . "term_taxonomy.*
FROM wp_posts wposts
LEFT JOIN wp_postmeta wpostmeta ON wposts.ID = wpostmeta.post_id
LEFT JOIN wp_term_relationships ON (wposts.ID = wp_term_relationships.object_id)
LEFT JOIN wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id)
WHERE wp_term_taxonomy.taxonomy = 'category'
AND wposts.post_status = 'publish'
AND wposts.post_type = 'post'";

This query select indeed all the published posts into the Wordpress database with its category, the only problem is that it select all the posts twice.

Does any one has any idea on where could be the problem?

Thank you

Could it be that you are not joining on the entire foreign key between 2 of the tables? Or you need to select the max entry of one of the tables?
Dave

Hi dav1mo,

I found how to avoid the query to return twice the result I had to use SELECT DISTINCT and now it is working perfect.

Thank you for your time.
Helio