php - How to get the posts list and the associated tags with the smallest number of queries -

my tables structured follows:

tags (more of category): id, tag name, description, slug

posts: id, title, url ...

poststags: id, idpost, idtag

users: id, username, userslug...

votes: id, idpost, iduser

every post can have 5 tags , every user can vote once. currently, tags not implemented yet, retrieve paginated result set following query:

select p.*, u.username, u.userslug, u.id userid,  exists (select 1 votes v v.iduser=$id , p.userid=v.iduser , p.url = v.url) voted  posts p  join users u on u.id=p.userid order p.created desc limit 10 offset :offset 

the query gets ran via pdo , returned in json format angularjs ng-repeat. $id logged in user's id. use in exists subquery gray out vote buttons in angular view (there check on server side). if clicks username in view, taken detail page user's posts shown (userslug rescue).

the next step include tags in result list , here stuttered. each post in list must contain associated tags (tagname, description, slug) , each tag must take details page associated posts particular tag shown.

the first solution came mind bruteforce way through after running mentioned query:

foreach ($postsresult &$post) {     $sql ="select t.* tags t join poststags pt on t.id=pt.idtag pt.idpost=$post->id";     $stmt=$db->prepare($sql);     $stmt->execute();     $tagsresult=$stmt->fetchall(pdo::fetch_obj);     $post->tags = $tagsresult; } $response->write(json_encode($postsresult)); 

done, easy peasy! lot's of queries generate huge amount of strain on server. , don't want that.

the second solution fire query fetches tags associated postsresult(s) , insert corresponding tags in each post letting php dirty job.

$sql = "     select t.*,             pt.idpost        tags t join poststags pt on t.id=pt.idtag       pt.idpost in (array of post ids) "; $stmt=$db->prepare($sql); $stmt->execute(); $tagsresult = $stmt->fetchall(pdo::fetch_obj);  foreach ($postsresult &$post) {     $arr = array();      foreach ($tagsresult $tag) {         if ($post->id==$tag->idpost) {             $arr[]=$tag;         }     }      $post->tags = $arr; } $response->write(json_encode($postsresult)); 

is there better or faster way this?

is there better or faster way this?

if index $tagsresult postid, can using fetch_group, can remove inner nested loop , grab tags postid in constant time:

$sql = "     select pt.idpost, — select idpost first it’s grouped col            t.*       tags t join poststags pt on t.id=pt.idtag       pt.idpost in (array of post ids) ";  $stmt=$db->prepare($sql); $stmt->execute();  $tagsresult = $smt->fetchall(\pdo::fetch_group|\pdo::fetch_obj); //$tagsresult grouped postid //see https://stackoverflow.com/questions/5361716/is-there-a-way-to-fetch-associative-array-grouped-by-the-values-of-a-specified-c  foreach($postsresult &$post) {      if(isset($tagsresult[$post->id])) {         $post->tags = $tagsresult[$post->id];     }     else {         $post->tags = array();     }    }