Joining Two Tables Using Only Foreign Keys And Join

Yii 1.1 General Discussion
1

Hi,

I try for several hours to specify a CDbCriteria for getting entrys with the same value.

I want have something like this:

[sql]SELECT * FROM forum_post

INNER JOIN user_gallery

ON forum_post.user_id = user_gallery.user_id;[/sql]

I tried to do it with this code:




$loadid = Yii::app()->user->id;

$criteria = new CDbCriteria;

$criteria -> condition = ("user_id=$loadid");

$data = UserGallery::model() -> findAll($criteria);

	$arr = array();

	foreach ($data as $userGallery) {

		if($userGallery->uploaded_picture!=NULL) {

			$arr[]= $userGallery->uploaded_picture;

		}

	}

My table structure is:

user_gallery table:

[xml]-----------------------------------

|id | user_id | image | pro_pic |

|1 | 1 | Null | new.jpg |

|2 | 1 | 1.jpg | NULL |

|3 | 1 | new.gif| NULL |

-----------------------------------[/xml]

forum_post table:

[xml]

|id | user_id | ststus | photo_id |

|1 | 1 | hello! | NULL |

|2 | 1 | NULL | 2 |

|3 | 1 | NULL | 3 |

-----------------------------------[/xml]

Now i want to get the profile picture of user who posted the post in the forum_post table.

Now I’m getting current user_id. I want to get all the user_id’s of user_gallery table… Please help me.

2

Here is an example for you.

you can find the correct relation name which i used "usergallery0" in your ForumPost model. Change it with the correct name and go on…




$forum = ForumPost::model()->with('usergallery0')->findall();

foreach($forum as $f){

 echo $f->usergallery0->pro_pic;

}
3

Thanks for response McQueen, for that way I’m not getting the exact out put. So, i have created one field in the forum_post table as pro_pic_id(PK to user_gallery(table) id). Now when user post(upload image or content), I want to store only particular users user_gallery id in the forum_post table. So, i’m getting the particular user id with pro_pic, using this MySQL query :

[sql]SELECT id FROM user_gallery where user_id=24 AND uploaded_picture NOT IN(‘NULL’);

[/sql]

How to imeplement this query in the CDbCriteria or otherwise is any-other way to get the pro_pic from user+gallery table. :(