DataMapper ORM one to one relationships Search question

[eluser]Onema[/eluser]
I have been using the CI ORM, and I really like it, but I'm having a hard time trying to figure out how to do a search using a 1:1 relationship.

I have a site that works as a directory of people. therefore the information about users of the sites is split into 'user' information and 'contact' information. Only users that log in to the site can have a user and contact, but a contact doesn't have to have a user.

Code:

-- -----------------------------------------------------
    -- Table `contact`, I have omitted FK for simplicity
    -- -----------------------------------------------------
    DROP TABLE IF EXISTS `contact` ;
    
    CREATE  TABLE IF NOT EXISTS `contact` (
      `id` INT NOT NULL AUTO_INCREMENT ,
      `first_name` VARCHAR(255) NULL ,
      `last_name` VARCHAR(255) NULL ,
      `phone_number` VARCHAR(45) NULL ,
      `email_address` VARCHAR(255) NULL ,
      PRIMARY KEY (`id`) )
    ENGINE = InnoDB
    DEFAULT CHARACTER SET = utf8
    COLLATE = utf8_unicode_ci;
    
    
    -- -----------------------------------------------------
    -- Table `user`
    -- -----------------------------------------------------
    DROP TABLE IF EXISTS `user` ;
    
    CREATE  TABLE IF NOT EXISTS `user` (
      `id` INT NOT NULL AUTO_INCREMENT ,
      `username` VARCHAR(255) NOT NULL ,
      `password` VARCHAR(32) NULL ,
      `contact_id` INT NULL ,
      PRIMARY KEY (`id`) ,
      UNIQUE INDEX `username_UNIQUE` (`username` ASC) )
    ENGINE = InnoDB
    DEFAULT CHARACTER SET = utf8
    COLLATE = utf8_unicode_ci;

models look like this

Code:

class User extends DataMapper {    
        public $has_one = array(
            'Contact' => array(
               'class' => 'Contact'
              )
        );
    }

    class Contact extends DataMapper {    
        
    }

What i'm trying to do is to search based on a keyword on the username or first_name or last_name something like this:

    SELECT user.*, contact.*
    FROM user
     LEFT JOIN contact
        ON contact.id = user.contact_id
     WHERE username LIKE '%keyword%'
        OR first_name LIKE '%keyword%'
        OR last_name LIKE '%keyword%';

and this is how I'm trying to implement it, although it is wrong

Code:

$search_string = $search_string;
        
      $User = new User();
      $User->or_like('username', $search_string);
      $User->or_like_related('Contact', 'first_name', $search_string);
      $User->or_like_related('Contact', 'last_name', $search_string);
      
      $users = $User->get();

I keep getting this error message

A Database Error Occurred
Error Number: 1146</p><p>Table 'contact_user' doesn't exist</p><p>SELECT `user`.*
FROM (`user`)
LEFT OUTER JOIN `contact_user` contact_contact_user ON `user`.`id` = `contact_contact_user`.`_id`
LEFT OUTER JOIN `contact` contact_contact ON `contact_contact`.`id` = `contact_contact_user`.`_id`
WHERE `user`.`username` LIKE '%user%'
OR `contact_contact`.`first_name` LIKE '%user%'
OR `contact_contact`.`last_name` LIKE '%user%'</p>


That query looks very messed up and I don't know why
any ideas?
Thank you.

[eluser]WanWizard[/eluser]
The way you have defined it now, is that you have a foreign key for contact in user, and no link from contact to user. Which makes it a one-to-many (as a contact can be assigned to multiple users, even if you don't do that).

Then you have to define your relation both ways:

Code:

class User extends DataMapper {    
    public $has_one = array('contact');
}

class Contact extends DataMapper {    
    public $has_many = array('user');
}

And you don't have to assign the result of a get() to another variable. You can just iterate over $User.

[eluser]Onema[/eluser]
Thank you for your response.

I set both, has_many for Contact and had_one for user, I'm getting the following error message:

A Database Error Occurred
<p>Error Number: 1146</p><p>Table 'contact_user' doesn't exist</p><p>SELECT `user`.*
FROM (`user`)
LEFT OUTER JOIN `contact_user` contact_contact_user ON `user`.`id` = `contact_contact_user`.`_id`
LEFT OUTER JOIN `contact` contact_contact ON `contact_contact`.`id` = `contact_contact_user`.`_id`
WHERE `user`.`username` LIKE '%keyword%'
OR `contact_contact`.`first_name` LIKE '%keyword%'
OR `contact_contact`.`last_name` LIKE '%keyword%'

I don't understand why is joining against two tables.

Any chance I'm doing anything else wrong?

[eluser]WanWizard[/eluser]
If you have a relation, you either need FK's or a relationship table that has both id's.

Since you don't have the FK in User, DM will want to use relationship table (which doesn't exist).

[eluser]Onema[/eluser]
[quote author="WanWizard" date="1310834660"]If you have a relation, you either need FK's or a relationship table that has both id's.

Since you don't have the FK in User, DM will want to use relationship table (which doesn't exist).[/quote]
I thought that in the new version of the ORM you didn't need intermediate tables (I have to look up where I saw this tho), other wise there is no real 'one to one' or 'one to many relationships'.

I got over this issue by doing the following implementation of the user model:

Code:

class User extends DataMapper {
    public $Contact;
    public $table = USER_TABLE;

    public function __construct($id = null) {
      parent::__construct($id);
      $this->get_contact();
    }


    /*
     * METHOD TO CORRECTLY SET THE CONTACT
     */
    private function get_contact() {
      
      if(empty($this->Contact) ||
         $this->Contact->id != $this->contact_id) {
        if ($this->contact_id) {
          $this->Contact = new Contact($this->contact_id);
        }
        else {
          $this->Contact = new Contact();
        }
      }
      
      return $this->Contact;
    }


    /*
     * SAVE AND DELETE METHODS FOR USER
     */
    public function save() {  
      $this->Contact->save();
      $this->contact_id = $this->Contact->id;
      
      return parent::save();
    }
    
    
    public function delete () {
      parent::delete();

      /*
       * If you have the correct FK constraints
       * you can omit the following two lines
       */
      $this->get_contact();
      $this->Contact->delete();
    }

    /*
     * GET METHODS FOR CONTACT
     */
    public function get_first_name() {
      $this->get_contact();
      return $this->Contact->first_name;
    }

    public function get_last_name() {
      $this->get_contact();
      return $this->Contact->last_name;
    }
  }

In this way I can get the first name and last name by using the get methods. I know this is not the most efficient way to do things, and I wish the ORM would take care of the simpler relationships, I can only hope it will get even better than it already is

[eluser]WanWizard[/eluser]
You didn't read my reply.

For a direct link between tables, you need to define ITFK's. If Datamapper can't find those, it will assume you're working with a relationship table.

So add the required FK's to the table if you want to work without.

And you ALWAYS have to define the relationship both ways, because Datamapper will use both to make the most optimal query. If you only define one side of the relation, you're bound to run into problems like this...

[eluser]Onema[/eluser]
Pardon my ignorance, but I'm not sure what you refer to "ITFK".

here is the actual table that I'm using which has the FK and FK constraint set (In my first post I said I committed these for simplicity... But that is probably my mistake):

Code:

CREATE  TABLE IF NOT EXISTS `user` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `username` VARCHAR(255) NOT NULL ,
  `password` VARCHAR(32) NULL ,
  `contact_id` INT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `username_UNIQUE` (`username` ASC) ,
  INDEX `fk_user_contact` (`contact_id` ASC) ,
  CONSTRAINT `fk_user_contact`
    FOREIGN KEY (`contact_id` )
    REFERENCES `contact` (`id` )
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_unicode_ci;

With this fk and the following has_many and has_one:

Under User:

Code:

public $has_one = array(
            'Contact' => array()
           );

Under Contact:

Code:

public $has_many = array(
            'Users' => array()
           );

I keep getting these error message

Code:

A Database Error Occurred
Error Number: 1146
Table 'contact_user' doesn't exist

SELECT `user`.*
FROM (`user`)
LEFT OUTER JOIN `contact_user` contact_contact_user ON `user`.`id` = `contact_contact_user`.`_id`
LEFT OUTER JOIN `contact` contact_contact ON `contact_contact`.`id` = `contact_contact_user`.`_id`
WHERE `user`.`username` LIKE  '%keyword%'
OR `contact_contact`.`first_name` LIKE  '%keyword%'
OR `contact_contact`.`last_name` LIKE  '%keyword%'
ORDER BY `user`.`id`

Filename: /libraries/Datamapper.php

Line Number: 1406

I guess, I'm still not clear on how the $has_one and $has_many need to be set up. Help with this is appreciated.

[eluser]WanWizard[/eluser]
ITFK = in-table-foreign-key. As opposed to a FK in a relationship table.

I've found the time to properly look at this issue. Your problem is that you haven't followed the rules of Datamapper. One of them being that the models have a singular name, tables have a plural name. That means for the model 'User', the table must be called 'users', and for the model 'Contact', the table must be called 'contacts'.

Both is not the case here, so Datamapper is trying to construct a query without being able to retrieve table information.

This works like a charm here:

Code:

class User extends DataMapper {

    public $table = 'user'; // <== specify the table name if you use a non-standard one

    public $has_one = array('contact');

}

class Contact extends DataMapper {

    public $table = 'contact'; // <== specify the table name if you use a non-standard one

    public $has_many = array('user');
}

    function index()
    {
        $search_string = 'A';

        $User = new User();
        $User->or_like('username', $search_string);
        $User->or_like_related_contact('first_name', $search_string);
        $User->or_like_related_contact('last_name', $search_string);
        $users = $User->get();

        var_dump($users);
    }
}

[eluser]Onema[/eluser]
FANTASTIC! This is working well, I was setting the table name as you can see on post#4 (I do read the documentation before asking any questions, but unfortunately it is not always 100% clear for the more advanced options), but I was not using the or_like_related_contact method...

WanWizard, thank you very much.

[eluser]WanWizard[/eluser]
There should not be a functional difference between

Code:

or_like_related('contact', 'first_name', $search_string)

-and-

or_like_related_contact('first_name', $search_string)

Both should work fine. In my test it worked immediately as soon as I put both table names in the models...