Re: SQL Error: 1054 - Join query between 3 tables - HABM / dynamic binding

It looks to me like your associations should be:

Product:

var $hasOne = array(
'Feature' => array(
'className' => 'Feature',
'foreignKey' => 'product_id',
'dependent' => false
)
);

... or ...

Product:

var $hasMany = array(
'Feature' => array(
'className' => 'Feature',
'foreignKey' => 'product_id',
'dependent' => false
)
);

Feature:

var $belongsTo = array(
'Product' => array(
'className' => 'Product',
'foreignKey' => 'feature_id',
'dependent' => false
)
);

... but not both. If Feature and Product is only a one-to-one
relationship then only one table needs a foreign key. At a guess, that
would be the features table (my 1st example).

On Mon, Apr 27, 2009 at 1:59 PM, projectmayhem69@gmail.com
<projectmayhem69@gmail.com> wrote:
>
>
> http://groups.google.com/group/cake-php/browse_thread/thread/863305f8a7267067
>
> According to above advice I have tried below join in cakephp 1.2 but I
> keep on getting below SQL error for external fields in my find('all')
> call - Product.feature_id, Tag.id, etc..
>
> SQL Error: 1054: Unknown column 'Product.feature_id' in 'where clause'
>
> I have searched Google Groups but did not find the solution that would
> work. Have tried many variations, but the error seem to be
> persistent.
>
>
> Below setup details, all support highly appreciated.
>
>
>
> Tables
>
> Table 1 - 'tags'
> id
> tag
>
> Table 2 - 'products_tags'
> id
> tag_id
> product_id
>
> Table 3 - 'products'
> id
> feature_id
>
> Table 4 - 'features'
> id
> name
>
>
>
> Models
>
> Model 1 - 'Tag'
>
> var $hasAndBelongsToMany = array(
>                'Product' => array('className' => 'Product',
>                                        'joinTable' => 'products_tags',
>                                        'foreignKey' => 'tag_id',
>                                        'associationForeignKey' => 'product_id',
>                                        'unique' => true
>                )
> );
>
> Model 3 - 'Product'
>
> var $hasAndBelongsToMany = array(
>                'Tag' => array('className' => 'Tag',
>                                        'joinTable' => 'products_tags',
>                                        'foreignKey' => 'product_id',
>                                        'associationForeignKey' => 'tag_id',
>                                        'unique' => true
>                )
> );
>
> var $hasOne = array(
>                'Feature' => array('className' => 'Feature',
>                                                        'foreignKey' => 'id',
>                                                        'dependent' => false
>                ));
>
> Model 4 - 'Feature'
>
> var $hasOne = array(
>                'Product' => array('className' => 'Product',
>                                                                                'foreignKey' => 'feature_id',
>                                                                'dependent' => false
>                ));
>
>
>
> Controller
>
> // Dynamic binding
>
> $this->Product->bindModel(array('hasOne' => array(
>    'Feature1' =>
> array('className' => 'Feature', 'foreignKey' => false, 'conditions' =>
> 'Feature1.id=Product.feature_id'),
>    'ProductTag1' =>
> array('className' => 'ProductTag', 'foreignKey' => false, 'conditions'
> => 'ProductTag1.product_id=Product.id'),
>    'Tag1' =>
> array('className' => 'Tag', 'foreignKey' => false, 'conditions' =>
> 'Tag1.id=ProductTag1.tag_id'),
>  ) ), false);
>
>
> // Find statement
>
> $res = $this->Product->find('all', array(
>  'fields' => 'Product.*, Feature.*, Tag.*',
>  'recursive' => 0,
>  'conditions' => array('Feature.name' => 'LIKE %Wi-Fi%',
>  'Tag1.tag' => array('Wireless', 'Wi-Fi'), '1=1 GROUP BY
> Product.id'),
>  'order' => array('Product.id ASC')
> ));
>
>
> >
>

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