CodeIgniter DataMapper ORM v1.8.0 - How to Get records from multiple tables

Question

I am new to this and need a little help. I have a 2 tables...

CREATE TABLE `vehicles` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `vehicle_type` varchar(50) NOT NULL,
  `vehicle_make` varchar(50) NOT NULL,
  `vehicle_model` varchar(50) NOT NULL,
  `vehicle_year` varchar(50) NOT NULL,
  `vin` varchar(50) NOT NULL,
  `registered_state` varchar(10) NOT NULL,
  `license_plate` varchar(20) NOT NULL,
  `insurrance_policy` varchar(50) NOT NULL,
  PRIMARY KEY(`id`)
)
ENGINE=INNODB;

CREATE TABLE `drivers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(50) NOT NULL,
  `last_name` varchar(50) NOT NULL,
  `dob` date NOT NULL,
  `ss_no` varchar(50) NOT NULL,
  `address` varchar(100) NOT NULL,
  `city` varchar(50) NOT NULL,
  `state` varchar(10) NOT NULL,
  `zip_code` int(5) NOT NULL,
  `cell_phone` varchar(50) NOT NULL,
  `home_phone` varchar(50),
  `dl_no` varchar(50) NOT NULL,
  `dl_state` varchar(10) NOT NULL,
  `dl_exp` date NOT NULL,
  `dl_2_no` varchar(50) NOT NULL,
  `dl_2_state` varchar(10) NOT NULL,
  `dl_2_exp` date NOT NULL,
  `vehicle_id` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY(`id`),
  CONSTRAINT `Ref_01` FOREIGN KEY (`vehicle_id`)
    REFERENCES `vehicles`(`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
)
ENGINE=INNODB;

SET FOREIGN_KEY_CHECKS=1;

As you can see every driver has a car associated with him. How can I query every driver and every car that is associated with him in an object.

I can get all the drivers using this.

$d = new Driver();
$data['driver'] = $d->get();

In my model for driver has

var $has_one = array('vehicle');

I want to get all the records in $data['driver']

Wesley Murch · Accepted Answer · 2011-05-01T23:04:41.897

Here's one simple way, assuming you have one-to-one relationship:

In your Model Driver:

var $has_one = array('vehicle');

In your Model Vehicle:

var $has_one = array('driver');

To get a vehicle and its driver:

$v = new Vehicle();
$v->include_related('driver')->get();

include_related() will only work with $has_one related models.

Driver's properties are now stored in $v with the prefix driver_. To access the vehicle's driver columns:

echo $v->driver_first_name;
echo $v->driver_last_name;

Alternatively, you can auto-load the driver every time you access the vehicle:

// In Vehicle
var $auto_populate_has_one = TRUE;

For has-many relationships, you can do this:

$d = new Driver();
$d->get_by_id($id);// Get a driver by id
foreach ($d->vehicle->get() as $car)
{
    // Print all this Driver's vehicles
    echo $car->vehicle_model;
}

This is just one way to do it. There are so may ways to access relationships in Datamapper, your best bet is to read the documentation thoroughly, then read it again. Datamapper is a great ORM and you should learn and experiment with all it's features to get the most out of it.

score 0 · Answer 2 · answered May 01 '11 at 18:52

This could be a good opportunity to use a Foreign Key in your Drivers Table. Here is a link to the MySQL documentation on using Foreign Keys in your table: MySQL Foreign Keys

Alternatively if you don't want to deal with the hassles of advanced MySQL calls you could also just process two queries, one to your vehicles table, and one to your drivers table, and then use PHP to iterate (loop) through the results until you can find and match the correct values with each other.

Actually both ways are probably a bit of a hassle to set up, but your foreign key would likely be the easier to maintain.

CodeIgniter DataMapper ORM v1.8.0 - How to Get records from multiple tables

2 Answers2