Skip to content

Instantly share code, notes, and snippets.

@ashmckenzie
Created January 8, 2011 00:56
Show Gist options
  • Save ashmckenzie/770390 to your computer and use it in GitHub Desktop.
Save ashmckenzie/770390 to your computer and use it in GitHub Desktop.
CakePHP HABTM problem
CREATE TABLE `users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(100) NOT NULL,
`created` datetime DEFAULT NULL,
`modified` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
);
CREATE TABLE `announcements` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`message` text NOT NULL,
`created` datetime DEFAULT NULL,
`modified` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
);
CREATE TABLE `announcements_users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`announcement_id` int(11) NOT NULL,
`user_id` int(11) NOT NULL,
`deleted` int(1) NOT NULL DEFAULT '0',
`created` datetime DEFAULT NULL,
`modified` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
);
class User extends AppModel {
var $name = 'User';
var $hasAndBelongsToMany = array('Announcement');
}
class Announcement extends AppModel {
var $name = 'Announcement';
}
class AnnouncementUser extends AppModel {
var $name = 'AnnouncementUser';
}
@ashmckenzie
Copy link
Author

SQL Produced: -

SELECT `Announcement`.`id`, `Announcement`.`from`, `Announcement`.`subject`, `Announcement`.`message`, `Announcement`.`visible`, `Announcement`.`created`, `Announcement`.`modified`, `AnnouncementsUser`.`id`, `AnnouncementsUser`.`announcement_id`, `AnnouncementsUser`.`user_id`, `AnnouncementsUser`.`deleted`, `AnnouncementsUser`.`created`, `AnnouncementsUser`.`modified` FROM `announcements` AS `Announcement` JOIN `announcements_users` AS `AnnouncementsUser` ON (`AnnouncementsUser`.`user_id` = 3 AND `AnnouncementsUser`.`announcement_id` = `Announcement`.`id`)

Where as I really need the SQL to be a LEFT JOIN: -

SELECT `Announcement`.`id`, `Announcement`.`from`, `Announcement`.`subject`, `Announcement`.`message`, `Announcement`.`visible`, `Announcement`.`created`, `Announcement`.`modified`, `AnnouncementsUser`.`id`, `AnnouncementsUser`.`announcement_id`, `AnnouncementsUser`.`user_id`, `AnnouncementsUser`.`deleted`, `AnnouncementsUser`.`created`, `AnnouncementsUser`.`modified` FROM `announcements` AS `Announcement` LEFT JOIN `announcements_users` AS `AnnouncementsUser` ON (`AnnouncementsUser`.`user_id` = 3 AND `AnnouncementsUser`.`announcement_id` = `Announcement`.`id`) 

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment