EllisLab text mark
Advanced Search
     
DataMapper ORM one to one relationships Search question
Posted: 15 July 2011 05:47 PM
Joined: 2011-07-15
16 posts

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.

-- -----------------------------------------------------
    -- 
Table `contact`, I have omitted FK for simplicity
    
-- -----------------------------------------------------
    
DROP TABLE IF EXISTS `contact` ;
    
    
CREATE  TABLE IF NOT EXISTS `contact` (
      `
idINT NOT NULL AUTO_INCREMENT ,
      `
first_nameVARCHAR(255NULL ,
      `
last_nameVARCHAR(255NULL ,
      `
phone_numberVARCHAR(45NULL ,
      `
email_addressVARCHAR(255NULL ,
      
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` (
      `
idINT NOT NULL AUTO_INCREMENT ,
      `
usernameVARCHAR(255NOT NULL ,
      `
passwordVARCHAR(32NULL ,
      `
contact_idINT NULL ,
      
PRIMARY KEY (`id`) ,
      
UNIQUE INDEX `username_UNIQUE` (`usernameASC) )
    
ENGINE InnoDB
    
DEFAULT CHARACTER SET utf8
    COLLATE 
utf8_unicode_ci

models look like this

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

$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
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 ‘%user%’
OR `contact_contact`.`first_name` LIKE ‘%user%’
OR `contact_contact`.`last_name` LIKE ‘%user%’ 


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

 
Posted: 15 July 2011 06:49 PM   [ # 1 ]   [ Rating: 0 ]
Avatar
Joined: 2008-11-04
4489 posts

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:

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.

 Signature 

Me: WanWizard.eu | My company: Exite | Datamapper: DataMapper ORM <= LOOKING FOR A NEW MAINTAINER!

 
Posted: 16 July 2011 01:38 AM   [ # 2 ]   [ Rating: 0 ]
Joined: 2011-07-15
16 posts

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
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%’

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

Any chance I’m doing anything else wrong?

 
Posted: 16 July 2011 08:44 AM   [ # 3 ]   [ Rating: 0 ]
Avatar
Joined: 2008-11-04
4489 posts

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).

 Signature 

Me: WanWizard.eu | My company: Exite | Datamapper: DataMapper ORM <= LOOKING FOR A NEW MAINTAINER!

 
Posted: 21 July 2011 07:59 PM   [ # 4 ]   [ Rating: 0 ]
Joined: 2011-07-15
16 posts
WanWizard - 16 July 2011 12:44 PM

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).

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:

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 smile

 
Posted: 22 July 2011 05:45 PM   [ # 5 ]   [ Rating: 0 ]
Avatar
Joined: 2008-11-04
4489 posts

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…

 Signature 

Me: WanWizard.eu | My company: Exite | Datamapper: DataMapper ORM <= LOOKING FOR A NEW MAINTAINER!

 
Posted: 23 July 2011 02:36 PM   [ # 6 ]   [ Rating: 0 ]
Joined: 2011-07-15
16 posts

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):

CREATE  TABLE IF NOT EXISTS `user` (
  `
idINT NOT NULL AUTO_INCREMENT ,
  `
usernameVARCHAR(255NOT NULL ,
  `
passwordVARCHAR(32NULL ,
  `
contact_idINT NULL ,
  
PRIMARY KEY (`id`) ,
  
UNIQUE INDEX `username_UNIQUE` (`usernameASC) ,
  
INDEX `fk_user_contact` (`contact_idASC) ,
  
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:

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

Under Contact:

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

I keep getting these error message

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.

 
Posted: 23 July 2011 04:17 PM   [ # 7 ]   [ Rating: 0 ]
Avatar
Joined: 2008-11-04
4489 posts

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:

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);
    
}
 Signature 

Me: WanWizard.eu | My company: Exite | Datamapper: DataMapper ORM <= LOOKING FOR A NEW MAINTAINER!

 
Posted: 23 July 2011 08:41 PM   [ # 8 ]   [ Rating: 0 ]
Joined: 2011-07-15
16 posts

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.

 
Posted: 24 July 2011 06:16 AM   [ # 9 ]   [ Rating: 0 ]
Avatar
Joined: 2008-11-04
4489 posts

There should not be a functional difference between

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…

 Signature 

Me: WanWizard.eu | My company: Exite | Datamapper: DataMapper ORM <= LOOKING FOR A NEW MAINTAINER!