DataMapper ORM v1.8.2

[eluser]Unknown[/eluser]
Many-to-Many Reciprocal Self Relationships - not working?

I've followed the steps outlined on this page http://datamapper.wanwizard.eu/pages/adv...tions.html and maybe I'm missing something but I can't seem to get it to work.

Basically I want to create a comments page where the data is stored in 1 table. Any comments that have parent_id of zero is assumed to be the top level comment and all replies will have a parent_id of the top level comment.

My model

Code:

class Comment extends DataMapper {

var $has_many = array(
  
  'relatedcomment' => array(
   'class' => 'comment',
   'other_field' => 'comment'
  ),
  'comment' => array(
   'other_field' => 'relatedcomment',
   'join_other_as' => 'parent'
  )
  
);
}

My join table

Code:

CREATE TABLE IF NOT EXISTS `join_comments_comments` (
  `id` bigint(20) NOT NULL auto_increment,
  `comment_id` bigint(20) NOT NULL,
  `relatedcomment_id` bigint(20) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=13 ;

My controller

Code:

function wall($id)
{
  $course = new Course($id);
  $course->comment->get();
}

When I try to execute the code, I get a blank page, as if the page is stuck in an endless loop.

Any help would be great. Thanks.

[eluser]WanWizard[/eluser]
I think you can, but you'll have to use a subquery which is going to make it complex.

It would be handy if DataMapper would support WHERE EXIST, but it doesn't (and I'm not sure all RDBMS's support that, so many not a good idea either).

Alternatively, you could add a custom method to your model, write a standard query for it, run that using $this->db->query, make sure it returns at least the category id column, and convert the result back into the object by calling

Code:

$result = $this->db->query('your sql here');
$result and $this->_process_query($result);

[eluser]diZzyCoDeR[/eluser]
Guys, I have this problem that's beyond my level of understanding. So I will attempt to explain and then see if there's an obvious solution.

I have tables in a separate DB Server - MSSQL (ya, ya, I know) with 'ID' (BIGINT)
My server is a 32-Bit Windows 2008 server w/ PHP 5.3.8
The 'id's in one of my tables get above 2147483647 .. and when the do, they simply get returned as 2147483647 (the MAX_INT_VAL of my 32-bit PHP installation)

From what I understand, PHP is supposed to automatically cast to FLOAT when an integer value gets larger than it's supported maximum.

What I was thinking of doing, because I use a lot SQL VIEWS, is just modifying the SQL view for my table with this:

Code:

SELECT CAST(ID AS varchar(50)) AS id

UNLESS, that is, there is something else that is more proper (other than moving to a 64bit platform.. lol) like forcing the ID's to (float) in my model somehow.

*confuzed*

[eluser]WanWizard[/eluser]
You'll have to do something about your platform.

Even if this is going to work, DataMapper will not accept it. It needs the 'id' to be an integer, it uses casting (to int) or intval() at several places in the code.

[eluser]diZzyCoDeR[/eluser]
balls.

Thanks WanWizard. Appreciate it.

[eluser]Spir[/eluser]
How would you sort by a join field value? Is that possible?

Code:

$item->language->include_join_fields()->where('id', 1)->order_by('join_name', 'asc')->get();

doesn't seems to work. Any idea? Should I loop over my result to sort or could I sort using order_by method?

[eluser]quasiperfect[/eluser]
i have user and comment models

Code:

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

var $has_many = array(
     'sent_comment' => array(
            'class' => 'comment',
            'other_field' => 'sender'
        ),
        'recived_comment' => array(
            'class' => 'comment',
            'other_field' => 'reciver'
        )
    );

    function __construct($id = NULL)
    {
parent::__construct($id);
    }
}

class Comment extends DataMapper {
    var $table = 'comments';
    var $has_one = array(
        'sender' => array(
            'class' => 'user',
            'other_field' => 'sent_comment'
        ),
        'reciver' => array(
            'class' => 'user',
            'other_field' => 'recived_comment'
        )
    );
    var $has_many = array(
        'relatedcomment' => array(
            'class' => 'comment',
            'other_field' => 'comment'
        ),
        'comment' => array(
            'other_field' => 'relatedcomment'
        )
    );

    function __construct($id = NULL)
    {
parent::__construct($id);
    }
}

the tables

Code:

CREATE TABLE `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(15) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`),
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `comments` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `sender_id` int(10) unsigned NOT NULL,
  `reciver_id` int(10) unsigned NOT NULL,
  `text` text NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `comments_relatedcomments` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `comment_id` int(10) unsigned DEFAULT NULL,
  `relatedcomment_id` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `comments_users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `sender_id` int(10) unsigned DEFAULT NULL,
  `sent_comment_id` int(10) unsigned DEFAULT NULL,
  `reciver_id` int(10) unsigned DEFAULT NULL,
  `recived_comment_id` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

i can add users without any problems

Code:

$user_a = new User();
$user_a->username = "user_a";
$user_a->save();

$user_b = new User();
$user_b->username = "user_b";
$user_b->save();

how can i add a comment from user_a to user_b and save ?
how can i add a comment from user_b to user_a as a replay to the previous comment and save ?

edit : if anyone looked at the unedited version i corrected some errors so please look again

[eluser]WanWizard[/eluser]
[quote author="Spir" date="1327575356"]How would you sort by a join field value? Is that possible?[/quote]
Looking at the code, the DM version of order_by() always prefixes the field with the current table name, unless it is already prefixed.

So ordering on a field in a join table should be possible if you prefix it with the alias of the join table.

You could work around it by replacing the order_by() method by

Code:

public function order_by($orderby, $direction = '', $escape = TRUE)
{
$escape and $orderby = $this->add_table_name($orderby);
$this->db->order_by($orderby, $direction);

// For method chaining
return $this;
}

And then pass FALSE as third parameter if you don't want to prefix the field with the table name.

If this works for you, let me know and I'll add it to the next release.

[eluser]Spir[/eluser]
Many thanks for your great help WanWizard. It works like a charm. I actually ended working the other way around:

Code:

$item = $language->item->include_join_fields()->order_by('items_languages.name', 'asc', FALSE)->get();

Awesome

Also we could have something like this (maybe not as good in term of perf):

Code:

public function order_by($orderby, $direction = '')
{
  if (count(explode('.',$orderby)==0))
     $escape and $orderby = $this->add_table_name($orderby);
  $this->db->order_by($orderby, $direction);

  // For method chaining
  return $this;
}

[eluser]WanWizard[/eluser]
Better then the extra parameter.

In the end I did this:

Code:

strpos($orderby, '.') === FALSE AND $orderby = $this->add_table_name($orderby);