Thanks man. The only reason I want separate is exactly as you mentioned. Eventually I will need to load balance the current single server across different servers to distribute everything.
But that can be done later under development. Was trying to plan ahead. Thanks for the info. Will save this response for future reference. K -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of ShadowCross Sent: Saturday, February 12, 2011 6:49 AM To: CakePHP Subject: Re: Joins across tables? If you are creating your subqueries as described on http://book.cakephp.org/view/1030/Complex-Find-Conditions (sub-section titled "Sub-queries"), you could try replacing 'table' => $dbo->fullTableName($this->User), with 'table' => $dbo->config['database'] . '.' . $dbo- >fullTableName($this->User), I haven't tested this personally, so I can't guarantee it will work. You might want to reevaluate your subqueries and manual joins, or reconsider the use of multiple databases. Each unique $useDbConfig used by the models in a request will open a separate connection to the database. If you are using persistent connections, each request will tie up that many database connections, and can reduce your potential maximum simultaneous users by a factor of at least two (both the database server and the webserver generally configure maximum database connections). If you aren't using persistent connections, there is still the overhead of opening and closing the database connections when it switches from one database to the other. And third-party vendor libraries/packages might use up database connections of their own (i.e. Tera-Wurfl, Gallery2, to name a few that I've "integrated" with my Cake apps). On shared hosts, this can be a critical issue, since you have even less control of how the other accounts are using the database connections. The only time the database_name needs to prepend the table_name is if the tables from different databases are being accessed within the same SQL statement, and will only work if the databases used were on the same database server. You run into additional complications if any of the other database configuration parameters (with the exception of the 'database' and 'prefix') are different. For example, if the 'encoding' is different between the two, accessing the table from another database using one connection may result in different characters than accessing the same table using it's regular connection. If the 'login'/'password' is different, the rights to access the same table or even certain columns in the same table may differ if you try to access it from the other connection. In one of my applications, I had so many tables that I organized them into separate plugins, and each PluginAppModel uses the same $useDbConfig (i.e. inherits from AppModel), but defines a $tablePrefix unique to the plugin. I can easily distinguish the tables used for plugin by its prefix, and use only one database connection. One reason to keep the databases separate is if you foresee the need, in the future, to physically separate the two databases onto separate database servers. One of the companies I used to work for actually had a completely separate database cluster just for the Users database, which only tracked the username, password, and basic profile information. Game scores for each game, Tokens and Prizes won by each user were on other clusters as well. But in this case, each of your SQL statements that access tables from more than on database will break anyway... Have fun designing and coding!!! :-D On Feb 12, 12:16 am, "Krissy Masters" <[email protected]> wrote: > Yeah the only thing I found is an article here > > http://www.foldifoldi.com/?p=436 > > I need the full database table name SELECT * FROM database_name.table like > the example but that example from the link does not work with my setup with > subqueries and manual joins I make myself. > > In the SQL print out the actual database name and error for Table not found > when they cross over. SELECT * FROM `table` as TABLE never > `default`.`table`. > > I did see the various database SQL statements as you described but still for > now it can just be a something to look into later. > > For standard queries everything worked fine, but deeper more complex > "contained" finds was a nightmare. > > So for now all in 1 database will have to do for now. > > Maybe create a ticket so Cake gives option of using full database name plus > table and not just the short model table name? Never did that before. Is a > ticket for a problem only? Feature suggestion? > > Thanks all the same. > > K > > > > -----Original Message----- > From: [email protected] [mailto:[email protected]] On Behalf > > Of ShadowCross > Sent: Saturday, February 12, 2011 2:22 AM > To: CakePHP > Subject: Re: Joins across tables? > > It works for me: > > class Country extends AppModel { > var $useDbConfig = 'iso'; > } > > class Address extends AppModel { > var $useDbConfig = 'default'; > > var $belongsTo = array( > 'Country' => array( > 'className' => 'Country' > ) > ); > } > > Whenever I access the Address table, CakePHP sends two separate SQL > queries. The first query retrieves all the addresses matching the > conditions, the second query retrieves all the countries for those > addresses. It then assembles the results into the array. > > But so far I've only used the $belongsTo, and I'm using Cake's > "automagic". > > Check if you are using a custom query. > > On Feb 11, 7:00 pm, "Krissy Masters" <[email protected]> > wrote: > > Just curious if joins across database tables are supposed to work? Same > > server just different databases. > > > Set up my > > > public $useDbConfig = 'dataDb'; > > public $useDbConfig = 'optiDb'; (in each model depending on its db > > obviously) but now queries come across > > > SQL Error: 1146: Table 'dev_lab.categories' doesn't exist > > > If I go to category page its there no errors because that page has no > joins > > across db's. But a controller / page where categories has joins with a > model > > in another db I get the missing error. > > > Any insight? > > > Thanks > > -- > Our newest site for the community: CakePHP Video Tutorialshttp://tv.cakephp.org > Check out the new CakePHP Questions sitehttp://ask.cakephp.organd help > others with their CakePHP related questions. > > To unsubscribe from this group, send email to > [email protected] For more options, visit this group athttp://groups.google.com/group/cake-php -- Our newest site for the community: CakePHP Video Tutorials http://tv.cakephp.org Check out the new CakePHP Questions site http://ask.cakephp.org and help others with their CakePHP related questions. To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/cake-php -- Our newest site for the community: CakePHP Video Tutorials http://tv.cakephp.org Check out the new CakePHP Questions site http://ask.cakephp.org and help others with their CakePHP related questions. To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/cake-php
