[carlosfariaMember]

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:

PHP

$recipes = new Pod('recipes'); $recipes->findRecords('rand()', -1, 'ingredients.name <> &quot;vinegar&quot;');

1 2	$recipes = new Pod('recipes');         $recipes->findRecords('rand()', -1, 'ingredients.name <> &quot;vinegar&quot;');

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:

PHP

$recipes = new Pod('recipes'); $recipes->findRecords('rand()', -1, 'ingredients.name = &quot;vinegar&quot;');

1 2	$recipes = new Pod('recipes');         $recipes->findRecords('rand()', -1, 'ingredients.name = "vinegar"');

Any help will be very appreciated.

[sc0ttkclarkKeymaster Post count: 803]

Try using != instead of <>

[carlosfariaMember]

Thanks Scott, but I already tried that and I got the same result. When I query with

PHP

$recipes->findRecords('rand()', -1, 'ingredients.name = &quot;vinegar&quot;');

1	$recipes->findRecords('rand()', -1, 'ingredients.name = "vinegar"');

the results are two rows but when I query with:

PHP

$recipes->findRecords('rand()', -1, 'ingredients.name != &quot;vinegar&quot;');

1	$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:

PHP

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 != &quot;vinegar&quot; ) ORDER BY rand()

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

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!!

[sc0ttkclarkKeymaster Post count: 803]

I noticed "vinegar" is lowercase, is that the way it’s stored in name or are you thinking of the ‘slug’ field?

[carlosfariaMember]

it’s all lowercase. The name and also the slug…

[chris.pilkoMember]

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

[carlosfariaMember]

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!!

[carlosfariaMember]

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…

[sc0ttkclarkKeymaster Post count: 803]

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.

[Author]

Posts