Skip to content

Instantly share code, notes, and snippets.

@wescleymatos

wescleymatos/innerjoin.txt

Created November 30, 2011 14:34
Show Gist options
  • Save wescleymatos/1409265 to your computer and use it in GitHub Desktop.
Save wescleymatos/1409265 to your computer and use it in GitHub Desktop.
Download ZIP
Relação Entre inner join SQL e no Cakephp
Raw
innerjoin.txt
--
-- Estrutura da tabela `cars`
--
CREATE TABLE IF NOT EXISTS `cars` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`kind_id` int(11) NOT NULL,
`album_id` int(11) DEFAULT NULL,
`type` set('novo','usado') DEFAULT NULL,
`year` varchar(45) DEFAULT NULL,
`cylinder` varchar(45) DEFAULT NULL,
`fuel` varchar(255) DEFAULT NULL,
`doors` int(11) DEFAULT NULL,
`color` varchar(45) DEFAULT NULL,
`image` varchar(255) DEFAULT NULL,
`plaque` varchar(255) DEFAULT NULL,
`km` varchar(55) DEFAULT NULL,
`value` decimal(10,2) DEFAULT NULL,
`description` text,
`active` tinyint(1) DEFAULT NULL,
`created` datetime DEFAULT NULL,
`modified` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `fk_useds_kinds1` (`kind_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;
--
-- Extraindo dados da tabela `cars`
--
INSERT INTO `cars` (`id`, `kind_id`, `album_id`, `type`, `year`, `cylinder`, `fuel`, `doors`, `color`, `image`, `plaque`, `km`, `value`, `description`, `active`, `created`, `modified`) VALUES
(3, 2, 1, 'novo', '2012', '1.8', 'flex', 4, '67', '84ef9e6a9b3375c4e7be8875ec9f8780ab3efa88.jpg', '', '0', '36.00', '<p>\r\n Novo Palio 2012;</p>\r\n', 1, '2011-11-29 14:39:41', '2011-11-29 15:54:55');
-- --------------------------------------------------------
--
-- Estrutura da tabela `cars_colors`
--
CREATE TABLE IF NOT EXISTS `cars_colors` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`car_id` int(11) NOT NULL,
`color_id` int(11) NOT NULL,
`image` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `fk_cars_has_colors_colors1` (`color_id`),
KEY `fk_cars_has_colors_cars1` (`car_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=17 ;
--
-- Extraindo dados da tabela `cars_colors`
--
INSERT INTO `cars_colors` (`id`, `car_id`, `color_id`, `image`) VALUES
(13, 3, 29, 'f796151b863372ba4f5c5f44668f497ae435d622.jpg'),
(14, 3, 31, '5cba04a67d737151490e6e13be3efbfc1d247761.jpeg'),
(15, 3, 44, '07c27dbce7996500043437e71c7a83869789ad15.jpg'),
(16, 3, 1, '413ccf395cc42612709f4828e1e27759fe5c933e.jpg');
-- --------------------------------------------------------
--
-- Estrutura da tabela `kinds`
--
CREATE TABLE IF NOT EXISTS `kinds` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`mark_id` int(11) NOT NULL,
`name` varchar(255) DEFAULT NULL,
`description` text,
`hotsite` varchar(255) DEFAULT NULL,
`active` tinyint(1) DEFAULT NULL,
`created` datetime DEFAULT NULL,
`modified` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `fk_kinds_marks1` (`mark_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=13 ;
--
-- Extraindo dados da tabela `kinds`
--
INSERT INTO `kinds` (`id`, `mark_id`, `name`, `description`, `hotsite`, `active`, `created`, `modified`) VALUES
(1, 1, 'Uno', '', '', 1, '2011-11-24 12:50:41', '2011-11-24 12:50:41'),
(2, 1, 'Palio', '', '', 1, '2011-11-24 12:50:50', '2011-11-24 12:50:50'),
(3, 1, 'Bravo', '', '', 1, '2011-11-24 12:51:03', '2011-11-24 12:51:03'),
(4, 1, 'Idea', '', '', 1, '2011-11-24 12:52:41', '2011-11-24 12:52:41'),
(5, 1, 'Strada', '', '', 1, '2011-11-24 12:52:52', '2011-11-24 12:52:52'),
(6, 1, 'Doblo', '', '', 1, '2011-11-24 12:53:07', '2011-11-24 12:53:07'),
(7, 1, 'Linea', '', '', 1, '2011-11-24 12:54:34', '2011-11-24 12:54:34'),
(8, 1, 'Siena', '', '', 1, '2011-11-24 12:55:01', '2011-11-24 12:55:01'),
(9, 1, 'Freemont', '', '', 1, '2011-11-24 12:55:26', '2011-11-24 12:55:26'),
(10, 1, 'Palio Weekend', '', '', 1, '2011-11-24 12:55:44', '2011-11-24 12:55:44'),
(11, 1, 'Punto', '', '', 1, '2011-11-24 12:56:36', '2011-11-24 12:56:36'),
(12, 1, '500', '', '', 1, '2011-11-24 12:56:52', '2011-11-24 12:56:52');
-- --------------------------------------------------------
--
-- Estrutura da tabela `marks`
--
CREATE TABLE IF NOT EXISTS `marks` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`description` text,
`logo` varchar(255) DEFAULT NULL,
`slogan` varchar(255) DEFAULT NULL,
`created` datetime DEFAULT NULL,
`modified` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=11 ;
--
-- Extraindo dados da tabela `marks`
--
INSERT INTO `marks` (`id`, `name`, `description`, `logo`, `slogan`, `created`, `modified`) VALUES
(1, 'Fiat', '<p>\r\n As atividades do grupo eram inicialmente centralizadas na fabrica&ccedil;&atilde;o de autom&oacute;veis e de ve&iacute;culos industriais e agr&iacute;colas. Na primeira d&eacute;cada do s&eacute;culo XX j&aacute; fabricava tamb&eacute;m locomotivas, e, com o in&iacute;cio da Primeira Guerra Mundial, passou a fabricar ambul&acirc;ncias, metralhadoras e at&eacute; motores para submarinos. Com o tempo, diversificou suas atividades, e hoje o grupo atua em v&aacute;rios setores industriais e financeiros. O centro de suas atividades industriais est&aacute; na It&aacute;lia, por&eacute;m atua atrav&eacute;s de subsidi&aacute;rias em 61 pa&iacute;ses, com 1063 unidades que empregam 223.000 pessoas, 111 mil das quais fora da It&aacute;lia.</p>\r\n', '9520bc68afcd64a31f3a5afa7358cc3061dbcec9.jpg', 'Movidos pela paixão', '2010-12-02 09:35:40', '2011-11-25 11:06:17'),
(2, 'Chevrolet', NULL, NULL, NULL, '2010-12-02 09:35:40', '2010-12-02 09:35:40'),
(3, 'Volkswagen', NULL, NULL, NULL, '2010-12-16 19:02:33', '2011-07-11 15:01:50'),
(4, 'Ford', NULL, NULL, NULL, '2011-02-04 18:11:00', '2011-07-11 15:02:12'),
(5, 'Peugeot', NULL, NULL, NULL, '2011-02-23 14:10:37', '2011-07-11 15:01:57'),
(6, 'Renault', NULL, NULL, NULL, '2011-02-23 14:25:21', '2011-07-11 15:02:06'),
(7, 'Toyota', NULL, NULL, NULL, '2011-04-07 17:25:26', '2011-04-07 17:25:26'),
(8, 'Honda', NULL, NULL, NULL, '2011-04-13 17:02:19', '2011-04-13 17:02:19'),
(9, 'Kia', NULL, NULL, NULL, '2011-07-01 10:31:27', '2011-07-01 10:31:27'),
(10, 'Mitsubishi', NULL, NULL, NULL, '2011-07-11 15:10:22', '2011-07-11 15:10:22');
--
-- Restrições para as tabelas dumpadas
--
--
-- Restrições para a tabela `cars`
--
ALTER TABLE `cars`
ADD CONSTRAINT `fk_useds_kinds1` FOREIGN KEY (`kind_id`) REFERENCES `kinds` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
--
-- Restrições para a tabela `cars_colors`
--
ALTER TABLE `cars_colors`
ADD CONSTRAINT `fk_cars_has_colors_cars1` FOREIGN KEY (`car_id`) REFERENCES `cars` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
ADD CONSTRAINT `fk_cars_has_colors_colors1` FOREIGN KEY (`color_id`) REFERENCES `colors` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
--
-- Restrições para a tabela `kinds`
--
ALTER TABLE `kinds`
ADD CONSTRAINT `fk_kinds_marks1` FOREIGN KEY (`mark_id`) REFERENCES `marks` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
=============================================
SELECT *
FROM `cars_colors` AS `carrocor`
INNER JOIN colors AS `cor` ON ( `carrocor`.`color_id` = `cor`.`id` )
INNER JOIN cars AS `carro` ON ( `carro`.`id` = `carrocor`.`car_id` )
INNER JOIN kinds AS modelo ON ( modelo.id = carro.kind_id )
INNER JOIN marks AS marca ON ( marca.id = modelo.mark_id )
=============================================
$joins = array(
array('table' => 'colors',
'alias' => 'Color',
'type' => 'INNER',
'conditions' => array(
'CarsColor.color_id = Color.id',
)
),
array('table' => 'cars',
'alias' => 'Car',
'type' => 'INNER',
'conditions' => array(
'Car.id = CarsColor.car_id',
)
),
array('table' => 'kinds',
'alias' => 'Kind',
'type' => 'INNER',
'conditions' => array(
'Kind.id = Car.kind_id',
)
),
array('table' => 'marks',
'alias' => 'Mark',
'type' => 'INNER',
'conditions' => array(
'Mark.id = Kind.mark_id',
)
)
);
$this->paginate = array('joins'=>$joins,'fields' => array('Mark.id, Mark.name, Kind.name, Color.name, Color.id, CarsColor.image, CarsColor.id, Car.id'));
$this->CarsColor->recursive = -1;
$this->set('carsColors', $this->paginate());
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment