Filter blog posts by category

maxbel · March 2015

Hi,

If I use:
$CI->fuel_blog->get_category_posts(array('cat1','cat2'))
I will get all the posts that are either in category 'cat1' or category 'cat2'.

Is there a way of easily getting only the posts that figure in both categories?

admin · March 2015

You can't that way (it is actually intended to be used with a single category. You can always target the model specifically like so:

$CI->load->module_model(BLOG_FOLDER, 'blog_posts_model');
$CI->blog_posts_model->db()->where('blog_categories.slug', 'cat1');
$CI->blog_posts_model->db()->or_where('blog_categories.slug', 'cat2');
$cat_posts = $CI->blog_posts_model->find_all();

maxbel · March 2015

Ok, I understand the logic, but I can't target the blog_posts_model. I'm getting an error:

Message: Undefined property: Page_router::$blog_posts

My BLOG module is in "./fuel/modules/blog", default install - no changes. I'm using Fuel 1.2.1.

What am I doing wrong?

admin · March 2015

I had a typo in the code... it should have been "blog_posts_model" instead of "blog_post"

maxbel · March 2015

D'oh, I could've/should've caught that. The same typo is in the last line. Should be:
$cat_posts = $CI->blog_posts_model->find_all();
But now I'm getting a DB error:


Unknown column 'blog_categories.slug' in 'where clause'

SELECT `fuel_blocks`.* FROM (`fuel_blocks`) WHERE `blog_categories`.`slug` = 'cat1' OR `blog_categories`.`slug` = 'cat2' AND `name` = 'commonHeader' AND `language` = 'fr' AND `fuel_blocks`.`published` = 'yes' LIMIT 1

I'm trying to figure out why it is pulling from the fuel_blocks table. Any ideas?

admin · March 2015

I think you want fuel_blog_categories.slug.

maxbel · March 2015

That is indeed the one I want, but I had already tried it. Same type of error:


Unknown column 'fuel_blog_categories.slug' in 'where clause'

SELECT `fuel_blocks`.* FROM (`fuel_blocks`) WHERE `fuel_blog_categories`.`slug` = 'cat1' OR `fuel_blog_categories`.`slug` = 'cat2' AND `name` = 'commonHeader' AND `language` = 'fr' AND `fuel_blocks`.`published` = 'yes' LIMIT 1

admin · March 2015

Hmmm. Is it this call that is causing that error?
$cat_posts = $CI->blog_posts_model->find_all();
There is a has_many blocks relationship in the blog_posts_model. If you comment that out, what happens?
Also, change any ->db-> to ->db()->:


$CI->blog_posts_model->db()->where('blog_categories.slug', 'cat1');
$CI->blog_posts_model->db()->or_where('blog_categories.slug', 'cat2');

maxbel · March 2015

The find_all() call is not generating the error, it is originating from the where clauses. Everything works fine if I remove these (all the posts are returned).

Commenting out the blocks relationship in blog_posts_model has no effect (I had already tried).

Changing db to db()...

$CI->blog_posts_model->db()->where('blog_categories.slug', 'cat1');
$CI->blog_posts_model->db()->or_where('blog_categories.slug', 'cat2');

... does work. However the result is identical to the original ...
$CI->fuel_blog->get_category_posts(array('cat1','cat2')) ...since the where clauses are combined using OR.

What I'm looking for is combining the where clauses with AND. Here is where it gets sticky again. If I call for only one condition, either cat1 or cat2...


$CI->blog_posts_model->db()->where('blog_categories.slug', 'cat1');

... it works fine.

If I call for both using an array...

$CI->blog_posts_model->db()->where(array('fuel_blog_categories.slug'=>'cat1','fuel_blog_categories.slug'=>'cat2') );

...only the last condition is met (here only matches for cat2 will be returned).

Now if I call for both sequentially...

$CI->blog_posts_model->db()->where('blog_categories.slug', 'cat1');
$CI->blog_posts_model->db()->where('blog_categories.slug', 'cat2');

...which essentially should be the same as using the array to pass the where conditions, nothing is returned.

admin · March 2015

Are you able to use $CI->blog_posts_model->debug_query() to see what query is being generated?

maxbel · March 2015

This is what's being generated:

SELECT fuel_blog_posts.*, fuel_blog_users.display_name, CONCAT(fuel_users.first_name, " ", fuel_users.last_name) as author_name, YEAR(fuel_blog_posts.post_date) as year, DATE_FORMAT(fuel_blog_posts.post_date, "%m") as month, DATE_FORMAT(fuel_blog_posts.post_date, "%d") as day FROM (`fuel_blog_posts`) LEFT JOIN `fuel_relationships` ON `fuel_relationships`.`candidate_key` = `fuel_blog_posts`.`id` AND fuel_relationships.candidate_table = "fuel_blog_posts" AND fuel_relationships.foreign_table = "fuel_blog_categories" LEFT JOIN `fuel_blog_users` ON `fuel_blog_users`.`fuel_user_id` = `fuel_blog_posts`.`author_id` LEFT JOIN `fuel_users` ON `fuel_users`.`id` = `fuel_blog_posts`.`author_id` LEFT JOIN `fuel_blog_categories` ON `fuel_blog_categories`.`id` = `fuel_relationships`.`foreign_key` WHERE `fuel_blog_posts`.`id` = 0 GROUP BY `fuel_blog_posts`.`id` LIMIT 1

It reports this query regardless of which WHERE conditions are used. I'm not sure if I'm using it right. I've inserted $CI->blog_posts_model->debug_query() right before $vars['posts'] = $CI->blog_posts_model->find_all();, in my variables file.

admin · March 2015

You should use debug query right after the query you want to debug (not before).

maxbel · March 2015

Ok, got it. If I use the array for my WHERE parameters, this is the generated query:

SELECT fuel_blog_posts.*, fuel_blog_users.display_name, CONCAT(fuel_users.first_name, " ", fuel_users.last_name) as author_name, YEAR(fuel_blog_posts.post_date) as year, DATE_FORMAT(fuel_blog_posts.post_date, "%m") as month, DATE_FORMAT(fuel_blog_posts.post_date, "%d") as day FROM (`fuel_blog_posts`) LEFT JOIN `fuel_relationships` ON `fuel_relationships`.`candidate_key` = `fuel_blog_posts`.`id` AND fuel_relationships.candidate_table = "fuel_blog_posts" AND fuel_relationships.foreign_table = "fuel_blog_categories" LEFT JOIN `fuel_blog_users` ON `fuel_blog_users`.`fuel_user_id` = `fuel_blog_posts`.`author_id` LEFT JOIN `fuel_users` ON `fuel_users`.`id` = `fuel_blog_posts`.`author_id` LEFT JOIN `fuel_blog_categories` ON `fuel_blog_categories`.`id` = `fuel_relationships`.`foreign_key` WHERE `fuel_blog_categories`.`slug` = 'cat2' AND `fuel_blog_posts`.`published` = 'yes' GROUP BY `fuel_blog_posts`.`id`

The WHERE clause only contains the last array element (cat2); these are the posts that are returned.

If I specifiy them seperatly, I get this query:

(the first par of the query is identical, so I won't repeat) ...
`fuel_relationships`.`foreign_key` WHERE `fuel_blog_categories`.`slug` = 'cat1' AND `fuel_blog_categories`.`slug` = 'cat2' AND `fuel_blog_posts`.`published` = 'yes' GROUP BY `fuel_blog_posts`.`id`

This one doesn't return anything. Not an expert, but the queries seem to be constructed correctly. I wouldn't know where to look next.

admin · March 2015

I think it's not returning anything because there won't ever be a single record with both cat1 and cat2 for the slug value... you'll need an OR instead of an AND to get both those categories.

maxbel · March 2015

So, if I need to get posts that fit multiple categories at once, the best way would be to build my own method in the blog_posts model, right?

admin · March 2015

You could build it on the blog_posts model but would probably recommend creating your own class or function that creates the query using the model that way you can safely make updates to the blog module without overriding your own code. One simple place for this may be in the fuel/application/helpers/my_helper.php file which is included automatically. Or you can create your own class in fuel/application/libraries

maxbel · March 2015

Ok great. Thanks for your help!

Howdy, Stranger!

Categories

Filter blog posts by category

Comments