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+unsubscr...@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.
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
mysql join query gives too much results, twice.
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+unsubscr...@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.
Re: Mysql Left join query returns null
Remove the quotes.
suburb1 ='2'"
On Mon, Jul 16, 2012 at 1:00 PM, Ditshwanelo wrote:
> This is my query,i dnt knw wat is wrong it was working nd nw it returns
> null
> $search_query=$this->Suburb->query("SELECT suburbs.*, matchsuburbs.* FROM
> matchsuburbs LEFT JOIN suburbs ON matchsuburbs.suburb2 = suburbs.id WHERE
> matchsuburbs.suburb1 ='2'");
> print_r($search_query);
> returns empty array
>
> --
> 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
> cake-php+unsubscr...@googlegroups.com For more options, visit this group
> at http://groups.google.com/group/cake-php
>
--
*Regards
--
Soumavo Chattaraj
Sr. Application Programmer | Web Spiders
*
--
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
cake-php+unsubscr...@googlegroups.com For more options, visit this group at
http://groups.google.com/group/cake-php
Mysql Left join query returns null
This is my query,i dnt knw wat is wrong it was working nd nw it returns null
$search_query=$this->Suburb->query("SELECT suburbs.*, matchsuburbs.* FROM
matchsuburbs LEFT JOIN suburbs ON matchsuburbs.suburb2 = suburbs.id WHERE
matchsuburbs.suburb1 ='2'");
print_r($search_query);
returns empty array
--
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
cake-php+unsubscr...@googlegroups.com For more options, visit this group at
http://groups.google.com/group/cake-php
to realize a join query !
Hi all,
i trying to realize a join query but its not work and i can not see my
problem/issue?!? I implement my code according to:
http://book.cakephp.org/2.0/en/models/associations-linking-models-together.html
//Models:
class Category extends AppModel {
public $hasMany = array(
'Url' => array(
'className' => 'Url',
'foreignKey' => 'category_id'
)
);}
class Url extends AppModel {
public $hasMany = array(
'ATag' => array(
'className' => 'ATag',
'foreignKey' => 'url_id'
),
'HTag' => array(
'className' => 'HTag',
'foreignKey' => 'url_id'
),
'HeadTag' => array(
'className' => 'HeadTag',
'foreignKey' => 'url_id'
),
'PTag' => array(
'className' => 'PTag',
'foreignKey' => 'url_id'
),
);
}
//Controller:
class FrontendController extends AppController {
public function index(){
$this->loadModel('Category');
$keyword = 'some key';
$domain = 'www.some-domain.com'
/*
How can i realize this with joins:
SELECT * FROM categories, urls, a_tags, head_tags
WHERE categories.id = urls.category_id AND
urls.id = a_tags.url_id AND
urls.id = head_tags.url_id AND
categories.name = 'some key' AND
urls.name LIKE '%www.some-domain.com%'
**/
//it does not work(join)!
$options['joins'] = array(
array('table' => 'urls',
'alias' => 'Url',
'type' => 'LEFT',
'conditions' => array(
'Url.id = ATag.url_id',
'Url.id = HTag.url_id',
'Url.id = HeadTag.url_id',
'Url.id = PTag.url_id',
)
)
);
$options['conditions'] = array(
'AND' =>
array('Category.name' => $keyword),
array("Url.name LIKE" => "%$domain%"),
);
$this->Category->recursive = -1;
$res = $this->Category->find('all', $options);
pr($res)
//Error: SQLSTATE[42S22]: Column not found: 1054 Unknown column
'ATag.url_id' in 'on clause'
/* SQL Query:
SELECT `Category`.`id`, `Category`.`parent_id`, `Category`.`lft`,
`Category`.`rght`, `Category`.`name`,
`Category`.`created`,`Category`.`modified`,`Category`.`blacklist`,
`Category`.`is_searched_by_user`
FROM `ssx`.`categories` AS
`Category`
LEFT JOIN `ssx`.`urls` AS `Url` ON (
`Url`.`id` =`ATag`.`url_id` AND
`Url`.`id` = `HTag`.`url_id` AND
`Url`.`id` =
`HeadTag`.`url_id` AND
`Url`.`id` = `PTag`.`url_id`)
WHERE
`Category`.`name` = 'some kategory' AND
`Url`.`name` LIKE
'%www.some-domain.com%'
*/
}
--
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
cake-php+unsubscr...@googlegroups.com For more options, visit this group at
http://groups.google.com/group/cake-php
Re: Self Join query
Oops clicked 'send' by accident.
So what I meant was :
Hah it works ? Great :)
Yes Containable Behavior is a very important one.
Start using it on all your model from the start of your project , it will
save you a lot of queries and refactoring of code afterwards.
Glad I could help,
Bert
On Tue, Sep 29, 2009 at 1:11 PM, Bert Van den Brande wrote:
> Ha
>
>
> On Tue, Sep 29, 2009 at 12:44 PM, hunny wrote:
>
>>
>> Thanks Bert,
>>
>> It works perfectly fine. This is exactly what I was looking for.
>> While going through the manual, I had completely left the Behaviors
>> Section. I think its high time that I go through it.
>>
>>
>> On Sep 28, 4:53 pm, Bert Van den Brande wrote:
>> > I typed this code out of the top of my head, but it might be worth a try
>> :
>> >
>> > $this->User->Behaviors->attach('Containable');
>> > $this->User->bindModel(array(
>> > 'hasOne' => array( // Model linking type is 'hasOne', this
>> > forces Cake to make joins
>> > 'UserB' => array( // User other name than 'User' to
>> avoid
>> > data arrays being mixed up in the result
>> > 'className' => 'User',
>> > 'foreignKey' => false, // Force conditions in query
>> as
>> > defined below
>> > 'type' => 'LEFT',
>> > 'conditions' => array('User.company_id =
>> > UserB.company_id'))
>> > )));
>> > $result = $this->User->find('all', array('conditions' =>
>> > array('User.id' => 2)));
>> >
>> > Friendly greetings,
>> > Bert
>> >
>> > On Mon, Sep 28, 2009 at 1:43 PM, Bert Van den Brande > >wrote:
>> >
>> > > I would use the Containable behavior here, combined with a custom
>> binding
>> > > that you attach on the fly.
>> >
>> > > Have no idea however how exactly to expres a self-join binding ... try
>> and
>> > > play around with it :)
>> >
>> > > On Mon, Sep 28, 2009 at 12:04 PM, hunny > >wrote:
>> >
>> > >> On Sep 28, 2:19 pm, Aivaras wrote:
>> > >> > Hey, take a look at this:
>> > >>http://voveris.eu/2009/09/05/left-join-with-cakephp/Iam sure you find
>> > >> this
>> > >> > handy.
>> >
>> > >> > On Mon, Sep 28, 2009 at 12:04, hunny
>> > >> wrote:
>> >
>> > >> > > Hi All,
>> >
>> > >> > > I am new to cakephp. I would like to execute the following SELF
>> Join
>> > >> > > query.
>> >
>> > >> > > SELECT B.* FROM `users` AS A LEFT JOIN `users` AS B
>> > >> > > ON A.id = 2 where A.company_id = B.company_id
>> >
>> > >> > > Could some one guide or refer some tutorials, on how to achieve
>> this
>> > >> > > in cake php.
>> >
>> > >> > > Thanks in advance
>> >
>> > >> Hi All,
>> >
>> > >> Bad me, I think should have explained my problem properly.
>> >
>> > >> Lets say following are the contents of my users table:
>> >
>> > >> id | name | company id
>> >
>> > >> 1 | abc | 123
>> > >> 2 | def | 123
>> > >> 3 | ghi | 124
>> > >> 4 | jkl | 123
>> >
>> > >> Now what I want is to retrieve, for a member (say 'abc') all the
>> > >> people who are working in the same company.
>> >
>> > >> Even though company_id is a foreign key, I am not interested in the
>> > >> data of that table. What you suggested above is how to get the data
>> > >> from the company table.
>> >
>> > >> The simple sql query if I want all the members working in same
>> company
>> > >> as id = 2 would be:
>> >
>> > >> SELECT B.* FROM `users` AS A LEFT JOIN `users` AS B
>> > >> ON A.id = 2 where A.company_id = B.company_id
>> >
>> > >> Since this includes JOIN with the table itself, I am not sure how to
>> > >> achieve using Association Type.
>> >
>> >
>> >>
>>
>
--~--~-~--~~~---~--~~
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+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~--~~~~--~~--~--~---
Re: Self Join query
Ha
On Tue, Sep 29, 2009 at 12:44 PM, hunny wrote:
>
> Thanks Bert,
>
> It works perfectly fine. This is exactly what I was looking for.
> While going through the manual, I had completely left the Behaviors
> Section. I think its high time that I go through it.
>
>
> On Sep 28, 4:53 pm, Bert Van den Brande wrote:
> > I typed this code out of the top of my head, but it might be worth a try
> :
> >
> > $this->User->Behaviors->attach('Containable');
> > $this->User->bindModel(array(
> > 'hasOne' => array( // Model linking type is 'hasOne', this
> > forces Cake to make joins
> > 'UserB' => array( // User other name than 'User' to avoid
> > data arrays being mixed up in the result
> > 'className' => 'User',
> > 'foreignKey' => false, // Force conditions in query
> as
> > defined below
> > 'type' => 'LEFT',
> > 'conditions' => array('User.company_id =
> > UserB.company_id'))
> > )));
> > $result = $this->User->find('all', array('conditions' =>
> > array('User.id' => 2)));
> >
> > Friendly greetings,
> > Bert
> >
> > On Mon, Sep 28, 2009 at 1:43 PM, Bert Van den Brande >wrote:
> >
> > > I would use the Containable behavior here, combined with a custom
> binding
> > > that you attach on the fly.
> >
> > > Have no idea however how exactly to expres a self-join binding ... try
> and
> > > play around with it :)
> >
> > > On Mon, Sep 28, 2009 at 12:04 PM, hunny >wrote:
> >
> > >> On Sep 28, 2:19 pm, Aivaras wrote:
> > >> > Hey, take a look at this:
> > >>http://voveris.eu/2009/09/05/left-join-with-cakephp/Iam sure you find
> > >> this
> > >> > handy.
> >
> > >> > On Mon, Sep 28, 2009 at 12:04, hunny
> > >> wrote:
> >
> > >> > > Hi All,
> >
> > >> > > I am new to cakephp. I would like to execute the following SELF
> Join
> > >> > > query.
> >
> > >> > > SELECT B.* FROM `users` AS A LEFT JOIN `users` AS B
> > >> > > ON A.id = 2 where A.company_id = B.company_id
> >
> > >> > > Could some one guide or refer some tutorials, on how to achieve
> this
> > >> > > in cake php.
> >
> > >> > > Thanks in advance
> >
> > >> Hi All,
> >
> > >> Bad me, I think should have explained my problem properly.
> >
> > >> Lets say following are the contents of my users table:
> >
> > >> id | name | company id
> >
> > >> 1 | abc | 123
> > >> 2 | def | 123
> > >> 3 | ghi | 124
> > >> 4 | jkl | 123
> >
> > >> Now what I want is to retrieve, for a member (say 'abc') all the
> > >> people who are working in the same company.
> >
> > >> Even though company_id is a foreign key, I am not interested in the
> > >> data of that table. What you suggested above is how to get the data
> > >> from the company table.
> >
> > >> The simple sql query if I want all the members working in same company
> > >> as id = 2 would be:
> >
> > >> SELECT B.* FROM `users` AS A LEFT JOIN `users` AS B
> > >> ON A.id = 2 where A.company_id = B.company_id
> >
> > >> Since this includes JOIN with the table itself, I am not sure how to
> > >> achieve using Association Type.
> >
> >
> >
>
--~--~-~--~~~---~--~~
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+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~--~~~~--~~--~--~---
Re: Self Join query
Thanks Bert,
It works perfectly fine. This is exactly what I was looking for.
While going through the manual, I had completely left the Behaviors
Section. I think its high time that I go through it.
On Sep 28, 4:53 pm, Bert Van den Brande wrote:
> I typed this code out of the top of my head, but it might be worth a try :
>
> $this->User->Behaviors->attach('Containable');
> $this->User->bindModel(array(
> 'hasOne' => array( // Model linking type is 'hasOne', this
> forces Cake to make joins
> 'UserB' => array( // User other name than 'User' to avoid
> data arrays being mixed up in the result
> 'className' => 'User',
> 'foreignKey' => false, // Force conditions in query as
> defined below
> 'type' => 'LEFT',
> 'conditions' => array('User.company_id =
> UserB.company_id'))
> )));
> $result = $this->User->find('all', array('conditions' =>
> array('User.id' => 2)));
>
> Friendly greetings,
> Bert
>
> On Mon, Sep 28, 2009 at 1:43 PM, Bert Van den Brande wrote:
>
> > I would use the Containable behavior here, combined with a custom binding
> > that you attach on the fly.
>
> > Have no idea however how exactly to expres a self-join binding ... try and
> > play around with it :)
>
> > On Mon, Sep 28, 2009 at 12:04 PM, hunny wrote:
>
> >> On Sep 28, 2:19 pm, Aivaras wrote:
> >> > Hey, take a look at this:
> >>http://voveris.eu/2009/09/05/left-join-with-cakephp/Iam sure you find
> >> this
> >> > handy.
>
> >> > On Mon, Sep 28, 2009 at 12:04, hunny
> >> wrote:
>
> >> > > Hi All,
>
> >> > > I am new to cakephp. I would like to execute the following SELF Join
> >> > > query.
>
> >> > > SELECT B.* FROM `users` AS A LEFT JOIN `users` AS B
> >> > > ON A.id = 2 where A.company_id = B.company_id
>
> >> > > Could some one guide or refer some tutorials, on how to achieve this
> >> > > in cake php.
>
> >> > > Thanks in advance
>
> >> Hi All,
>
> >> Bad me, I think should have explained my problem properly.
>
> >> Lets say following are the contents of my users table:
>
> >> id | name | company id
>
> >> 1 | abc | 123
> >> 2 | def | 123
> >> 3 | ghi | 124
> >> 4 | jkl | 123
>
> >> Now what I want is to retrieve, for a member (say 'abc') all the
> >> people who are working in the same company.
>
> >> Even though company_id is a foreign key, I am not interested in the
> >> data of that table. What you suggested above is how to get the data
> >> from the company table.
>
> >> The simple sql query if I want all the members working in same company
> >> as id = 2 would be:
>
> >> SELECT B.* FROM `users` AS A LEFT JOIN `users` AS B
> >> ON A.id = 2 where A.company_id = B.company_id
>
> >> Since this includes JOIN with the table itself, I am not sure how to
> >> achieve using Association Type.
>
>
--~--~-~--~~~---~--~~
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+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~--~~~~--~~--~--~---
Re: Self Join query
I typed this code out of the top of my head, but it might be worth a try :
$this->User->Behaviors->attach('Containable');
$this->User->bindModel(array(
'hasOne' => array( // Model linking type is 'hasOne', this
forces Cake to make joins
'UserB' => array( // User other name than 'User' to avoid
data arrays being mixed up in the result
'className' => 'User',
'foreignKey' => false, // Force conditions in query as
defined below
'type' => 'LEFT',
'conditions' => array('User.company_id =
UserB.company_id'))
)));
$result = $this->User->find('all', array('conditions' =>
array('User.id' => 2)));
Friendly greetings,
Bert
On Mon, Sep 28, 2009 at 1:43 PM, Bert Van den Brande wrote:
> I would use the Containable behavior here, combined with a custom binding
> that you attach on the fly.
>
> Have no idea however how exactly to expres a self-join binding ... try and
> play around with it :)
>
>
> On Mon, Sep 28, 2009 at 12:04 PM, hunny wrote:
>
>>
>>
>>
>> On Sep 28, 2:19 pm, Aivaras wrote:
>> > Hey, take a look at this:
>> http://voveris.eu/2009/09/05/left-join-with-cakephp/I am sure you find
>> this
>> > handy.
>> >
>> > On Mon, Sep 28, 2009 at 12:04, hunny
>> wrote:
>> >
>> > > Hi All,
>> >
>> > > I am new to cakephp. I would like to execute the following SELF Join
>> > > query.
>> >
>> > > SELECT B.* FROM `users` AS A LEFT JOIN `users` AS B
>> > > ON A.id = 2 where A.company_id = B.company_id
>> >
>> > > Could some one guide or refer some tutorials, on how to achieve this
>> > > in cake php.
>> >
>> > > Thanks in advance
>> >
>> >
>>
>> Hi All,
>>
>> Bad me, I think should have explained my problem properly.
>>
>> Lets say following are the contents of my users table:
>>
>> id | name | company id
>>
>> 1 | abc | 123
>> 2 | def | 123
>> 3 | ghi | 124
>> 4 | jkl | 123
>>
>> Now what I want is to retrieve, for a member (say 'abc') all the
>> people who are working in the same company.
>>
>> Even though company_id is a foreign key, I am not interested in the
>> data of that table. What you suggested above is how to get the data
>> from the company table.
>>
>> The simple sql query if I want all the members working in same company
>> as id = 2 would be:
>>
>> SELECT B.* FROM `users` AS A LEFT JOIN `users` AS B
>> ON A.id = 2 where A.company_id = B.company_id
>>
>> Since this includes JOIN with the table itself, I am not sure how to
>> achieve using Association Type.
>>
>> >>
>>
>
--~--~-~--~~~---~--~~
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+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~--~~~~--~~--~--~---
Re: Self Join query
I would use the Containable behavior here, combined with a custom binding that you attach on the fly. Have no idea however how exactly to expres a self-join binding ... try and play around with it :) On Mon, Sep 28, 2009 at 12:04 PM, hunny wrote: > > > > On Sep 28, 2:19 pm, Aivaras wrote: > > Hey, take a look at this: > http://voveris.eu/2009/09/05/left-join-with-cakephp/I am sure you find > this > > handy. > > > > On Mon, Sep 28, 2009 at 12:04, hunny wrote: > > > > > Hi All, > > > > > I am new to cakephp. I would like to execute the following SELF Join > > > query. > > > > > SELECT B.* FROM `users` AS A LEFT JOIN `users` AS B > > > ON A.id = 2 where A.company_id = B.company_id > > > > > Could some one guide or refer some tutorials, on how to achieve this > > > in cake php. > > > > > Thanks in advance > > > > > > Hi All, > > Bad me, I think should have explained my problem properly. > > Lets say following are the contents of my users table: > > id | name | company id > > 1 | abc | 123 > 2 | def | 123 > 3 | ghi | 124 > 4 | jkl | 123 > > Now what I want is to retrieve, for a member (say 'abc') all the > people who are working in the same company. > > Even though company_id is a foreign key, I am not interested in the > data of that table. What you suggested above is how to get the data > from the company table. > > The simple sql query if I want all the members working in same company > as id = 2 would be: > > SELECT B.* FROM `users` AS A LEFT JOIN `users` AS B > ON A.id = 2 where A.company_id = B.company_id > > Since this includes JOIN with the table itself, I am not sure how to > achieve using Association Type. > > > > --~--~-~--~~~---~--~~ 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+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~--~~~~--~~--~--~---
Re: Self Join query
On Sep 28, 2:19 pm, Aivaras wrote: > Hey, take a look at > this:http://voveris.eu/2009/09/05/left-join-with-cakephp/I am sure you find > this > handy. > > On Mon, Sep 28, 2009 at 12:04, hunny wrote: > > > Hi All, > > > I am new to cakephp. I would like to execute the following SELF Join > > query. > > > SELECT B.* FROM `users` AS A LEFT JOIN `users` AS B > > ON A.id = 2 where A.company_id = B.company_id > > > Could some one guide or refer some tutorials, on how to achieve this > > in cake php. > > > Thanks in advance > > Hi All, Bad me, I think should have explained my problem properly. Lets say following are the contents of my users table: id | name | company id 1 | abc | 123 2 | def | 123 3 | ghi | 124 4 | jkl | 123 Now what I want is to retrieve, for a member (say 'abc') all the people who are working in the same company. Even though company_id is a foreign key, I am not interested in the data of that table. What you suggested above is how to get the data from the company table. The simple sql query if I want all the members working in same company as id = 2 would be: SELECT B.* FROM `users` AS A LEFT JOIN `users` AS B ON A.id = 2 where A.company_id = B.company_id Since this includes JOIN with the table itself, I am not sure how to achieve using Association Type. --~--~-~--~~~---~--~~ 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+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~--~~~~--~~--~--~---
Re: Self Join query
Hey, take a look at this: http://voveris.eu/2009/09/05/left-join-with-cakephp/ I am sure you find this handy. On Mon, Sep 28, 2009 at 12:04, hunny wrote: > > Hi All, > > I am new to cakephp. I would like to execute the following SELF Join > query. > > SELECT B.* FROM `users` AS A LEFT JOIN `users` AS B > ON A.id = 2 where A.company_id = B.company_id > > Could some one guide or refer some tutorials, on how to achieve this > in cake php. > > Thanks in advance > > > > --~--~-~--~~~---~--~~ 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+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~--~~~~--~~--~--~---
Self Join query
Hi All, I am new to cakephp. I would like to execute the following SELF Join query. SELECT B.* FROM `users` AS A LEFT JOIN `users` AS B ON A.id = 2 where A.company_id = B.company_id Could some one guide or refer some tutorials, on how to achieve this in cake php. Thanks in advance --~--~-~--~~~---~--~~ 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+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~--~~~~--~~--~--~---
Re: SQL Error: 1054 - Join query between 3 tables - HABM / dynamic binding
Thanks. It is only one feature per product. I removed hasOne association from 'Product' table I already have the foreign key in the 'Product' table 'feature_id' for which I have hasOne association in the 'Feature' table. I still get the same error: $sql= "SELECT `Feature1`.`id`, `Feature1`.`name`, `Feature1`.`description`, ... FROM `features` AS `Feature1` WHERE `Feature1`.`id`=`Product`.`feature_id` " $error = "1054: Unknown column 'Product.feature_id' in 'where clause'" $out= null I could just get 'all' and foreach it through while filtering everything, but I trust there is a way to do this smartly with find() in cakephp. Thanks. --~--~-~--~~~---~--~~ 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+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~--~~~~--~~--~--~---
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, projectmayhe...@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+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~--~~~~--~~--~--~---
SQL Error: 1054 - Join query between 3 tables - HABM / dynamic binding
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+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~--~~~~--~~--~--~---
Re: Join query
This looks like a job for model associations.
http://book.cakephp.org/view/78/Associations-Linking-Models-Together
Basically set Parent to have many Entity (Or what every relationship
is the correct one for your case)
On Apr 6, 4:07 pm, Manisha wrote:
> Hello All,
>
> Here is the Controller
>
> class userController extends AppController {
>
> var $name='User';
>
> }//This is controller having model User
>
> and I have to make a join query between two models 1)parent 2)entitie
>
> for example:
>
> select * from tbl_parents a, tbl_entities b where a.parent_id =
> b.entity_id and b.entity_relationship='gallery' and a.deleted='no' and
> b.entity_id_1='49d7c6b5-7458-42b2-8d45-18830e7ba4d1' order by
> a.entity_id ASC
>
> I am running this query manually in userController
>
> Please give me some idea about how to make a join query in cakePHP
>
> Manisha.
--~--~-~--~~~---~--~~
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+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~--~~~~--~~--~--~---
Join query
Hello All,
Here is the Controller
class userController extends AppController {
var $name='User';
}//This is controller having model User
and I have to make a join query between two models 1)parent 2)entitie
for example:
select * from tbl_parents a, tbl_entities b where a.parent_id =
b.entity_id and b.entity_relationship='gallery' and a.deleted='no' and
b.entity_id_1='49d7c6b5-7458-42b2-8d45-18830e7ba4d1' order by
a.entity_id ASC
I am running this query manually in userController
Please give me some idea about how to make a join query in cakePHP
Manisha.
--~--~-~--~~~---~--~~
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+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~--~~~~--~~--~--~---
join query
I am beginner in cakephp. How to write join query? I am declare in
location model page " var $hasAndBelongsToMany =
array('BusinessMaster'); " and business master model page "var
$hasAndBelongsToMany = array('Location');".
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups "Cake
PHP" group.
To post to this group, send email to cake-php@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~--~~~~--~~--~--~---
