DataMapper ORM v1.8.0

[eluser]Dennis Rasmussen[/eluser]
Gief array solution ! (just kidding)
This is just a secret thank you bump.

[eluser]IgnitedCoder[/eluser]
@WanWizard,

I posted a question recently and wasn't sure if you saw it... so here goes if you don't mind.

I have atable as follows, and I'm using include related with great success...

Table [relatedusers_users]

[id] [user_id] [relateduser_id] [type]
1 4 2 sales
2 4 3 contact
3 2 4 doctor
4 2 3 partners

Now heres where it gets tricky. Assuming my user_id = 4, I need to find the number and list the connections we have in common. In the above table I am related to user 3 and so is user 2. So basically we have 1 connection in common.

How would I query this out... Right now this works for me.

Code:

var $has_many = array('relateduser' => array('class' => 'user', 'other_field' => 'user'),
                          'user' => array('other_field' => 'relateduser'));

$user->relateduser->get(); - I get all related users to me

Please provide some direction...

Thanks,
Brendan

[eluser]WanWizard[/eluser]
Always think about how you would solve this in native SQL. The answer: use a subquery.

I just posted an anwer to a similar question here: http://ellislab.com/forums/viewthread/182930/#865744. Offcourse, in your case you need a where_in_related(), because you're looking for connections in common...

[eluser]AdamM[/eluser]
I've got a question: how to use DataMapper with multiple databases?

Suppose, I've got an external database on same server in Internet. And a DSN is not static, so I receive it at "runtime".

In normal case, I can use simply:

Code:

$DB1 = $this->load->database($dsn1, TRUE);
$DB2 = $this->load->database($dsn2, TRUE);

and I can operate on both databases simultaneously. How can I pass $dsn to DataMapper objects?

[eluser]WanWizard[/eluser]
Check the manual on the 'db_params' configuration variable. You can also define this per model.

If you want to set it dynamically, you'll have to to this in the model constructor, before you call parent::_construct().

[eluser]AdamM[/eluser]
[quote author="WanWizard" date="1299636223"]Check the manual on the 'db_params' configuration variable. You can also define this per model.

If you want to set it dynamically, you'll have to to this in the model constructor, before you call parent::_construct().[/quote]

But if I have two databases, how to distinguish from which database entity class should use? I set one/two database connections in model constructor and what next?

[eluser]WanWizard[/eluser]
I don't understand what you mean.

Your models table always resides in only one database. This is the one you define in your model. If you don't define one, the database configured in the datamapper config file is used. If that is not present either, CI's database config is used.

[eluser]IgnitedCoder[/eluser]
@WanWizard,

Just want to say awesome work on DM... it has saved me literally hundreds of hours of writing pure SQL code. So thank you!!

Onto my question...

How would I do the following:

select user_id, username, (select count(friends.friend_id) from friends where friends.friend_id = users.id and accepted=1) as connections from users

Thanks in advance.

Brendan

[eluser]WanWizard[/eluser]
You use the subquery feature (http://datamapper.wanwizard.eu/pages/subqueries.html):

Code:

// create the users and friends objects
$users = new User();
$friends = $users->friend;

// create the subquery
$friends->select_func('COUNT', '*', 'count')
$friends->where('accepted', 1);
$friends->where_related('user', 'id', '${parent}.id');

// now compose and run the query
$users->select( array('id', 'username') )->select_subquery($friends, 'connections')->get();

(disclaimer: from the top of my head... )

[eluser]IgnitedCoder[/eluser]
Excellent... works like a charm.

Thanks again...

Brendan