querying across models with multiple databases and useDbConfig

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

querying across models with multiple databases and useDbConfig

Martin Wood-Mitrovski

I have a similar question to this one :

http://groups.google.com/group/cake-php/browse_thread/thread/739fceb74b7ad621/f79ef013c80afccf?lnk=gst

which is kind of lurking unanswered. :)

Say I have 2 models, User and Document

User hasMany Documents
Document belongsTo User

They each live in seperate databases, so in the User model i have

var $useDbConfig = 'users';

while the Document uses the default dbConfig.

Both db config's have persistent set to false.

What I would like to do is to query the Document model using conditions on the
User model, e.g.

$this->Document->recursive = 1;
$this->Document->findAll(array('User.id'=>'1'));

but I get the error: Unknown column 'User.id' in 'where clause'

which is because the User lives in the other DB.

So, is this supposed to work? or is it a situation i have to deal with manually?

i.e. query one model, loop over the results to provide conditions for querying
the second model

or is there some other kind of cake magic I can use?

thanks,

Martin

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "Cake PHP" group.
To post to this group, send email to [hidden email]
To unsubscribe from this group, send email to [hidden email]
For more options, visit this group at http://groups.google.com/group/cake-php?hl=en
-~----------~----~----~----~------~----~------~--~---

Reply | Threaded
Open this post in threaded view
|

Re: querying across models with multiple databases and useDbConfig

francky06l

Did you set debug to 2 ? belongsTo relation is generating a JOIN
statement I believe..
I admit I have never tried having this kind of relation over 2
different DB, very interesting case. Did you try to set the Document
with a "not default" config and the user with the default one ?

On Nov 4, 10:12 pm, Martin Wood-Mitrovski
<[hidden email]> wrote:

> I have a similar question to this one :
>
> http://groups.google.com/group/cake-php/browse_thread/thread/739fceb7...
>
> which is kind of lurking unanswered. :)
>
> Say I have 2 models, User and Document
>
> User hasMany Documents
> Document belongsTo User
>
> They each live in seperate databases, so in the User model i have
>
> var $useDbConfig = 'users';
>
> while the Document uses the default dbConfig.
>
> Both db config's have persistent set to false.
>
> What I would like to do is to query the Document model using conditions on the
> User model, e.g.
>
> $this->Document->recursive = 1;
> $this->Document->findAll(array('User.id'=>'1'));
>
> but I get the error: Unknown column 'User.id' in 'where clause'
>
> which is because the User lives in the other DB.
>
> So, is this supposed to work? or is it a situation i have to deal with manually?
>
> i.e. query one model, loop over the results to provide conditions for querying
> the second model
>
> or is there some other kind of cake magic I can use?
>
> thanks,
>
> Martin


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "Cake PHP" group.
To post to this group, send email to [hidden email]
To unsubscribe from this group, send email to [hidden email]
For more options, visit this group at http://groups.google.com/group/cake-php?hl=en
-~----------~----~----~----~------~----~------~--~---

Reply | Threaded
Open this post in threaded view
|

Re: querying across models with multiple databases and useDbConfig

Grant Cox

1.  It is just not possible to do this, unless the one mysql
connection has permission to access both databases (ie same database
server, same user/pass).

2.  If the permissions are ok, then you can do a join across databases
by prefixing the database name, eg
SELECT User.* from database1.users as User LEFT JOIN
database2.documents as Document ON Document.user_id = User.id

3.  But Cake doesn't put in the database prefixes - as in Cake the
only way to get two databases is with two database connections, and it
is not assumed that these will have the same access details (fair
enough).

4.  If you are sure that this kind of query will work, you can
construct it manually.  Or, you can do something tricky like:

// add this to your /app/app_model.php
        function getDatabaseName()
        {
                $conn =& ConnectionManager::getInstance();
                $db_name = $conn->config->{$this->useDbConfig}['database'];

                return $db_name;
        }

// make your query like:
// first add the database names to the model's table prefix
$this->Document->tablePrefix = $this->Document->getDatabaseName() .'.'.
$this->Document->tablePrefix;
$this->Document->User->tablePrefix = $this->Document->User-
>getDatabaseName() .'.'.$this->Document->User->tablePrefix;
// now make the query
$this->Document->recursive = 1;
$this->Document->findAll(array('User.id'=>'1'));



I'm not 100% sure that the above will work as is - but I have
something similar in a modified version of
http://bakery.cakephp.org/articles/view/extending-of-dbosource-and-model-with-sql-generator-function
and it works fine to create queries joining across databases.


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "Cake PHP" group.
To post to this group, send email to [hidden email]
To unsubscribe from this group, send email to [hidden email]
For more options, visit this group at http://groups.google.com/group/cake-php?hl=en
-~----------~----~----~----~------~----~------~--~---

Reply | Threaded
Open this post in threaded view
|

Re: querying across models with multiple databases and useDbConfig

Martin Wood-Mitrovski



Grant Cox wrote:
> 1.  It is just not possible to do this, unless the one mysql
> connection has permission to access both databases (ie same database
> server, same user/pass).

unfortunately in this case they are seperate databases with seperate
permissions, although they are within the same mysql server (although for future
projects this might not be true either)

i've just replaced the code with some manual stuff, query the users db, then
loop, query and merge the results from the documents db.

But, I can see this becoming more of an issue as Cake becomes more widely used
and people try to integrate their apps with existing databases. The ability to
$useDbConfig is great and works perfectly until you start doing cross-db
conditions. Of course it would be fantastic if the code didnt have to know or
care about the database backing the model, that way it would be much more portable.

Im sure generalising the problem for dealing with all possible queries across
multiple seperate databases (and merging the results) is very difficult, so I
dont expect this feature to be added anytime soon, but if anyone has seen this
capability in any other framework I would be interested in seeing how its done
(and trying to port it to Cake)

thanks everyone for your help.

Martin

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "Cake PHP" group.
To post to this group, send email to [hidden email]
To unsubscribe from this group, send email to [hidden email]
For more options, visit this group at http://groups.google.com/group/cake-php?hl=en
-~----------~----~----~----~------~----~------~--~---

Reply | Threaded
Open this post in threaded view
|

Re: querying across models with multiple databases and useDbConfig

Grant Cox

Except that your original post suggested that you wanted to use the
"User.id" as a condition in your WHERE clause, for a join between
Users and Documents.  Which is simply not possible when you have two
databases with different permissions, regardless of the framework (or
lack thereof) - it just isn't possible in SQL.

When you have to have different connections to the database, then you
will need to use multiple queries - so the only way to do it is
$users = $this->User->findAll( $user_conditions, array('User.id') );
$user_ids = Set::extract($users, '{n}.User.id');
$documents = $this->Document->findAll( array('Document.user_id'=>
$user_ids) );

which really isn't that complex.


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "Cake PHP" group.
To post to this group, send email to [hidden email]
To unsubscribe from this group, send email to [hidden email]
For more options, visit this group at http://groups.google.com/group/cake-php?hl=en
-~----------~----~----~----~------~----~------~--~---

Reply | Threaded
Open this post in threaded view
|

Re: querying across models with multiple databases and useDbConfig

Martin Wood-Mitrovski



Grant Cox wrote:
> Except that your original post suggested that you wanted to use the
> "User.id" as a condition in your WHERE clause, for a join between
> Users and Documents.  Which is simply not possible when you have two
> databases with different permissions, regardless of the framework (or
> lack thereof) - it just isn't possible in SQL.

I know it isnt possible in SQL, but im asking about cake, its possible that a
framework sitting above the raw sql can manage at least some subset of these
types of queries.

> When you have to have different connections to the database, then you
> will need to use multiple queries - so the only way to do it is
> $users = $this->User->findAll( $user_conditions, array('User.id') );
> $user_ids = Set::extract($users, '{n}.User.id');
> $documents = $this->Document->findAll( array('Document.user_id'=>
> $user_ids) );
>
> which really isn't that complex.

For me complexity isnt really the issue, although any change from a plain
findAll($conditions) introduces some complexity.

really my concerns are portability of the model and possible issues when working
on a team. where do you push this knowledge of seperate db configs?
what happens with plugins and components?

thanks for your thoughts..

Martin


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "Cake PHP" group.
To post to this group, send email to [hidden email]
To unsubscribe from this group, send email to [hidden email]
For more options, visit this group at http://groups.google.com/group/cake-php?hl=en
-~----------~----~----~----~------~----~------~--~---

Reply | Threaded
Open this post in threaded view
|

Re: querying across models with multiple databases and useDbConfig

davos
In reply to this post by Grant Cox
I had to do something similar (although a little different), and ended up hacking the model to prepend the database into the queries in a similar way to your example
Grant Cox-2 wrote
SELECT User.* from database1.users as User LEFT JOIN
database2.documents as Document ON Document.user_id = User.id
It's a little convoluted but it's explained at http://recurser.com/articles/2007/06/04/multiple-dbs-in-cakephp/ if it's useful at all.