Thread

I’ve always seemed to have problems with many to many relationships. Say I have “Table1” and “Table2”. They have a many to many relationship. After the join table wasn’t updated, I deleted the join table to see what error I got.

Table ‘table1_table2’ doesn’t exist

SELECT * FROM (`table1_table2`) WHERE `table1_id` = 1 AND `table2_id` = ‘2’

Why in the &$#@ would it be trying to perform a SELECT statement?!?!?!?

I’ve always seemed to have problems with many to many relationships. Say I have “Table1” and “Table2”. They have a many to many relationship. After the join table wasn’t updated, I deleted the join table to see what error I got.

Table ‘table1_table2’ doesn’t exist

SELECT * FROM (`table1_table2`) WHERE `table1_id` = 1 AND `table2_id` = ‘2’

Why in the &$#@ would it be trying to perform a SELECT statement?!?!?!?

It selects from the table all the time… To do checks to see if things exist etc…

Turn on the profiler And you can see this
add this to the config file:
[code[
$config[‘enable_profiler’]= TRUE;</code></pre>

Otherwise your problem is with the table not being there… (but you caused that one).

Just a few thing to get out of the way.

1. Did you update your mysql driver file (check the troubleshoot page on the manual)
2. What are your config settings

Yeah I have the right driver. This is even more confusing because I’m assuming once it checks if it exists, it will attempt to insert it. I actually copied and pasted the table name into the database to make sure the table existed.

I finally got around to putting the User Guide back up!  Sorry for any inconvenience.

@Iverson: tdktank59’s correct.  It always does a select statement before adding or removing a relationship.  This is to ensure the integrity of relationships remain correct.  For example, if you had a One to Many relationship, it would ensure that if a record already existed for the “One” side, it would update it rather than creating a new one, to keep the One to Many relationship intact etc.

What are the relationship settings you have in your models?

Thanks for the help earlier on my question about ordering.  I have another question.  Is there a way to print out or log the sql statement datamapper creates?

yes,

add this to the bottom of the config file.

$config[‘enable_profiler’] = TRUE;

It looks like the download links in the user guide are broken since you moved to the new site.  The download in the CI wiki works, though.  I’m looking forward to trying out DataMapper.

yes,

add this to the bottom of the config file.

$config[‘enable_profiler’] = TRUE;

Actually, that method will only work when DataMapper in used conjunction with Modular Extensions - HMVC v5.x (see this post for more info). 

A typical DM user would probably want to follow the CI user guide and use the output class’s enable_profiler method to set that property:

$this->output->enable_profiler(TRUE);

Allright so im working on some permission stuff…

Heres the database stuff

roles
id
name
description

pages
id
name
description

methods
id
name
description

users
id
username
password

join_roles_users *many to many
id
role_id
user_id

join_pages_roles *many to many
id
page_id
role_id

join_methods_roles *many to many
id
method_id
role_id

join_methods_pages *many to many
id
method_id
page_id

Everything is setup right at least as far as I can tell (too much code to post. also many to many relations everywhere)

So heres the problem im having.

I need to figure out if the user has a role that has access to the page (class) and method. The problem is I can get the results for all of the pages. Not for the methods attached to the pages in the role. If you understand that.

So a user would have these permissions
Note that the names are the same and should be taken as one entry into the database not 2… This way there are not alot of duplicate entries just relations from page to method.

Page 1
* Action 2
Page 2
* Action 1
* Action 2

So when I try and get the correct relations all the actions appear for page 1 under both roles.

for example heres what i can print
and index should not be under brainstorms

*1 brainstorms
*   1 index
   2 grid
   3 create
   4 view
   5 edit
2 auth
   1 index
   6 logout
   7 login

Let me know if that makes any sense… Im trying to get the pages to display there relations based on the roles relations…

I don’t understand what you are trying to do, but maybe I can point you in a direction.

As great as DataMapper is, occasionally you still just have to write or build your own SQL code.  I have a fairly complex setup with a table that represents two similar-but-slightly-different models, that I have to query as one.  (One is a job, the other is a servicecall.  They contain similar data, but have different relationships.)

Using DataMapper normally won’t work, because the models will automatically filter out the data, and the joined columns have different names.

So, I am using $model->db->join($join_table, $join_on) to allow for more complex joins.  For example:

$jobs = new Job();
// custom joins:
// join users on either contracts OR servicecalls
$jobs->db->join('join_jobs_users',
        '(join_jobs_users.job_id = model_jobs.id OR join_jobs_users.servicecall_id = model_jobs.id)');
// join in the actual users table
$jobs->db->join('model_users', 'model_users.id = join_jobs_users.user_id');
// can use this like normal
$jobs->where('model_users.id', $userid);
// get_any is a method within Job that overrides the servicecall check.
$jobs->get_any();

(I don’t remember if I had to modify DM or CI to make these queries work.  It’s been a while since I started doing this for my compound queries.)

My point is, I guess, you might need to hand join the tables.  (It’s also fairly easy to add a method to join related tables directly into the result.  However, this requires a few small changes in DataMapper.)

Yeah i could do that…

However I found a way to do it!

I ended up taking a look at the manual again and sure enough there was a way to do it. The trick is through loops…

for example:

returns this:

  * 1 Administrator
      o 1 brainstorms
          + 2 grid
          + 3 create
          + 4 view
          + 5 edit

  * 2 Managers
      o 2 auth
          + 6 logout
          + 7 login

Now all I do is add my checks in there and all is good!

Just a word of warning: looping through like that does result in a lot of queries (in your example, there were 5 queries).  That may be fine, if so, then you are all set.

In my case, the actual query I represented, along with the joins, had resulted in something like N*M*O queries - and this was the startup/welcome page.  Not good having the main page require 30 some queries just to load in data, much of it returning redundant information.  I now have that query knocked down to about 5, including the authorization queries.  Of course, my end query is about 20 lines!

However, the rule of thumb is to wait to optimize, so I’m glad you found a solution!

I’m hoping to work with stensi to help incorporate better joins, so it is easy to represent complex data.

lol

Well actually its 19 queries just for the permissions… However the load time dosnt change that much…

Before:

Loading Time Base Classes     0.0170
Controller Execution Time ( Welcome / Index )    0.0609
Total Execution Time     0.0781

After:

Loading Time Base Classes     0.0153
Controller Execution Time ( Welcome / Index )    0.0879
Total Execution Time     0.1035

so for 0.0254 seconds i can get all my permissions in those 19 joins. Mind you this is with very limited data in the database so it would be interesting to see after there is alot of rows… But that was the point of having the methods handle multiple classes (pages). So an index on one page would be the index on another etc… (as far as the database sees them)

Hello again.
I’ve been busy developing an extension to the Datamapper Model that will provide methods to quickly draw forms/tables of your Datamapper models. It’s going well, Datamapper has proved to be very effective.

However a number of times I have met an issue removing relationships from a model.

Example:
I have two related models.

class School extends DataMapper {

    var $has_many = array("student");

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

class Student extends DataMapper {

    var $has_one = array("school");

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

I wish to remove a subset of pupils from a school and then display the remaining school pupils

// ini models
$sch = new School();
$stu = new Student();

// load school
$sch->get_by_id(1);

// load subset of students
$stu->where_in('id', array(1, 3, 4, 7));
$stu->get();

// remove subset of students from school
$sch->delete($stu->all);

// refresh remaining students at school
$sch->school->get(); // throws 'Call to a member function get() on a non-object' Error

There are ways around this issue, you can re-initialise the student object within the school, but its not very pretty. Just wondering if anybody else has has this issue or can see a cleaner solution?

Cheers.

Did anybody resolve this? I’m getting the same error and I’m positive my models and database is correct. I rename the model or table and it throws a database error so I know it’s looking at the right stuff. But I get Call to a member function get() on a non-object’ Error everytime I try to access a simple one to many relationship.

UPDATE: Ignore everything in this post.  It is incorrect.  See the second post after this one.

==== IGNORE THIS ====
I looked into it.  There is a bug of sorts, that exists between PHP and DataMapper.

When you look up an Object, DM figures out what you want using the __get() magic method.  This method is only called if the specified property (in this case, the related model) doesn’t already exist.

When you call ->delete($obj), DataMapper assumes you want to remove the object as well.  So, it calls unset() on the property.  However, apparently PHP doesn’t consider an unset() property the same thing as one that doesn’t exist.

So, enough with the technical explanation.  To fix it, I recommend this.  Go to line 2419 or so, and replace this line:

unset($this->{$object->model});

with this one:

$this->{$object->model}->clear();

This should fix the problem, while still clearing out the related object.

UPDATE Question:
What version of PHP are you running?  I tested it on mine, and PHP 5.2.6 had no problem calling the __get method after an unset.

UPDATE 2
One more: you actually have a mistake in your example code!  You are calling sch->school, but you mean sch->student.  That’s your error.