Re: mysql join query gives too much results, twice.

Hi,

To have the categories of a product, I would search from Product model.

If recursive = 1 the associated categories would be in the result.

$this->Category->Product->find('first', array(

'conditions' => array('Product.id'=>$pid)

));

Or you can use Containable behavior to control what you want in the result.

You have in your Product model :

public $actsAs = array('Containable');

public $hasAndBelongsToMany = array(

'Category' => array(

'className' => 'Category',

'joinTable' => 'cat_connect',

'foreignKey' => 'product_id', 

'associationForeignKey'  => 'category_id', 

) 

 );

to find the categories from the Controller :

$this->Product->find('first', array(

'conditions' => array('Product.id'=>$pid),

'contain' => array('Category.name')

));

sorry if I have not answered the question,

hope this helps

BenJ

Le mardi 15 octobre 2013 11:48:40 UTC+2, UltraMarkus a écrit :

Hi,

 

As im new to this group, i first want to apologize for my bad english. Just starded to use cakePHP 2 weeks ago, and i must say, great work!!! I managed to get my things to be done and Im gratefull for excistence of it.

 

But hey, now im stuck with my query, and believe me, i searched a lot. However, I might not understand what Im doing or it's just a hard trick.

 

Here is my query:

 

$this->set('categorienAdded', $this->Categorie->find('all',array('joins' => array(
    array(
        'table' => 'categories',
        'alias' => 'cat',
        'type' => 'inner',
        'foreignKey' => false,
  'conditions'=> array('')       
    ),
    array(
        'table' => 'cat_connects',
        'alias' => 'cat_con',
        'type' => 'inner',
        'foreignKey' => false,
        'conditions'=> array(           
   'cat.id = cat_con.categorie_id',
            'cat_con.product_id' => $pid)
        )
    )
)));

 

My tables are :

 

     cat_connect:

 

     id              product_id              categorie_id

     1               65                           4

     2               64                           2

     3               64                           1

 

     categories:

 

     id              naam

     1               Categorie 1

     2               Test

     3               Work

     4               Temp

 

 

As my $pid = 64 I whould like to have only 'Categorie 1'  and  'Test' in my result. However, i get all Names twice. How should i build my query?

 

 

--
Like Us on FaceBook https://www.facebook.com/CakePHP
Find us on Twitter http://twitter.com/CakePHP
 
---
You received this message because you are subscribed to the Google Groups "CakePHP" group.
To unsubscribe from this group and stop receiving emails from it, send an email to cake-php+unsubscribe@googlegroups.com.
To post to this group, send email to cake-php@googlegroups.com.
Visit this group at http://groups.google.com/group/cake-php.
For more options, visit https://groups.google.com/groups/opt_out.