Fetching results from different tables with the same structure

Question

I'm being asked if I can fetch the result from 18 different Wordpress databases merged into one database with conflicting names, filter them, and then play with the results in PHP.

For example:

I have data (posts) in ara_posts, val_posts, and car_posts (all of them are tables that have post_name, post_type, post_status and post_date in their column names). I want to select all the data from those tables and filter the result (only show results that have post_type = 'post').

What I want to do with the result is compare the dates and fetch the latest 3 posts from all those 18 tables, that have their status to "publish" and the post type "post".

How is that possible?

The latest 3 from each (so 3x18 rows) or the latest 3 from all tables? — ypercubeᵀᴹ, Jul 03 '11 at 16:49
@ypercube The latest 3 (according to GMT) of all tables - it will fetch the posts, then compare which has the newest GMT post date, no matter what table. — AeroCross, Jul 04 '11 at 01:19

score 2 · Accepted Answer · edited May 23 '17 at 12:27

2

As proposed in an answer to this question quite similar to yours, you can probably use the UNION operator. Something like this:

(SELECT post_name, post_type, post_status, post_date FROM ara_posts WHERE post_status='publish' AND post_type='post')
UNION ALL
(SELECT post_name, post_type, post_status, post_date FROM val_posts WHERE post_status='publish' AND post_type='post')
UNION ALL
(...)
ORDER BY post_date DESC
LIMIT 3

edited May 23 '17 at 12:27

Community

1
1

answered Jul 02 '11 at 21:45

Viktor

3,436
1
33
42

This does seem to work, yet it's completely inefficient codewise, because if I would have to copy/paste 18 exact queries for each query I would need to do, am I right? - if I wanted to change the "where" clause in one of those queries, I would have to manually do it for all of them. Doesn't seem optimal to me. – AeroCross Jul 04 '11 at 01:21
@ypercube Couldn't agree more. I think I might have used the wrong expression - doesn't seem "maintanable" code to me. Don't you have any suggestion? – AeroCross Jul 04 '11 at 01:28
@AeroCross: You can have PHP code that produces the SQL query, adding the identical parts (conditions, order, limit) to the 18 subqueries. – ypercubeᵀᴹ Jul 04 '11 at 01:33

score 1 · Answer 2 · answered Jul 03 '11 at 16:37

And you might just add a manual column to the select so you know from which database the data are coming:

(SELECT 'ara' as dbname, post_name, post_type, post_status, post_date FROM ara_posts WHERE post_status='publish' AND post_type='post') 
UNION ALL 
(SELECT 'val' as dbname, post_name, post_type, post_status, post_date FROM val_posts WHERE post_status='publish' AND post_type='post') 
UNION ALL (...) ORDER BY post_date DESC LIMIT 3

ypercubeᵀᴹ · Answer 3 · 2011-07-04T09:43:57.730

I would add the ORDER BY post_date DESC LIMIT 3 part inside all subqueries. this way, all subqueries will use the index of the post_date field of the corresponding table and the main query would only have to sort 18x3 rows.

If you only have that tin the main query, it will have to fetch all (thousands? millions?) rows and then sort. I'm not sure that the optimizer is clever enough not to do that:

( SELECT post_name, post_type, post_status, post_date, 'ara' AS source 
  FROM ara_posts 
  WHERE post_status='publish' 
    AND post_type='post'
  ORDER BY post_date DESC
  LIMIT 3
)
UNION ALL
( SELECT post_name, post_type, post_status, post_date, 'val' AS source 
  FROM val_posts
  WHERE post_status='publish' 
    AND post_type='post'
  ORDER BY post_date DESC
  LIMIT 3
)
UNION ALL
(...)
ORDER BY post_date DESC
LIMIT 3

And Dennis has a very good point on how to identify the source of the final results.

score 1 · Answer 4 · answered Jul 04 '11 at 02:21

1

I'd define a view, based on the union queries in the other answers (but without the where and limit) parts. Then you can query the view directly. A view is the analog of a function - it's your basic unit of reuse for databases.

answered Jul 04 '11 at 02:21

Burleigh Bear

3,274
22
32

This is definitevely a great advice. I'll let you know i it all goes well. – AeroCross Jul 05 '11 at 05:55

Fetching results from different tables with the same structure

4 Answers4