Not equal, multi-select pick column
November 22, 2010 at 9:44 am #164720
I have a recipes pod that is using another pod, ingredients, as a multi-select pick.
What i want is select the recipes that doesn’t have ‘vinegar’ as one of its ingredients.
I’m using this code to select data in my pod:12$recipes = new Pod('recipes');$recipes->findRecords('rand()', -1, 'ingredients.name <> "vinegar"');
But it selects all the recipes, the ones with ‘vinegar’ selected an the ones without…
In fact, when i search for the recipes that have ‘vinegar’ as ingredient, using this code works:12$recipes = new Pod('recipes');$recipes->findRecords('rand()', -1, 'ingredients.name = "vinegar"');
Any help will be very appreciated.November 22, 2010 at 5:30 pm #164721
Try using != instead of <>November 22, 2010 at 5:54 pm #164722
Thanks Scott, but I already tried that and I got the same result. When I query with
the results are two rows but when I query with:
the results include that two rows.
Then, this is the way to do it, right?
Maybe I should change the name of the column to be different from the table…
Any other idea?
The SQL I get is:123456789101112131415161718SELECTSQL_CALC_FOUND_ROWS DISTINCT t.*, p.id AS pod_id, p.created, p.modifiedFROM@wp_pod pLEFT JOIN@wp_pod_rel r1 ON r1.field_id = 17 AND r1.pod_id = p.idLEFT JOIN`@wp_pod_tbl_ingredients` `ingredients` ON `ingredients`.id = r1.tbl_row_idINNER JOIN`@wp_pod_tbl_recipes` t ON t.id = p.tbl_row_idWHEREp.datatype = 6AND ( ingredients.name != "vinegar" )ORDER BY rand()
In fact, if I use this SQL code directly in my database with phpMyAdmin, the results are 6 rows with the ones with the ingredient ‘vinegar’ included…
Hope this is not a bug!!November 22, 2010 at 5:56 pm #164723
I noticed "vinegar" is lowercase, is that the way it’s stored in name or are you thinking of the ‘slug’ field?November 22, 2010 at 6:12 pm #164724
it’s all lowercase. The name and also the slug…November 22, 2010 at 6:40 pm #164725
It’s not a bug: It’s a logic problem akin to the old brain teaser "I’ve got two coins together worth 30 cents in my pocket, and one of them is not a quarter." The answer being that one of them is a nickel, and the other one is a quarter.
You’ve got a salad dressing with oil and vinegar, therefore one of your ingredients is not vinegar, so the query returns the row.
You need a WHERE NOT EXISTS subquery to find where none of the ingredients in that recipe is vinegar. I played with it a little, but I’ve run out of time for now. MySQL doc page is here: http://dev.mysql.com/doc/refman/5.0/en/exists-and-not-exists-subqueries.htmlNovember 23, 2010 at 9:28 am #164726
Great Chris, I knew something like this was the problem, because phpMyAdmin returns the same as Pods.
Now my problem is that I’m very bad at such SQL statements, and moreover, to have them working with Pods.
Should I put that WHERE NOT EXISTS subquery as a parameter in my findRecords call?
Please, enlight me, Master!!November 24, 2010 at 10:03 am #164727
Still no luck. Anyone can explain me the way to have a WHERE NOT EXISTS subquery as a parameter in my findRecords call?
I’m a bit frustrated with this…November 24, 2010 at 3:33 pm #164728
You must be logged in to reply to this topic.