Thread

How exactly do you call the related fields now?

$asd = new Dqip();
      $asd->where(‘id’,$dqip_id)->get();
      $asd->data_source->get();
      $asd->author->get();

once I call the author i get an error since the join table does not exist.

Error Number: 1054

Unknown column ‘join_dqips_users.dqip_author_id’ in ‘on clause’

SELECT `users`.* FROM (`users`) LEFT JOIN `join_dqips_users` as join_dqips_users ON `users`.`id` = `join_dqips_users`.`user_id` LEFT JOIN `dqips` as dqips ON `dqips`.`id` = `join_dqips_users`.`dqip_author_id` WHERE `dqips`.`id` = ‘1’

It should work.  I hate to say it, but it should.  The _get_related_table method (line 2693) looks through the $fields array on each object.  If (in your example) the field ‘author_id’ exists on the dquips table, it uses dquips for the join table.

The only reason that it should go to the old style join table is if ‘author’ is not set in the has_one array and ‘author_id’ is not a field in the table.

It should work.  I hate to say it, but it should.  The _get_related_table method (line 2693) looks through the $fields array on each object.  If (in your example) the field ‘author_id’ exists on the dquips table, it uses dquips for the join table.

The only reason that it should go to the old style join table is if ‘author’ is not set in the has_one array and ‘author_id’ is not a field in the table.

Allright ill take a look at it when I get a chance…

Kinda odd I havnt changed anything besides the objects for the users…

Hi,

first of all I’m really impressed with the lib. At least something like django’s DB interface in PHP 😊

I have a problem with using where_related. I have Site table with urls and Tag with tags for sites. This is a many-to-many relation and looks like this:

class Site extends DataMapper {
    var $table = 'sites';    
    var $has_many = array('tag');
    
    function Site()
    {
        parent::DataMapper();
    }            
}
[...]

class Tag extends DataMapper {
    var $table = 'tags';    
    var $has_many = array('site');    

    function Tag()
    {
        parent::DataMapper();
    }                                  
}

Now I want to fetch all sites tagged with ‘PHP’ and ‘CodeIgniter’, so I do sth like this:

$site = new Site();
  $tag_1 = new Tag();
  $tag_1->get_by_tag('PHP');

  $tag_2 = new Tag();
  $tag_2->get_by_tag('CodeIgniter');
        
  $site->limit(30)->where_related($t1)->where_related($t2)->get();

But I don’t get any results as the SQL WHERE section of the query generated by DataMapper will always return false:

SELECT `sites`.*
  FROM (`sites`)
    LEFT JOIN `sites_tags` ON `sites`.`id` = `sites_tags`.`site_id`
    LEFT JOIN `tags` ON `tags`.`id` = `sites_tags`.`tag_id`
      WHERE `tags`.`id` = '42' AND `tags`.`id` = '64'

Is there any way to do this in DataMapper or do I have to create the query manually?

thanks for help,
miau

I did some research.  The kind of query you are wanting is an intersection, which is fairly advanced for an ORM.  However, I think it is pretty cool, too, so I’m thinking I might add it to my version of DataMapper.

Until this becomes part of the core, you can try hand doing it like this:

// NOT tested:
$tags = new Tag();
$tags->where_in("tag", array("PHP", "CodeIgniter")->get();
$sql = "tags.id IN (";
$first = TRUE;
foreach($tags->all as $tag)
{
    if($first)
    {
        $first = FALSE;
    }
    else
    {
        $sql .= "\nINTERSECT\n";
    }
    $sql .= 'SELECT sites_tags.tag_id FROM sites_tags WHERE sites_tags.site_id = ' . $tag->id;
}
$site->where($sql);

If you do this a lot, you can easily make it a method.

Update:
You poor MySQL users.  I had no idea.  I just discovered that MySQL doesn’t support intersects.  Given that, there is no way, that I can see, that someone using a MySQL database can get the results requested above, without resorting to multiple queries.

Hi!

Well, I’ve tried to read all of the post just to find an answer… but there are 56 pages!!. I couldn’t find the answer, so here it goes:

I’m making a Bug Tracker module for a CI app… I’m using DataMapper. I have my “bugs” table and my “priorities” table. The idea is that a bug can be updated whenever I want, just changing the priority, but, of course, keeping track of the priorities it had all along. There is, of course, the bugs_priorities table. there’s an id, bug_id and priority_id…. but I need to add a date field, so I can know when happened that update….

bugs
- id
- name
- details

priorities
- id
- name (i.e: Normal, Critical, Trivial…).

bugs_priorities
- id
- bug_id
- priority_id
- date <- Here is my problem

I need to know, in example, the priorities that a bug had between two dates…

How can I do that? is there another way?

Thanks…

PD: Sorry about my english!

@DavidZB
There is no way to get information from the join tables in DataMapper, currently.  You need to use a third Object to store the information, like this:

bugs, priorities stay the same.

prioritydates
fields: id, date
has_one: priority, bug

bugs_prioritydates
id, bug_id, prioritydate_id

priorities_prioritydates
id, priority_id, prioritydate_id

$bug = ...
$pds = $bug->prioritydate->where("date >=", $date1)->where("date <=", $date2)->get();
foreach($pds->all as $pd) {
    $date = $pd->date;
    $priority = $pd->priority->get();
}

Now, in the current implementation, you have to use the extra join tables, which is rather ugly, and makes certain complex queries difficult.  I have been working on a version that (among other things) allows has_ones to use traditional, in-table fields.  You can try it out a few pages back, it is almost a drop-in replacement for the official version.  If you use that, then you can drop the join tables, and just make your prioritydate table:

id, date, priority_id, bug_id

And with the testing version, you can run a query like this:

$bug = ...
$pds = new PriorityDate();
$pds->where_related($bug)
$pds->join_related('priority', array("id","name",etc…));
$pds->get();
foreach($pds->all as $pd) {
    echo("On {$pd->date}, the priority was {$pd->priority_name}");
}

The testing code is definitely experimental, however, so you might not want to use it in a production system yet.

class Creator extends DataMapper
{
  var $table = 'users';

  var $has_many = array('file');
  
  // ...
}

class User extends DataMapper
{
  var $table = 'users';

  var $has_many = array('file');

  // ...
}

class File extends DataMapper
{
  var $table = 'files';

  var $has_one = array('creator');
  var $has_many = array('user');

  // ...
}

And join tables would become join_files_users and join_files_creators.

I am just now implementing this and it is not working for me :(

I get the following error:

Error Number: 1054

Unknown column 'creator_id' in 'where clause'

SELECT * FROM (`join_files_users`) WHERE `file_id` = 9 AND `creator_id` = '2'

Why doesn’t it use ‘join_files_creators’?

This is my query:

$c = new Creator();
$c->get_by_id($user_id);
    
$f = new File();
$f->name = $this->input->post('file_name');
...
$f->save($c);

Check your mysql driver

In the manual go to troubleshooting and should be the first thin there

I already did… that’s not it :(

I figured it out lol

Because the table in the Creator model is set to users so it will join a users table not the creator table…

Change $table = “creator”; and it should join the right table that you want.

Actually there is no “creators” table. The situation is as follows:

There’s a “users” table and a “files” table. When a new file is added via the admin panel both the “creator” (which is a user) and the relation of the file to the users (managing rights) is stored.

So I need to save a relation to:
1. the user who created the file
2. the users who are eligible to view the file

ok then… creator then has to extend users instead of datamapper

Check this page it might solve your problem

http://stensi.com/datamapper/pages/relationtypes.html

This is a self referencing type issue.

I read that, too. Tried exactly what was explained, but just can’t get it to work :( Can anyone help with a code example?

EDIT: I am also not so sure if this is a self-referencing issue. Since I really want to reference Users <> Files and Users (as Creator) <> Files. I just can’t use the join_files_users table twice…

It’s not that a User has_one or has_many Creators. A file has both a relation to users and a creator.

I’m lost :(

Maybe the only solution to this is adding another CREATORS table?

USERS
id
...

FILES
id
...

CREATORS
id

join_files_users
join_creators_files
join_creators_users

It’s kind of redundant since creators are in fact users, but I have no clue how to solve it otherwise…