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+unsubscribe@googlegroups.com
For more options, visit this group at http://groups.google.com/group/cake-php?hl=en
-~----------~----~----~----~------~----~------~--~---