DataMapper ORM v1.8.2

[eluser]imohammad[/eluser]
I had no idea that you can use where_related multiple times.
You have been a huge help for me during this learning process and I'm so thankful Simon!

Let me make the same previous example but a bit harder.

Let's say we want the same result in the previous example and in addition, we want to filter them by their rates.

Code:

So I have the following tabe (in addition to the previous tables):

Users_rates


Users_Rates Columns:
- ID
- User_id
- rating

And we want to calculate the average of the rating for each user, and we filter the retrieved users based on the average rate.

Code:

Users_Rates dummy data:
1
1
4

2
1
5

3
2
3

4
2
5

And I want to include the average column with the result. I don't think I can use Include_related()? because this is a one to many relation.


I'm thinking of all possible examples, and I'm thankful for your help a lot!


- Mohammed

[quote author="Maglok" date="1406210862"]Yeah I know how ORM needs a bit of a different approach in thinking.

So you want users that have a certain skill and/or a certain group. You can do that like this:

Code:

// Instance your objects
$group = new Group();
$user = new User();
$skill = new Skill();

// Get a bunch of groups
$group->where('title', 'dev')->get();

// Combine the get for the skills and get them
$skill->where_in('title', array('php', 'java'))->get();

// Get the set that has the related $group and the related $skill
$this->where_related($group)->where_related($skill)->get();

This is just a example, I didn't test it.

And important thing is to remember that you cannot just do another get() on a instance of a datamapper object and expect it to remember the previous result.[/quote]

[eluser]Maglok[/eluser]
I have to admit I am not sure I grasp your question 100%.

Let's see what I can make of it.

You have a table where you store what user has rated what/how high.

I am not sure what you mean by 'the average column' I do not see a average column in your example.

I might be going off-road here but seems to me you have to setup your datamodel as the following, see some useage examples below as well:

Code:

// Setup a User model
// Setup a Group model
// Setup a Skill model
// Setup a Rating model

// Setup relations so that a User has many Groups, Skills and Ratings

// Create a new user
$user = new User();

// Get a specific user with ID 5
$user->get_by_id(5);

// Access data from that user
foreach($user->rating as $rating)
{
  echo $rating->rating; // Print the rating column of the current rating
}

// You could also write a function in your User model that returns the average of all the ratings from that user
// In User model
public function average_rating()
{
  // Get all the ratings and put them in a single array
  $ratinginfo = $this->rating->all_to_single_array('rating');
  
  // Add them all up
  $sum = array_sum($ratinginfo);
  
  // Calculate the average and return it
  return ($sum / sizeof($ratinginfo));
}

// You can also try a approach to getting a average by calculating it in the constructor once and then assigning it as a value of Rating
// User constructor
public function __construct()
{
  parent::__construct();
  
  $this->average_rating = $this->average_rating();
}

[eluser]lovestorms[/eluser]
Hi all,
I'm new at datamapper and i need your help.

Model:

Code:

class Details extends DataMapper
{
var $table = 'details';
public $has_many = array('box');
public $validation = array(
  'price' =>array(
   'rules'=>array('required')
  ),
  'as' =>array(
   'rules'=>array('required')
  ),
  'is_sbox' =>array(
   'rules'=>array('required')
  ),
  'month' =>array(
   'rules'=>array('required')
  ),
  'type' =>array(
   'rules'=>array('required')
  ),
  'date_time' =>array(
   'rules'=>array('required')
  ),
);
}

class box extends DataMapper
{
var $table = 'box';
public $has_one = array('details');

public $validation = array(
  'username' => array(
   'rules'=>array('required')
  ),
  'passport_no' => array(
   'rules'=>array('required')
  ),
  'address' => array(
   'rules'=>array('required')
  ),
  'phone' => array(
   'rules'=>array('required')
  ),
);
}

Control:

Code:

function contracts(){
  
  $c = new Details();
  $s = new Box ();

  $c->where('type','3')->get();
  $s->where_related('details', 'id', $c)->get();

            
         $this->load->view('admin/contracts');
}

Error:

Code:

Error Number: 1146

Table 'testdb.details_box' doesn't exist

SELECT `box`.* FROM (`box`) LEFT OUTER JOIN `details_box` details_box ON `box`.`id` = `details_box`.`box_id` WHERE `details_box`.`details_id` IN (64, 66, 67, 68, 86, 88)

Thank you

[eluser]Maglok[/eluser]
Hey there,

Datamapper is trying to find a join table named details_box between details and box.

Something that also pops is that you are doing a where_related with $c as parameter while that should be a value not a object.

It could be that your database does not have the correct schematic. Could you post that?

[eluser]lovestorms[/eluser]
Hi, there are my table

Code:

Details:

id
price
id_box
type (1:buy, 2:rent)

Box:

id
account
password
email
nickname

I set relation both of them in model is $has_many. I want join table details with table box without create new table detail_box.

Control:

Code:

$box = new Box;

$box->include_related('details', 'id_box');

//$box->get_iterated();
$box->get();

[eluser]Maglok[/eluser]
There are two ways to create a relation, the has one and the has many.

The has many requires a join table between the two.

The has one requires the class that has one to have a column called field_id. So in your case that would be box_id. Unfortunately it is hardcoded in datamapper that it has to be field_id, so try renaming your id_box into box_id.

[eluser]lovestorms[/eluser]
[quote author="Maglok" date="1409294528"]There are two ways to create a relation, the has one and the has many.

The has many requires a join table between the two.

The has one requires the class that has one to have a column called field_id. So in your case that would be box_id. Unfortunately it is hardcoded in datamapper that it has to be field_id, so try renaming your id_box into box_id. [/quote]

Thank you! I have fix it.

How about join 3 or more table? It use a same way?

[eluser]Maglok[/eluser]
That is trickier. There are two ways to do that.

First option: You create join columns in the table and use them like join fields. That way is not that good though, since then you will have a ID and you still need to get() the actual object.

Second (and in my opinion better) option: Instead of just a join table you create the join table with 3+ joins and you define it as a datamapper model. This is a difficult concept so let me give an example:

Code:

We have a Project, Person and Role. Lets say we want a Person to get a Role in a Project. The Role is a list of options from the database.

We have Project, Person and Role as Datamapper objects. But we need a join table with the project_id, person_id and role_id. We name the join table Project_person_role.

Then we make a datamapper model for Project_person_role.

Project_person_role
has one: Project
has one: Person
has one: Role

Project
has many Project_person_role

Person
has many Project_person_role

Role
has many Project_person_role

[eluser]maikens[/eluser]
Hi, I have the following scenario:

There are are three tables involved in this: groups, user_types, and resources.

user_types have a many-to-many relationship with groups - The user can create user_types, say one named 'painter' and one named 'foreman'. They can then choose which user_types are available to each group. For instance, assuming there are two groups 'a' and 'b', the user might decide that group 'a' has painters and foremen, and that group 'b' has only painters.

So when creating a resource, such as a manual, the user specifies which user_types can access them in each group. So the user might create manuals 1 and 2, and assign manual 1 to painters in group a, and manual 2 to painters in group b

My tentative DataMapper solution to this is to have a many-to-many relationship between groups and user_types using a join table called groups_user_types. Then, that join table has a many-to-many relationship to resources, using a join table called groups_user_types_resources. I am unsure if this can be done in DM, as I can't find any documentation / examples with this particular relationship. Can I do this in DM, and if so, how? And if not, any suggestions on how to implement a solution blending DM with CodeIgniter's Active Record class as painlessly as possible?

Db tables would look like this:

Code:

resources
---------
id|name
1|manual1
2|manual2

user_types
----------
id|name
1|painters
2|foremen

groups
------
id|name
1|a
2|b

groups_user_types
-----------------
id|group_id|user_type_id
1|       1|           1
2|       1|           2
3|       2|           1

groups_user_types_resources
---------------------------
id|resource_id|groups_user_type_id
1|          1|                  1
2|          1|                  3
3|          2|                  1
4|          2|                  2

I'd appreciate any corrections and insight you could provide.

[eluser]Maglok[/eluser]
Hey there.

Ah yes I know your 'issue' you effectively want a join with 3 tables. There is a way to do this.

Make a DM object out of groups_user_types_resources. Give that DM object a 'has_one' relation with each of the tables/DM objects.



(If you cant get something done in DM you can always use the query() method and write your own query as well, but I have not had to use it just yet)