Connecting tables when querying in MySQL

Question

I have 3 tables: tco_articles, tco_module_eostext and tco_articles_modules. My tco_articles has unique id key. One for each article. My tco_module_eostext has unique instance_id that belongs to each article. My tco_articles_modules contains all article_ids, but have 9 times as much instance_ids that are used in other tables.

So I can have article_id with instance_id that when you query in the tco_module_eostext will return empty.

I'd like to make a query that will return correct body text for the correct article.

So far I have:

global $wpdb;
$posts = array();

$ids = $wpdb->get_results('SELECT DISTINCT instance_id, article_id FROM tco_articles_modules', ARRAY_A);

This returns array with all the instances and ids like:

Array
(
    [0] => Array
        (
            [instance_id] => 928615
            [article_id] => 129396
        )

    [1] => Array
        (
            [instance_id] => 928616
            [article_id] => 129396
        )

    [2] => Array
        (
            [instance_id] => 928617
            [article_id] => 129396
        )

    [3] => Array
        (
            [instance_id] => 928618
            [article_id] => 129396
        )

You can see that the article_ids are the same but instance_id. When you put

$wpdb->get_results('SELECT body FROM tco_module_eostext WHERE instance_id=928617 ', ARRAY_A);

You may get empty, but for

$wpdb->get_results('SELECT body FROM tco_module_eostext WHERE instance_id=928618 ', ARRAY_A);

You could have some body text.

This is my problem. I need to go through all of them and filter out the not empty ones and assign them correct article. I managed to output the articles

foreach ($ids as $key => $value) {
    $instance_ID = $value['instance_id'];
    $article_ID = $value['article_id'];

    $article_out = $wpdb->get_results('SELECT * FROM tco_articles WHERE id='.$article_ID.' ', ARRAY_A);
    $posts[$article_ID] = $article_out[0];

}

Which returns something like:

Array
(
    [129396] => Array
        (
            [id] => 129396
            [headline] => Bla bla bla title
            [intro] => This is cool article intro
            [needs_review] => 0
            [published] => 2014-12-16 09:17:00
            [unpublished] => 
            [hidden] => 0
            [no_single_page] => 0
            [permalink] => link-perma-link-here
            [internal_slug] => 
            [type_id] => 3
            [thread_id] => 0
            [news_id] => 0
            [header_game_id] => 0
            [puff_hh_id] => 0
            [puff_title] => 
            [hdrcol_id] => 900
            [review_queued] => 
            [lock_timeout] => 0
            [created] => 2014-12-16 09:17:00
            [updated] => 2015-01-16 13:51:30
            [created_by] => 84142
            [updated_by] => 84142
        )
    ...

Now I'd like to append the body text from the tco_module_eostext table.

Is there a query I can use to do this automatically or to do this one at the time and then append to the $posts array?

The foreach method of querying is kinda slow when you have 180000+ posts.

Any help is appreciated.

Answer 1

If you are sure that there is always only one row in tco_module_eostext against each article_id, you can use JOIN (inner join), which will only show one row for each article_id.

SELECT a.*, t.body
FROM tco_articles a
JOIN tco_articles_modules m ON m.article_id = a.id
JOIN tco_module_eostext t ON m.instance_id = t.instance_id
//WHERE .....

But, this will not show any row of some articles if there is no entry in other two tables for that article_id. But there is still way to solve this. We can use LEFT OUTER JOIN and then make sure we only make the join if there is any row in tco_module_eostext for any instace_id. This will make sure you get at least the article info from tco_articles table when there is no data in other tables.

SELECT a.*, t.body
FROM tco_articles a
LEFT OUTER JOIN tco_articles_modules m ON m.article_id = a.id AND EXISTS ( SELECT NULL FROM tco_module_eostext WHERE instance_id = m.instance_id )
LEFT OUTER JOIN tco_module_eostext t ON m.instance_id = t.instance_id
//WHERE .....