Re: slightly OT: need an unusual find condition (or possible custom query)
Thanks AD, I did have an nagging suspicion that this would require something like that with temporary tables and things. What I did for now (until I do some performance testing on it and in case anyone find it useful) was: 1. A deleteAll using a NOT condition. Deletes anything that does not match my list of ids. 2. A find(list) of what is left. 3. Loop my list of ids and test each against the ones found in the database using in_array() 4. A saveAll to save any ids that were new (not in the array). As you can see quite a few steps but is if reasonable efficient for now. But, a big thank you for the example with the temp table, I suspect it will come in handy quite soon. /Martin On Jan 26, 7:56 pm, AD7six andydawso...@gmail.com wrote: On Jan 26, 2:35 pm, Martin Westin martin.westin...@gmail.com wrote: Hi, I am in need of an unusual query that I find it hard to search for on Google or MySQL.com since all the terms I think of are very common ones. Hopefully someone reading this can guide me in the right direction. I have a series of values in an array. I can find out which records have matching values by passing this array as a condition to get a WHERE somefield IN (value1,value2) clause. I can also find which records have non-matching fields by using a WHERE NOT(...) clause. Finding the orphans in the database so to speak. Of-course I want something else. I want to find out which values in my array do not match a record in the database. Finding the orphans in my array so to speak. I have not figured out how to do this without using the IN clause and then comparing the results. Since this is expensive (looping arrays take time) I really wanted to find an SQL- way that would be less expensive. SELECT VALUES IN MY ARRAY THAT DO NOT FIND ANY MATCHING RECORD - sort of. Any guidance would be appreciated /Martin There's probably a more elegant way, This'd work: DROP TABLE `tmp`; CREATE TABLE `tmp` ( `id` INT( 11 ) NULL ) ENGINE = MEMORY; INSERT INTO `tmp` ( `id` ) VALUES (1),(2),(3),(4),(5); # The values you want to search for select `tmp`.`id` from `mytable` RIGHT JOIN `tmp` on `mytable`.`somefield` = `tmp`.`id` WHERE `mytable`.`somefiled` IS NULL; Anything returned is a `mytable`.`somefield` that doesn't exist. AD --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
slightly OT: need an unusual find condition (or possible custom query)
Hi, I am in need of an unusual query that I find it hard to search for on Google or MySQL.com since all the terms I think of are very common ones. Hopefully someone reading this can guide me in the right direction. I have a series of values in an array. I can find out which records have matching values by passing this array as a condition to get a WHERE somefield IN (value1,value2) clause. I can also find which records have non-matching fields by using a WHERE NOT(...) clause. Finding the orphans in the database so to speak. Of-course I want something else. I want to find out which values in my array do not match a record in the database. Finding the orphans in my array so to speak. I have not figured out how to do this without using the IN clause and then comparing the results. Since this is expensive (looping arrays take time) I really wanted to find an SQL- way that would be less expensive. SELECT VALUES IN MY ARRAY THAT DO NOT FIND ANY MATCHING RECORD - sort of. Any guidance would be appreciated /Martin --~--~-~--~~~---~--~~ 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: slightly OT: need an unusual find condition (or possible custom query)
I think you'll need to loop through the array with find('count').
On Mon, Jan 26, 2009 at 8:35 AM, Martin Westin
martin.westin...@gmail.com wrote:
Hi,
I am in need of an unusual query that I find it hard to search for on
Google or MySQL.com since all the terms I think of are very common
ones. Hopefully someone reading this can guide me in the right
direction.
I have a series of values in an array. I can find out which records
have matching values by passing this array as a condition to get a
WHERE somefield IN (value1,value2) clause.
I can also find which records have non-matching fields by using a
WHERE NOT(...) clause. Finding the orphans in the database so to
speak.
Of-course I want something else. I want to find out which values in my
array do not match a record in the database. Finding the orphans in my
array so to speak. I have not figured out how to do this without using
the IN clause and then comparing the results. Since this is
expensive (looping arrays take time) I really wanted to find an SQL-
way that would be less expensive.
SELECT VALUES IN MY ARRAY THAT DO NOT FIND ANY MATCHING RECORD -
sort of.
Any guidance would be appreciated
/Martin
--~--~-~--~~~---~--~~
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: slightly OT: need an unusual find condition (or possible custom query)
On Jan 26, 2:35 pm, Martin Westin martin.westin...@gmail.com wrote: Hi, I am in need of an unusual query that I find it hard to search for on Google or MySQL.com since all the terms I think of are very common ones. Hopefully someone reading this can guide me in the right direction. I have a series of values in an array. I can find out which records have matching values by passing this array as a condition to get a WHERE somefield IN (value1,value2) clause. I can also find which records have non-matching fields by using a WHERE NOT(...) clause. Finding the orphans in the database so to speak. Of-course I want something else. I want to find out which values in my array do not match a record in the database. Finding the orphans in my array so to speak. I have not figured out how to do this without using the IN clause and then comparing the results. Since this is expensive (looping arrays take time) I really wanted to find an SQL- way that would be less expensive. SELECT VALUES IN MY ARRAY THAT DO NOT FIND ANY MATCHING RECORD - sort of. Any guidance would be appreciated /Martin There's probably a more elegant way, This'd work: DROP TABLE `tmp`; CREATE TABLE `tmp` ( `id` INT( 11 ) NULL ) ENGINE = MEMORY; INSERT INTO `tmp` ( `id` ) VALUES (1),(2),(3),(4),(5); # The values you want to search for select `tmp`.`id` from `mytable` RIGHT JOIN `tmp` on `mytable`.`somefield` = `tmp`.`id` WHERE `mytable`.`somefiled` IS NULL; Anything returned is a `mytable`.`somefield` that doesn't exist. AD --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
