Combine 2 SQL queries in order to avoid foreach loop

Question

I want to query a wordpress database in order to get all the desired posts alongside with their images. I have managed to do it with 2 different queries. The first one, brings all the posts alongside with the desired fields and the second one, runs in a foreach loop in order to get the image for each post. My problem is that this takes a really long time and i want to avoid id. I need a solution in order to avoid the foreach loop in the second query and merge it into the first.

Query 1

"SELECT * FROM wp_posts";

And foreach returned posts.ID

Query 2

"SELECT wp_posts.guid IN (Select wp_postmeta.meta_value from wp_postmeta where wp_postmeta.meta_key = '_thumbnail_id' AND wp_postmeta.post_id = ('the returned post id foreach of ID output of the first query')"

Pathik Vejani · Accepted Answer · 2016-01-23 10:31:19Z

1

Using normalization method it will solve your problem by avoiding two queries:

SELECT WP.guid
FROM wp_posts WP,
     wp_postmeta WPM
WHERE wp_postmeta.meta_key = '_thumbnail_id'
  AND WPM.post_id=WP.yourForeignField
GROUP BY WP.guid

edited Jan 23, 2016 at 10:31

Pathik Vejani

4,49711 gold badges66 silver badges110 bronze badges

answered Jan 23, 2016 at 10:28

ameenulla0007

2,6831 gold badge15 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Shravan Sharma · Accepted Answer · 2016-01-23 10:30:59Z

0

You can use wordpress default function get_posts() like this.

$args = array('posts_per_page'=> -1,'meta_key'=> '_thumbnail_id' );
get_posts( $args );

You can get more information from here

answered Jan 23, 2016 at 10:30

Shravan Sharma

9898 silver badges17 bronze badges

Comments

TinyTheBrontosaurus · Accepted Answer · 2016-01-23 10:34:58Z

0

I know in posgresql, a JOIN would do this. I don't know enough about word press, but I'll look into it and update this answer

UPDATE This should do it:

SELECT guid FROM wp_posts 
OUTER JOIN wp_postmeta on wp_postmeta.post_id = wp_posts.id
where wp_postmeta.meta_key = '_thumbnail_id';

The JOIN combines the two tables on a specific key.

edited Jan 23, 2016 at 10:34

answered Jan 23, 2016 at 10:28

TinyTheBrontosaurus

4,9806 gold badges25 silver badges36 bronze badges

Collectives™ on Stack Overflow

Combine 2 SQL queries in order to avoid foreach loop

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related