findAll() where not in table Yiiframework

Question

He there,

I have two tables in my database: "Achievements" and "AchievementsCompleted". I would like to select all of the Achievements unless there is an AchievementCompleted entry with that achievement as foreign key constraint.

So for example:

There are 3 rows in the table Achievement:

ID: 1 - Achievement1
ID: 2 - Achievement2
ID: 3 - Achievement3

and there is one entry in the table AchievementCompleted:

achievement_id = 2 completed by user 3

In that case I would like the output to be only the models of id 1 and 3 for user 3.

$criteria = new CDbCriteria();
$criteria->condition = "";
$models = Achievements::model()->findAll($criteria);

Thanks in advance! I hope this makes sense.

Check documentation of cdbcriteria for `join ` or `relation with` — Babu, May 12 '14 at 12:59
Could you maybe give me an example? The documentation is too vague imo. Thanks. — Shark, May 12 '14 at 13:13

score 0 · Answer 1 · answered May 12 '14 at 13:16

0

Try this out

$criteria = new CDbCriteria;
$criteria->join ='INNER JOIN AchievementsCompleted';
$criteria->condition = "AchievementsCompleted.achievement_id != Achievements.id";
$models = Achievements::model()->findAll($criteria);

answered May 12 '14 at 13:16

Vishal Sharma

1,372
1
8
17

score 0 · Answer 2 · edited May 23 '17 at 12:05

0

You need to use a RIGHT JOIN:

Here's you're getting all the Achievements which aren't "Completed":

   $criteria=new CDbCriteria;
   $criteria->join='RIGHT JOIN Achievements ON AchievementsCompleted.Achievement_id=Achievements.id';
   $models = AchievementsCompleted::model()->findAll($criteria);

You need to replace the Table Columns with your own columns ("Achievement_id" and "id").

Here's a visual graph explaining how SQL Joins work: What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN?

edited May 23 '17 at 12:05

Community

1
1

answered May 12 '14 at 13:59

Wissam El-Kik

2,469
1
17
21

He Wissam, this actually gives me a list of the achievements that are completed. How can I spin this around to a list of not completed achievements? Thanks! This is what I have according to your example: $criteria=new CDbCriteria; $criteria->join='RIGHT JOIN tbl_achievements ON t.achievement_id=tbl_achievements.id'; $criteria->condition = 'user_id = ' . Yii::app()->user->id; $models = AchievementsCompleted::model()->findAll($criteria); – Shark May 12 '14 at 14:54

findAll() where not in table Yiiframework

2 Answers2