[CakePHP : The Rapid Development Framework for PHP] #6366: HABTM: finderQuery is executed but no child records are returned

[CakePHP : The Rapid Development Framework for PHP]

#6366: HABTM: finderQuery is executed but no child records are returned
-----------------------------------+----------------------------------------
    Reporter:  thpar               |          Type:  Bug   
      Status:  new                 |      Priority:  Medium
   Milestone:  1.2.x.x             |     Component:  Model 
     Version:  1.2 Final           |      Severity:  Normal
    Keywords:  FinderQuery, HABTM  |   Php_version:  PHP 5 
Cake_version:  1.2.3.8166          |  
-----------------------------------+----------------------------------------
 While bug #4100 claims this issue was resolved, I believe it still
 persists.
 When having a HABTM relationship in an uncommon database schema, I want to
 define this relationship by specifying the FinderQuery. In debug mode 2,
 you can see the query is executed correctly (also if copy-pasted into a
 MySQL client, it returns the correct results), but still the linked models
 are just empty arrays.


 Some proposed solutions (these didn't help):
 - alias the table names to the linked model name
 - set foreignKeys to false



 Besides, I wonder what to do with the joinTable field if you don't have an
 actual joinTable here (due to poor database design).
 Seems you can't leave the joinTable field empty, null or false?


 My (slightly cleaned up) code:

 {{{
 <?php
 class Gene extends AppModel{
         var $name='Gene';
         var $useTable='annotation';

         var $primaryKey='uniqID';
         var $displayField = 'transcript_ID';

         var $hasAndBelongsToMany = array(
                                         'GeneFamily' => array(
                                                 'className' =>
 'GeneFamily',
                                                 'joinTable' =>
 'geneID_familyID',
                                                 'foreignKey' => false,
                                                 'associationForeignKey'
 => false,
                                                 'finderQuery' => 'SELECT
 GeneFamily.family_ID,
  GeneFamily.family_name FROM families as GeneFamily JOIN geneID_familyID
 as Mapping on
 GeneFamily.family_ID = Mapping.family_ID WHERE Mapping.gene_ID IN (SELECT
 Gene.gene_ID FROM
 annotation as Gene WHERE uniqID = {$__cakeID__$})',
                                                 'deleteQuery' => '',
                                                 'insertQuery' => ''
                                 ));
 }
 ?>
 }}}

 {{{
 <?php
 class GeneFamily extends AppModel{
         var $name='GeneFamily';
         var $useTable='families';
         var $primaryKey='family_ID';
         var $displayField = 'family_name';

         var $belongsTo = array(
                                 'SuperFamily' => array(
                                         'className' => 'SuperFamily',
                                         'foreignKey' => 'superfam_ID'));
 }
 ?>
 }}}

 {{{
 <?php

 class LookupSequenceController extends AppController {
         var $name = 'LookupSequence';
         var $uses = array("Gene", "GeneFamily");

         function index(){
                 $gene_id = "AT1G73690";
                 //get the first occurence
                 $myGene = $this->Gene->find('first', array(
                         'conditions' => array("Gene.gene_ID" =>
 $gene_id)));

                 $this->set('myGene', $myGene);
         }

 }
 ?>
 }}}


 {{{
 /* Resulting queries : */

 SELECT `Gene`.`uniqID`, ... and all other fields...
 FROM `annotation` AS `Gene` WHERE `Gene`.`gene_ID` = 'AT1G73690' LIMIT 1

 SELECT GeneFamily.family_ID, GeneFamily.family_name FROM families as
 GeneFamily JOIN
 geneID_familyID as Mapping on GeneFamily.family_ID = Mapping.family_ID
 WHERE Mapping.gene_ID IN
 (SELECT Gene.gene_ID FROM annotation as Gene WHERE uniqID = 7608)

 (Num rows: 1 ... these queries work!)

 /* Resulting object: */
 Array ( [Gene] => Array ( [uniqID] => 7608 ... etc... all data is here...
 )
 [GeneFamily] => Array ( ) )
 }}}

 This linked model 'GeneFamily' is empty, while it should contain the
 fields family_ID and family_name.


 Note: A similar construction with $hasMany works fine.

-- 
Ticket URL: <https://trac.cakephp.org/ticket/6366>
CakePHP : The Rapid Development Framework for PHP <https://trac.cakephp.org/>
Cake is a rapid development framework for PHP which uses commonly known design 
patterns like ActiveRecord, Association Data Mapping, Front Controller and MVC. 
Our primary goal is to provide a structured framework that enables PHP users at 
all levels to rapidly develop robust web applications, without any loss to 
flexibility.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups 
"tickets cakephp" group.
To post to this group, send email to tickets-cakephp@googlegroups.com
To unsubscribe from this group, send email to 
tickets-cakephp+unsubscr...@googlegroups.com
For more options, visit this group at 
http://groups.google.com/group/tickets-cakephp?hl=en
-~----------~----~----~----~------~----~------~--~---