Not equal, multi-select pick column
Hi all,
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:
$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:
$recipes = new Pod('recipes');
$recipes->findRecords('rand()', -1, 'ingredients.name = "vinegar"');
Any help will be very appreciated.
8 Answers
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.html
Try using != instead of <>
Send access to your site to contact@podscms.org and I'll take a look at it and see if I can fix it for you.
I noticed "vinegar" is lowercase, is that the way it's stored in name or are you thinking of the 'slug' field?
it's all lowercase. The name and also the slug...
Thanks Scott, but I already tried that and I got the same result. When I query with
$recipes->findRecords('rand()', -1, 'ingredients.name = "vinegar"');
the results are two rows but when I query with:
$recipes->findRecords('rand()', -1, 'ingredients.name != "vinegar"');
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:
SELECT
SQL_CALC_FOUND_ROWS DISTINCT t.*, p.id AS pod_id, p.created, p.modified
FROM
@wp_pod p
LEFT JOIN
@wp_pod_rel r1 ON r1.field_id = 17 AND r1.pod_id = p.id
LEFT JOIN
`@wp_pod_tbl_ingredients` `ingredients` ON `ingredients`.id = r1.tbl_row_id
INNER JOIN
`@wp_pod_tbl_recipes` t ON t.id = p.tbl_row_id
WHERE
p.datatype = 6
AND ( 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!! :D
edited Nov 22 '10 at 6:19
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!!
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...
