[eluser]meridimus[/eluser]
I'm writing a blog in CI and I have multiple categories associated with each post. My question is, how can I utilise active record, mysql, php and my table structure to pull from multiple places at once.
For example:
3 tables: Blog (post info), BlogCategories (category details), BlogCategoriesMap (id map)
The BlogCategoriesMap links all Blog posts to their BlogCategories. There can be more than one Category linked to a single blog post and multiple blog posts.
I'm showing 10 posts per page and each post has its own categories associated. So, the question is how BEST to deal with this. Here are my old skool solutions.
1. Run the "get latest 10 blog posts query"
2. When parsing the view in a foreach loop do another additional query that pulls all the categories for that post. That's 11 queries!
Horrible and old and I hate it. Next.
1. Run the "get latest 10 blog posts query".
2. foreach as $query->result_array(); and add categories as a sub array of each blog post then dual foreach inside the view. Still 11 queries.
Gross again.
Another lame attempt.
1. Run the "get latest 10 blog posts" and join blogCategoriesMap and blogCategories
2. foreach in the view but store each duplicated blog post and for each duplicate that's hit, write a category link. 1 query.
Terrible again.
I need to know what the best way is, I'm completely self taught too so there might be something glaringly obvious that should be staring me in the face ONLY I CAN'T SEE IT! HELP ME!
