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

Hi

The primary Model you are working with is Categorie, so you don't need to join against the categories table again.

This will get you closer.

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

You can use the sql_dump, or the DebugKit plugin to view the actual SQL generated by CakePHP.

Regards

Reuben Helms

On Tuesday, 15 October 2013 19:48:40 UTC+10, UltraMarkus wrote:

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.