I’m back working on this having had to take some time away from it due to commitments to another project.
As it stands right now, I have the race venue, date/time and runner name in the CGridView grid. Now what I need to do is add in a cartesian product from the many-to-many relationship to associated a runner to one or more sponsorships for a given race.
Here is a SQL file, inclusive of dummy data…
-- --------------------------------------------------------
-- Host: 127.0.0.1
-- Server version: 5.5.27 - MySQL Community Server (GPL)
-- Server OS: Win32
-- HeidiSQL Version: 8.3.0.4694
-- --------------------------------------------------------
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
-- Dumping database structure for sponsorship
CREATE DATABASE IF NOT EXISTS `sponsorship` /*!40100 DEFAULT CHARACTER SET latin1 */;
USE `sponsorship`;
-- Dumping structure for table sponsorship.race
CREATE TABLE IF NOT EXISTS `race` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`venue_fk_nn` smallint(6) NOT NULL,
`race_date_time_nn` int(11) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `venue-race_date_index_unique` (`venue_fk_nn`,`race_date_time_nn`),
CONSTRAINT `race_venue_fk_nn` FOREIGN KEY (`venue_fk_nn`) REFERENCES `venue` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1;
-- Dumping data for table sponsorship.race: ~9 rows (approximately)
/*!40000 ALTER TABLE `race` DISABLE KEYS */;
INSERT INTO `race` (`id`, `venue_fk_nn`, `race_date_time_nn`) VALUES
(1, 1, 1404482400),
(3, 1, 1404485400),
(2, 1, 1404494700),
(4, 2, 1404482400),
(6, 2, 1404485400),
(5, 2, 1404494700),
(7, 3, 1404482400),
(9, 3, 1404485400),
(8, 3, 1404494700);
/*!40000 ALTER TABLE `race` ENABLE KEYS */;
-- Dumping structure for table sponsorship.runner
CREATE TABLE IF NOT EXISTS `runner` (
`id` smallint(2) NOT NULL AUTO_INCREMENT,
`runner_name_nn` varchar(40) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `runner_name_nn` (`runner_name_nn`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;
-- Dumping data for table sponsorship.runner: ~10 rows (approximately)
/*!40000 ALTER TABLE `runner` DISABLE KEYS */;
INSERT INTO `runner` (`id`, `runner_name_nn`) VALUES
(1, 'Runner1'),
(10, 'Runner10'),
(2, 'Runner2'),
(3, 'Runner3'),
(4, 'Runner4'),
(5, 'Runner5'),
(6, 'Runner6'),
(7, 'Runner7'),
(8, 'Runner8'),
(9, 'Runner9');
/*!40000 ALTER TABLE `runner` ENABLE KEYS */;
-- Dumping structure for table sponsorship.sponsor
CREATE TABLE IF NOT EXISTS `sponsor` (
`id` smallint(2) NOT NULL AUTO_INCREMENT,
`sponsor_name_un_nn` varchar(40) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `sponsor_name_un_nn` (`sponsor_name_un_nn`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;
-- Dumping data for table sponsorship.sponsor: ~8 rows (approximately)
/*!40000 ALTER TABLE `sponsor` DISABLE KEYS */;
INSERT INTO `sponsor` (`id`, `sponsor_name_un_nn`) VALUES
(1, 'SponsoringCompany1'),
(2, 'SponsoringCompany2'),
(3, 'SponsoringCompany3'),
(4, 'SponsoringCompany4'),
(5, 'SponsoringCompany5'),
(6, 'SponsoringCompany6'),
(7, 'SponsoringCompany7'),
(8, 'SponsoringCompany8');
/*!40000 ALTER TABLE `sponsor` ENABLE KEYS */;
-- Dumping structure for table sponsorship.sponsorship
CREATE TABLE IF NOT EXISTS `sponsorship` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`runner_sponsorship_jnc_fk_nn` int(11) NOT NULL,
`sponsor_fk_nn` smallint(6) NOT NULL,
`amount` float(7,3) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `sponsorship_sponsor_fk_nn` (`sponsor_fk_nn`),
CONSTRAINT `sponsorship_sponsor_fk_nn` FOREIGN KEY (`sponsor_fk_nn`) REFERENCES `sponsor` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=latin1;
-- Dumping data for table sponsorship.sponsorship: ~30 rows (approximately)
/*!40000 ALTER TABLE `sponsorship` DISABLE KEYS */;
INSERT INTO `sponsorship` (`id`, `runner_sponsorship_jnc_fk_nn`, `sponsor_fk_nn`, `amount`) VALUES
(1, 1, 1, 5.000),
(2, 2, 2, 5.000),
(3, 3, 3, 5.600),
(4, 4, 4, 8.000),
(5, 5, 5, 6.900),
(6, 6, 6, 6.400),
(7, 7, 7, 5.000),
(8, 8, 8, 10.000),
(9, 9, 1, 2.000),
(10, 10, 2, 3.000),
(11, 11, 3, 71.000),
(12, 12, 4, 56.200),
(13, 13, 5, 23.200),
(14, 14, 6, 51.200),
(15, 15, 7, 7.200),
(16, 16, 8, 2.300),
(17, 17, 1, 6.200),
(18, 18, 2, 4.700),
(19, 19, 3, 6.500),
(20, 20, 4, 4.400),
(21, 21, 5, 3.600),
(22, 22, 6, 4.500),
(23, 23, 7, 45.300),
(24, 24, 8, 63.200),
(25, 25, 1, 14.500),
(26, 26, 2, 2.300),
(27, 27, 3, 1.900),
(28, 28, 4, 20.000),
(29, 29, 5, 25.000),
(30, 30, 6, 36.300);
/*!40000 ALTER TABLE `sponsorship` ENABLE KEYS */;
-- Dumping structure for table sponsorship.tbl_migration
CREATE TABLE IF NOT EXISTS `tbl_migration` (
`version` varchar(255) NOT NULL,
`apply_time` int(11) DEFAULT NULL,
PRIMARY KEY (`version`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- Dumping data for table sponsorship.tbl_migration: ~8 rows (approximately)
/*!40000 ALTER TABLE `tbl_migration` DISABLE KEYS */;
INSERT INTO `tbl_migration` (`version`, `apply_time`) VALUES
('m000000_000000_base', 1404517233),
('m130207_231295_create_venue_table', 1404517234),
('m130207_231296_create_race_table', 1404517238),
('m130207_231300_create_runner_table', 1404517241),
('m130207_231310_create_x_race_runner_table', 1404593228),
('m130207_231315_create_sponsor_table', 1404593233),
('m130207_231370_create_sponsorship_table', 1404593233),
('m130207_231375_create_x_race_runner_sponsorship_table', 1404593234);
/*!40000 ALTER TABLE `tbl_migration` ENABLE KEYS */;
-- Dumping structure for table sponsorship.venue
CREATE TABLE IF NOT EXISTS `venue` (
`id` smallint(2) NOT NULL AUTO_INCREMENT,
`venue_name_un_nn` varchar(40) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `venue_name_un_nn` (`venue_name_un_nn`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
-- Dumping data for table sponsorship.venue: ~3 rows (approximately)
/*!40000 ALTER TABLE `venue` DISABLE KEYS */;
INSERT INTO `venue` (`id`, `venue_name_un_nn`) VALUES
(1, 'RaceVenue1'),
(2, 'RaceVenue2'),
(3, 'RaceVenue3');
/*!40000 ALTER TABLE `venue` ENABLE KEYS */;
-- Dumping structure for table sponsorship.x_race_runner
CREATE TABLE IF NOT EXISTS `x_race_runner` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`race_fk_nn` int(11) NOT NULL,
`runner_fk_nn` smallint(6) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `x_race_runner_x_race_runner_race-runner_unique` (`race_fk_nn`,`runner_fk_nn`),
KEY `x_race_runner_x_race_runner_runner_fk_nn` (`runner_fk_nn`),
CONSTRAINT `x_race_runner_x_race_runner_runner_fk_nn` FOREIGN KEY (`runner_fk_nn`) REFERENCES `runner` (`id`) ON DELETE CASCADE,
CONSTRAINT `x_race_runner_race_fk_nn` FOREIGN KEY (`race_fk_nn`) REFERENCES `race` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=latin1;
-- Dumping data for table sponsorship.x_race_runner: ~30 rows (approximately)
/*!40000 ALTER TABLE `x_race_runner` DISABLE KEYS */;
INSERT INTO `x_race_runner` (`id`, `race_fk_nn`, `runner_fk_nn`) VALUES
(1, 1, 1),
(3, 1, 3),
(4, 1, 4),
(6, 2, 6),
(7, 2, 7),
(9, 2, 9),
(10, 2, 10),
(11, 3, 1),
(12, 3, 2),
(13, 3, 3),
(14, 3, 4),
(15, 3, 5),
(16, 3, 6),
(21, 4, 1),
(20, 4, 10),
(22, 5, 2),
(23, 5, 3),
(26, 5, 6),
(17, 5, 7),
(18, 5, <img src='http://www.yiiframework.com/forum/public/style_emoticons/default/cool.gif' class='bbc_emoticon' alt='8)' />,
(19, 5, 9),
(27, 6, 7),
(28, 6, <img src='http://www.yiiframework.com/forum/public/style_emoticons/default/cool.gif' class='bbc_emoticon' alt='8)' />,
(29, 6, 9),
(30, 6, 10),
(2, 7, 2),
(5, 7, 5),
(8, 7, <img src='http://www.yiiframework.com/forum/public/style_emoticons/default/cool.gif' class='bbc_emoticon' alt='8)' />,
(24, 8, 4),
(25, 8, 5);
/*!40000 ALTER TABLE `x_race_runner` ENABLE KEYS */;
-- Dumping structure for table sponsorship.x_race_runner_sponsorship
CREATE TABLE IF NOT EXISTS `x_race_runner_sponsorship` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`x_race_runner_fk_nn` int(11) NOT NULL,
`sponsorship_fk_nn` int(11) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `x_race_runner_sponsorship_runner-sponsor_unique` (`x_race_runner_fk_nn`,`sponsorship_fk_nn`),
KEY `x_race_runner_sponsorship_sponsorship_fn_nn` (`sponsorship_fk_nn`),
CONSTRAINT `x_race_runner_sponsorship_sponsorship_fn_nn` FOREIGN KEY (`sponsorship_fk_nn`) REFERENCES `sponsorship` (`id`) ON DELETE CASCADE,
CONSTRAINT `x_race_runner_sponsorship_race_runner_fk_nn` FOREIGN KEY (`x_race_runner_fk_nn`) REFERENCES `x_race_runner` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=latin1;
-- Dumping data for table sponsorship.x_race_runner_sponsorship: ~30 rows (approximately)
/*!40000 ALTER TABLE `x_race_runner_sponsorship` DISABLE KEYS */;
INSERT INTO `x_race_runner_sponsorship` (`id`, `x_race_runner_fk_nn`, `sponsorship_fk_nn`) VALUES
(1, 1, 1),
(2, 2, 2),
(3, 3, 3),
(4, 4, 4),
(5, 5, 5),
(6, 6, 6),
(7, 7, 7),
(8, 8, <img src='http://www.yiiframework.com/forum/public/style_emoticons/default/cool.gif' class='bbc_emoticon' alt='8)' />,
(9, 9, 1),
(10, 10, 2),
(11, 11, 3),
(12, 12, 4),
(13, 13, 5),
(14, 14, 6),
(15, 15, 7),
(16, 16, <img src='http://www.yiiframework.com/forum/public/style_emoticons/default/cool.gif' class='bbc_emoticon' alt='8)' />,
(17, 17, 1),
(18, 18, 2),
(19, 19, 3),
(20, 20, 4),
(21, 21, 5),
(22, 22, 6),
(23, 23, 7),
(24, 24, <img src='http://www.yiiframework.com/forum/public/style_emoticons/default/cool.gif' class='bbc_emoticon' alt='8)' />,
(25, 25, 1),
(26, 26, 2),
(27, 27, 3),
(28, 28, 4),
(29, 29, 5),
(30, 30, 6);
/*!40000 ALTER TABLE `x_race_runner_sponsorship` ENABLE KEYS */;
/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
In the XRaceRunner model, I currently have…
public function relations() {
return array(
'sponsorships' => array(self::HAS_MANY, 'XRaceRunnerSponsorship', 'x_race_runner_fk_nn'),
'runner' => array(self::BELONGS_TO, 'Runner', 'runner_fk_nn'),
'race' => array(self::BELONGS_TO, 'Race', 'race_fk_nn'),
);
}
public function search() {
$criteria = new CDbCriteria;
$criteria->compare('id', $this->id);
$criteria->compare('race_fk_nn', $this->race_fk_nn);
$criteria->compare('runner_fk_nn', $this->runner_fk_nn);
$criteria->with = array('race', 'race.venue', 'runner');
$criteria->together = true;
return new CActiveDataProvider($this, array(
'criteria' => $criteria,
));
}
In the XRaceRunnerSponsorship model…
public function relations() {
return array(
'sponsorship' => array(self::BELONGS_TO, 'Sponsorship', 'sponsorship_fk_nn'),
'xRaceRunnerFkNn' => array(self::BELONGS_TO, 'XRaceRunner', 'x_race_runner_fk_nn'),
);
}
And in the Sponsorship model…
public function relations() {
return array(
'sponsorFkNn' => array(self::BELONGS_TO, 'Sponsor', 'sponsor_fk_nn'),
'xRaceRunnerSponsorships' => array(self::HAS_MANY, 'XRaceRunnerSponsorship', 'sponsorship_fk_nn'),
);
}
As mentioned in earlier posts, sorting/filtering is a requirement for the CGridView. However, no data needs to be added or changed in the frontend, so I therefore only need an admin view that will give a grid that can be sorted/filtered, and that is all (the data will actually be fed in via the backend).
I’ve tried to many different ways of creating the correct criteria for the CActiveDataProvider but I just cant seem to get it to work. I am checking the SQL queries created by using CWebLogRoute in the log component and sometimes it actually creates a query that would provide the required data (the Cartesian product) but the data from this is not what is displayed in the CGridView and it’s driving me crazy!! 
I would be so appreciative is someone could help me here. I would have assumed there would be a simple way to display a Cartesian product from a many-to-many relationship but I have dug through all the threads and examples I can find and I still cant seem to be able to solve it.
I would be ever so grateful if someone could help me 
Thanks in advance,
U4EA
), I decided to give your code a try before I signed off for the day and I seem to have it working now.
and I fully agree with your statements about it. It is my preferred extension.