Laravel code:
$posts = array();
$allPosts = DB::table('post_categories')
->Join('posts', 'posts.id', '=', 'post_categories.posts_id')
->select('posts.title','posts.id','posts.body','posts.created_at')
->where('post_categories.categories_id','!=',5)
->orderBy('posts.created_at','desc')
->get();
foreach ($allPosts as $post){
$categories = DB::table('post_categories')
->Join('categories', 'categories.id', '=', 'post_categories.categories_id')
->select('categories.name','categories.id')
->where('post_categories.posts_id','=',$post->id)
->get();
$post->categories = $categories;
array_push($posts,$post);
}
Model relations: Posts 1 - m post_categories m - 1 categories
first query is used fetch posts without category number 5 second is used to fetch categories in a post.
problem is i have a n+1 query due to the for loop. so i was wondering if there was a better way to do this without the for loop
debugbar result: screenshot from debugbar
post_categories looks like a many:many mapping table. If so follow the indexing advice in http://mysql.rjweb.org/doc.php/index_cookbook_mysql#many_to_many_mapping_table
Also, posts needs INDEX(created_at).
For further discussion, please provide SHOW CREATE TABLE and EXPLAIN SELECT ...
