Re: custom SQL query - all values IN() bust be AND
I'm not really clear on what it is you are trying to fetch. Are you
trying to restrict your results by the associated values?
The results you show can be fetched using a normal find('all')
assuming your models are correct. Just add another select count to the
conditions like:
$this->Advert->find('all', array('conditions' => array(
'AND' => array(
'(select count(1) FROM images WHERE
adverts.id = images.advert_id) > 0
'(select count(1) FROM options where
adverts.id = options.advert_id AND options.option IN (3, 4)) = 2'
)));
Or better, do a find('count') for the related data to find out if you
have the right count before you even do the fetch for the Advert:
$this->Advert->find('count', array('conditions' => array
('Options.advert_id' => $advert.id,
'Options.option' => array(3,4;
Would give you a count of 2 if there are two matches.
On Jan 5, 7:08 am, dev wrote:
> You wrote good solution, but if I need more than one model (HABTM)
> where i can search for results, like:
>
> Advert HABTM Option
> Advert HABTM Exta
>
> example:
> [0] => Array
> (
> [Advert] => Array
> (
> [id] => 2
> [code] => 54
> [created] => 2008-12-19 11:17:30
> )
>
> [Option] => Array
> (
> [0] => Array
> (
> [id] => 1
> [advert_id] => 2
> [option] => 3
> )
> [1] => Array
> (
> [id] => 2
> [advert_id] => 2
> [option] => 4
> )
>
> )
>
> [Extra] => Array
> (
> [0] => Array
> (
> [id] => 5
> [advert_id] => 2
> [option] => 8
> )
>
> [1] => Array
> (
> [id] => 7
> [advert_id] => 2
> [option] => 4
> )
>
> )
>
> )
>
> How about that?
>
> On Dec 24 2008, 2:04 pm, Smelly_Eddie wrote:
>
> > dev if thats the case than my last post should asswer your questions.
>
> > On Dec 24, 3:35 am, dev wrote:
>
> > > I will give an example:
>
> > > Advert hasMany Option
> > > I need to find Adverts, where its Options are 3 and 4 values. When
> > > using IN, it will return results with 3, or, 4, or both 3 and 4. But i
> > > want only with both values.
>
> > > Like:
> > > [0] => Array
> > > (
> > > [Advert] => Array
> > > (
> > > [id] => 2
> > > [code] => 54
> > > [created] => 2008-12-19 11:17:30
> > > )
>
> > > [Option] => Array
> > > (
> > > [0] => Array
> > > (
> > > [id] => 1
> > > [advert_id] => 2
> > > [option] => 3
> > > )
> > > [1] => Array
> > > (
> > > [id] => 2
> > > [advert_id] => 2
> > > [option] => 4
> > > )
>
> > > )
>
> > > )
>
> > > On Dec 24, 12:35 am, Smelly_Eddie wrote:
>
> > > > How can the same field have multiple values?
> > > > -it can be = '3'
> > > > -it can be= '4''
>
> > > > Otherwise maybe your are just concatenating multiple variables into
> > > > one, e.g.;
> > > > -it can be='3,4'
> > > > which probably should be evaluated for best practices..
>
> > > > You would not use IN in such a case.
>
> > > > If the are always ordered numerically you might use;
> > > > AND options.option like '%3,4%'
> > > > (assuming there are higher or lower values (2,..,5))
>
> > > > If they are not always order and could be 4,3 or 4,5,3 then you might
> > > > try;
> > > > AND options.option like '%3%' AND options.option like '%4%
>
> > > > On Dec 22, 11:21 am, dev wrote:
>
> > > > > i'm building custom SQl query and need a little help.
>
> > > > > query:
> > > > > select distinct adverts.id, adverts.code from adverts left join images
> > > > > on (images.advert_id = adverts.id) left join options on
> > > > > (options.advert_id = adverts.id) where (SELECT count(1) FROM images
> > > > > WHERE adverts.id = images.advert_id) > 0 AND options.option IN (3, 4);
>
> > > > > but i want to get all Advert records, where options.option has all
> > > > > these values (3,4). W
Re: custom SQL query - all values IN() bust be AND
You wrote good solution, but if I need more than one model (HABTM) where i can search for results, like: Advert HABTM Option Advert HABTM Exta example: [0] => Array ( [Advert] => Array ( [id] => 2 [code] => 54 [created] => 2008-12-19 11:17:30 ) [Option] => Array ( [0] => Array ( [id] => 1 [advert_id] => 2 [option] => 3 ) [1] => Array ( [id] => 2 [advert_id] => 2 [option] => 4 ) ) [Extra] => Array ( [0] => Array ( [id] => 5 [advert_id] => 2 [option] => 8 ) [1] => Array ( [id] => 7 [advert_id] => 2 [option] => 4 ) ) ) How about that? On Dec 24 2008, 2:04 pm, Smelly_Eddie wrote: > dev if thats the case than my last post should asswer your questions. > > On Dec 24, 3:35 am, dev wrote: > > > I will give an example: > > > Advert hasMany Option > > I need to find Adverts, where its Options are 3 and 4 values. When > > using IN, it will return results with 3, or, 4, or both 3 and 4. But i > > want only with both values. > > > Like: > > [0] => Array > > ( > > [Advert] => Array > > ( > > [id] => 2 > > [code] => 54 > > [created] => 2008-12-19 11:17:30 > > ) > > > [Option] => Array > > ( > > [0] => Array > > ( > > [id] => 1 > > [advert_id] => 2 > > [option] => 3 > > ) > > [1] => Array > > ( > > [id] => 2 > > [advert_id] => 2 > > [option] => 4 > > ) > > > ) > > > ) > > > On Dec 24, 12:35 am, Smelly_Eddie wrote: > > > > How can the same field have multiple values? > > > -it can be = '3' > > > -it can be= '4'' > > > > Otherwise maybe your are just concatenating multiple variables into > > > one, e.g.; > > > -it can be='3,4' > > > which probably should be evaluated for best practices.. > > > > You would not use IN in such a case. > > > > If the are always ordered numerically you might use; > > > AND options.option like '%3,4%' > > > (assuming there are higher or lower values (2,..,5)) > > > > If they are not always order and could be 4,3 or 4,5,3 then you might > > > try; > > > AND options.option like '%3%' AND options.option like '%4% > > > > On Dec 22, 11:21 am, dev wrote: > > > > > i'm building custom SQl query and need a little help. > > > > > query: > > > > select distinct adverts.id, adverts.code from adverts left join images > > > > on (images.advert_id = adverts.id) left join options on > > > > (options.advert_id = adverts.id) where (SELECT count(1) FROM images > > > > WHERE adverts.id = images.advert_id) > 0 AND options.option IN (3, 4); > > > > > but i want to get all Advert records, where options.option has all > > > > these values (3,4). When i'm using IN(), these values are with OR > > > > operator, how to set AND? --~--~-~--~~~---~--~~ 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: custom SQL query - all values IN() bust be AND
dev if thats the case than my last post should asswer your questions. On Dec 24, 3:35 am, dev wrote: > I will give an example: > > Advert hasMany Option > I need to find Adverts, where its Options are 3 and 4 values. When > using IN, it will return results with 3, or, 4, or both 3 and 4. But i > want only with both values. > > Like: > [0] => Array > ( > [Advert] => Array > ( > [id] => 2 > [code] => 54 > [created] => 2008-12-19 11:17:30 > ) > > [Option] => Array > ( > [0] => Array > ( > [id] => 1 > [advert_id] => 2 > [option] => 3 > ) > [1] => Array > ( > [id] => 2 > [advert_id] => 2 > [option] => 4 > ) > > ) > > ) > > On Dec 24, 12:35 am, Smelly_Eddie wrote: > > > How can the same field have multiple values? > > -it can be = '3' > > -it can be= '4'' > > > Otherwise maybe your are just concatenating multiple variables into > > one, e.g.; > > -it can be='3,4' > > which probably should be evaluated for best practices.. > > > You would not use IN in such a case. > > > If the are always ordered numerically you might use; > > AND options.option like '%3,4%' > > (assuming there are higher or lower values (2,..,5)) > > > If they are not always order and could be 4,3 or 4,5,3 then you might > > try; > > AND options.option like '%3%' AND options.option like '%4% > > > On Dec 22, 11:21 am, dev wrote: > > > > i'm building custom SQl query and need a little help. > > > > query: > > > select distinct adverts.id, adverts.code from adverts left join images > > > on (images.advert_id = adverts.id) left join options on > > > (options.advert_id = adverts.id) where (SELECT count(1) FROM images > > > WHERE adverts.id = images.advert_id) > 0 AND options.option IN (3, 4); > > > > but i want to get all Advert records, where options.option has all > > > these values (3,4). When i'm using IN(), these values are with OR > > > operator, how to set AND? --~--~-~--~~~---~--~~ 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: custom SQL query - all values IN() bust be AND
I will give an example: Advert hasMany Option I need to find Adverts, where its Options are 3 and 4 values. When using IN, it will return results with 3, or, 4, or both 3 and 4. But i want only with both values. Like: [0] => Array ( [Advert] => Array ( [id] => 2 [code] => 54 [created] => 2008-12-19 11:17:30 ) [Option] => Array ( [0] => Array ( [id] => 1 [advert_id] => 2 [option] => 3 ) [1] => Array ( [id] => 2 [advert_id] => 2 [option] => 4 ) ) ) On Dec 24, 12:35 am, Smelly_Eddie wrote: > How can the same field have multiple values? > -it can be = '3' > -it can be= '4'' > > Otherwise maybe your are just concatenating multiple variables into > one, e.g.; > -it can be='3,4' > which probably should be evaluated for best practices.. > > You would not use IN in such a case. > > If the are always ordered numerically you might use; > AND options.option like '%3,4%' > (assuming there are higher or lower values (2,..,5)) > > If they are not always order and could be 4,3 or 4,5,3 then you might > try; > AND options.option like '%3%' AND options.option like '%4% > > On Dec 22, 11:21 am, dev wrote: > > > i'm building custom SQl query and need a little help. > > > query: > > select distinct adverts.id, adverts.code from adverts left join images > > on (images.advert_id = adverts.id) left join options on > > (options.advert_id = adverts.id) where (SELECT count(1) FROM images > > WHERE adverts.id = images.advert_id) > 0 AND options.option IN (3, 4); > > > but i want to get all Advert records, where options.option has all > > these values (3,4). When i'm using IN(), these values are with OR > > operator, how to set AND? --~--~-~--~~~---~--~~ 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: custom SQL query - all values IN() bust be AND
I have a weird feeling that this post might be related to what you are trying to accomplish: http://teknoid.wordpress.com/2008/08/06/habtm-and-join-trickery-with-cakephp/ Well, hopefully it does help somehow ;) On Dec 22, 11:21 am, dev wrote: > i'm building custom SQl query and need a little help. > > query: > select distinct adverts.id, adverts.code from adverts left join images > on (images.advert_id = adverts.id) left join options on > (options.advert_id = adverts.id) where (SELECT count(1) FROM images > WHERE adverts.id = images.advert_id) > 0 AND options.option IN (3, 4); > > but i want to get all Advert records, where options.option has all > these values (3,4). When i'm using IN(), these values are with OR > operator, how to set AND? --~--~-~--~~~---~--~~ 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: custom SQL query - all values IN() bust be AND
How can the same field have multiple values? -it can be = '3' -it can be= '4'' Otherwise maybe your are just concatenating multiple variables into one, e.g.; -it can be='3,4' which probably should be evaluated for best practices.. You would not use IN in such a case. If the are always ordered numerically you might use; AND options.option like '%3,4%' (assuming there are higher or lower values (2,..,5)) If they are not always order and could be 4,3 or 4,5,3 then you might try; AND options.option like '%3%' AND options.option like '%4% On Dec 22, 11:21 am, dev wrote: > i'm building custom SQl query and need a little help. > > query: > select distinct adverts.id, adverts.code from adverts left join images > on (images.advert_id = adverts.id) left join options on > (options.advert_id = adverts.id) where (SELECT count(1) FROM images > WHERE adverts.id = images.advert_id) > 0 AND options.option IN (3, 4); > > but i want to get all Advert records, where options.option has all > these values (3,4). When i'm using IN(), these values are with OR > operator, how to set AND? --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
