Re: Digest for [email protected] - 6 Messages in 3 Topics

Mon, 14 Feb 2011 06:33:47 -0800

I've succeeded in getting joins across database tables last week using the hasmany-finderquery. 
The finder query permits you to insert a very specific mysql join statement.
I found out when a remote resource database is defined, that you must specify your local resource 
as well each and every time you use it.
It took me a couple days to figure this out, I was happy as heck when it worked.
It seems that the finderquery has some additional benefits, because of the specificity of the return 
it "seems" more efficient.
In my case, I need the database name to be dynamically assigned the required further modification to the model definition, It had to be moved into a private construct function. 

It's Ugly & Gnarly but works pretty cool.

class PluginName extends PluginNameAppModel {

    var $name = 'Plugin';
    var $hasMany;



        function __construct($id = false, $table = null, $ds = null) {
        $db = ConnectionManager::getDataSource('default');
        $siteDBname = $db->config['database'];
        $resourceDB = 'shared_resource_name';
$firstQuery = 'SELECT Whatever.whatever, Whatever.anotherthing, Whatever.somethingelse FROM '.$resourceDB.'.table-name AS Whatever JOIN '.$siteDBname.'.join-table-name as JoinTable ON Whatever.key-field = JoinTable.key-field OR Whatever.key-field2 LIKE JoinTable .key-field2 OR Whatever.key-field3 LIKE JoinTable .key-field3 WHERE JoinTable .index_id = {$__cakeID__$}'; 

        $secondQuery = 'SELECT Whoever.id, Whoever.name, Whoever.slug
FROM '.$siteDBname.'.table-name AS Whoever JOIN '.$siteDBname.'.join-table-name as JoinTable ON 
                                    Whoever.id = JoinTable .field_id
WHERE JoinTable .field_id = {$__cakeID__$}'; 

        $this->hasMany = array(
            'Whatever' => array(
                'className'        => 'Whatever',
'foreignKey' => false, //!IMPORTANT do not leave out foreignKey, either define or set to false!! 
                'finderQuery'    => $firstQuery ),

            'Whoever' => array(
                'className'     => 'Whoever.JoinName',
                'foreignKey'    => false,
                'finderQuery'    => $secondQuery ),

        );


On 2/11/2011 10:49 PM, [email protected] wrote:

Today's Topic Summary

Group: http://groups.google.com/group/cake-php/topics

    * Joins across tables? <#group_thread_0> [1 Update]
    * XML DataSource or solution <#group_thread_1> [1 Update]
    * New vs Old and need help - FEMALE NEEDS HELP! <#group_thread_2>
      [4 Updates]
Topic: Joins across tables? <http://groups.google.com/group/cake-php/t/aaf3378f40025a8b> 

      "Krissy Masters" <[email protected]> Feb 11 11:30PM
      -0330 ^ <#digest_top>

      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
Topic: XML DataSource or solution <http://groups.google.com/group/cake-php/t/b8e6dc1a089eba0> 

      Carlos Paparoni <[email protected]> Feb 11 06:34PM -0800
      ^ <#digest_top>

      To make an update on the situation:

      I made the code work with the Array DataSource up to a point. The
      problem right now is that it uses a $records array which is assigned
      statically in the Model, like this:
      <?php
      class State extends AppModel {
      var $name = 'State';
      var $useDbConfig = 'local';
      var $displayField = 'name';
      //Here is where records are assigned to the array:
      var $records = array(
      array('id' => 1, 'name' => 'Alabama', 'abbr' => 'AL'),
      array('id' => 2, 'name' => 'Alaska', 'abbr' => 'AK'),
      array('id' => 3, 'name' => 'Arizona', 'abbr' => 'AZ')
      );
      }
      ?>

      The issue is that if I try to use a function inside the model (for
      example, one I found to read a XML file and turn it into an
      array), I
      get parsing errors from PHP.
      So, I'm stuck. Where should I add that functionality? Should I edit
      the datasource and have it read the XML from there?
Topic: New vs Old and need help - FEMALE NEEDS HELP! <http://groups.google.com/group/cake-php/t/c7bc0bb2a530ec08> 

      "Krissy Masters" <[email protected]> Feb 11 09:22PM
      -0330 ^ <#digest_top>

      I do not think it matters if your new and need help or old
      cakepro and need
      help. Why the need to emphasize?

      New you say! Are you? Well then let me just drop everything,
      BREAKING NEWS!
      A NEW USER TO CAKE! Holy shit say it's not so!

      People on the board for more than a month can tell by the name
      basically if
      your new or not and the depth of the question gives it away too
      in most
      cases.

      I maybe I should put Girl Needs Help, maybe damsel in distress
      will get more
      replies?

      People will help, you so saying your new over and over is just
      irritating.
      Once these NEW people are on the board a while they too will see
      just how
      dumb it is to see post after post saying just how new people are.

      Krissy => 4 months, 3 days using cake so do I still qualify as
      new or do I
      have to phrase my questions with old hag needs help :P

      "Larry E. Masters" <[email protected]> Feb 11 07:04PM -0600 ^
      <#digest_top>

      Old hag might get some of us old timers attention.
-- Larry E. Masters 


      On Fri, Feb 11, 2011 at 6:52 PM, Krissy Masters

      thatsgreat2345 <[email protected]> Feb 11 05:23PM -0800 ^
      <#digest_top>

      I think the ability to say whether you're new/old doesn't depend on
      how long you've been using cake but you knowledge of the ins and
      outs
      of cake and how it all works.

      On Feb 11, 4:52 pm, "Krissy Masters" <[email protected]>
      wrote:

      Ryan Snowden <[email protected]> Feb 12 09:53AM +0800 ^
      <#digest_top>

      Or Noobhag

      Neat

--
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 


--
<http://www.sacodesign.com/>      

Scott Allen

[email protected] <mailto:[email protected]>

Saco Design, Inc. <http://www.sacodesign.com/>

207-221-3255

--
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

<<inline: saco-design-logo.gif>>

Reply via email to