Hey, i have 3 tables and want to show movies that haven’t been watched by a specific viewer.
CREATE TABLE viewer (
id INT NOT NULL PRIMARY KEY,
name VARCHAR(45))
CREATE TABLE movie (
id INT NOT NULL PRIMARY KEY,
title VARCHAR(45))
CREATE TABLE viewer_watched_movie (
viewer_id INT NOT NULL,
movie_id INT NOT NULL,
liked TINYINT(1),
PRIMARY KEY (viewer_id, movie_id),
CONSTRAINT fk_viewer_watched
FOREIGN KEY (movie_id) REFERENCES movie (id)
CONSTRAINT fk_movie_watched_by
FOREIGN KEY (viewer_id) REFERENCES viewer (id))
This is the mysql:
SELECT * FROM movie m
where not exists(
SELECT * FROM viewer v, viwer_watched_movie vm
where v.id = vm.viewer_id and
vm.movie_id = m.id and
v.id = 1)
I want to show this table in a viewer_watched_movie form.
I tried something like this.
$criteria=new CDbCriteria;
$criteria->with = array('viewer', 'viewer_watched_movie', 'movie');
$criteria->addCondition('"like the statement above"');
But I am stuck there and couldn’t find an adaptable code.