rayacoder
(Rayacoder)
March 3, 2014, 4:32pm
1
Hello,
How I can load posts with many categories?
this is my code for example:
$model = TestPost::model()->
with('categories')->findAll(
"category_id = 2"
);
foreach ($model as $model){
echo $model->id. "- ".$model->title;
echo "<br>";
}
By this code we can load posts with cat id 2, How I can use more cats (2,3,7,10) and more for load posts?
qianggan
(qianggan@msn.com)
March 3, 2014, 5:54pm
2
You want to return all post with different categories?
$model = TestPost::model()->
with('categories')->findAll(
"category_id in ( 2, 3, 4 )"
);
rayacoder
(Rayacoder)
March 3, 2014, 6:16pm
3
You want to return all post with different categories?
$model = TestPost::model()->
with('categories')->findAll(
"category_id in ( 2, 3, 4 )"
);
Yes I want return all posts with another cats.
Thank you, I test your code, But don’t worked!
rayacoder
(Rayacoder)
March 3, 2014, 6:27pm
5
what’s error you got?
any sample data in Your TestPost model?
Error is: Undefined offset: 1
Yes, sample data is exist in tables.
qianggan
(qianggan@msn.com)
March 3, 2014, 6:39pm
6
Undefined offset: 1, means your array do not have index = 1. So it’s not because the changes in findAll().
Is that possible to provide more details about your code, and sample data? like how did you defined relationship between TestPost and Categories.
Also assume your category_id is int type.
rayacoder
(Rayacoder)
March 3, 2014, 6:42pm
7
OK, It worked for return all post with some categories.
Now I want return all posts with one or more common thread.
For example return all posts WITH 2 AND 3 cats. HOW ?
rayacoder
(Rayacoder)
March 3, 2014, 6:46pm
8
I’m newbie in yii.
I’m using yii’s examples for test, but I want to learn more about relations.
I see http://www.yiiframework.com/doc/guide/1.1/en/database.arr for create my DB tables and codes.
qianggan
(qianggan@msn.com)
March 3, 2014, 6:53pm
9
You did not provide more relationship definition about your TestPost and Categories. Because you try to use with(‘categories’) in you findAll.
If you can return "category_id = 2 OR category_id = 3", then you should be able to return same result "category_id IN (2,3)".
If you did not get any result, it’s because your SQL statement has error. For example, category_id is not clear if you have category_id both in testPost table and Categories table. In this case you need use like “t.category_id IN (2,3)”.
All my assumption is based on your provided code, and your database is MySQL.
rayacoder
(Rayacoder)
March 3, 2014, 7:03pm
10
It’s OK, I can return with “category_id IN (2,3)”.
No I want return with one or more similar cats.(All posts with 2 && 3 category )
Exam:
Post 1 , Cat = 2
Post 2 , Cat = 2,3
Post 3 , Cat = 2,4
Post 4 , Cat = 3
Post 5 , Cat = 2,3
Post 6 , Cat = 4
My target is Posts with cat 2 && 3 ==== Post 2 + Post 5
qianggan
(qianggan@msn.com)
March 3, 2014, 7:17pm
11
I see what you say, it’s many to many relationship.
So you want to return not “OR” logic, it’s “AND” logic.
$model = TestPost::model()->
with('categories')->findAll(
"category_id = 2 AND category_id = 3"
);
rayacoder
(Rayacoder)
March 3, 2014, 7:23pm
12
I see what you say, it’s many to many relationship.
So you want to return not “OR” logic, it’s “AND” logic.
$model = TestPost::model()->
with('categories')->findAll(
"category_id = 2 AND category_id = 3"
);
Yes, I want “AND” logic, But this code can’t return any post.
In Sql and MySql We can’t use 2 WHERE in one query:
SELECT *
FROM `tbl_post_category`
WHERE `category_id` = 2 AND `category_id` = 3
LIMIT 0 , 30
It returned an empty result!
qianggan
(qianggan@msn.com)
March 3, 2014, 7:41pm
13
If you are using below code
public function relations()
{
return array(
'author'=>array(self::BELONGS_TO, 'User', 'author_id'),
'categories'=>array(self::MANY_MANY, 'Category',
'tbl_post_category(post_id, category_id)'),
);
}
Then
$model = TestPost::model()->
with('categories')->findAll(
"category_id = 2"
);
translated SQL statement should be
SELECT *
FROM `tbl_post` AS `t`
JOIN `tbl_post_cateogry` ON `tbl_post_category`.`post_id`=`t`.`id`
JOIN `tbl_category` ON `tbl_category`.`id` = `t`.`category_id`
WHERE `tbl_category`.`id` = 2 AND `tbl_category`.`id` = 3
rayacoder
(Rayacoder)
March 3, 2014, 7:53pm
14
Yes, I’m using the code you said. But I did not succeed
Thank You Dear Johnny Gan
qianggan
(qianggan@msn.com)
March 3, 2014, 9:05pm
15
OK, back to the SQL statement, because the tbl_post_category just stored (post_id, category_id) pair, so if you want to get one post_id which in two different categories, you can use below SQL statement.
SELECT GROUP_CONCAT( `category_id` ) AS categories
FROM `tbl_post_category`
GROUP BY post_id
But in your case, you can just do a little change based on your original code:
$postsInCategoy2 = TestPost::model()->
with('categories')->findAll(
"category_id = 2"
);
$postInCategory3 = TestPost::model()->
with('categories')->findAll(
"category_id = 3"
$displayPosts=array();
foreach ($postsInCategory3 as $post){
if(in_array($post, $postInCategory3)
{
$displayPosts[]=$post;
}
}
foreach($displayPosts AS $model)
{
echo $model->id. "- ".$model->title;
echo "<br>";
}
Above code may not in practical. So you can crate function in TestPost model like below
public function foundPostsInCategories($category_ids='')
{
if($category_ids!='')
{
$categoryCondition="category_id IN (".$category_ids.")";
}
$posts=TestPost::model()->
with('categories')->findAll(
$categoryCondition
);
$displayPosts=array();
foreach($posts AS $post)
{
$postCategories=array();
foreach($post->categories AS $category)
{
$postCategories[]=$category->id;
}
if(implode(',',$postCategories)==$category_ids)
{
$displayPosts[]=$post;
}
}
foreach($displayPosts AS $model)
{
echo $model->id. "- ".$model->title;
echo "<br>";
}
}
Then you can call
TestPost::model->foundPostsInCategories('2,3')
to get all posts which categories=2,3;
rayacoder
(Rayacoder)
March 4, 2014, 4:42am
16
Thank you very much , I’m at work, I test your code after returning to home.