Outputting pods – conditionals?

where="category.name IN( 'HB', 'other' )" should do it.

Thank you! I had looked around in the Pods documentation for a while and did not find this type of conditional statement.

Is it similarly possible to combine this logic using AND:

where=”category.name IN( ‘HB’, ‘other’ )” AND where!=”category.name IN( ‘different’ )”

Hi @wpress2010

This is mostly plain SQL, and yes you can combine statements;

Example:
where=”category.name IN ( 'HB', 'other' ) AND category.name NOT IN ( 'different' )

https://www.w3schools.com/sql/sql_in.asp

Cheers, Jory

Thank you. I tried this statement, and it does not correctly recognize the NOT IN category part of the code.

Here’s the way it is set up:

The Pods can have multiple categories. So, as an example, a Pod is in the categories ‘HB’ ‘other’ and ‘different’. So the statement should NOT return this Pod in the results. But it does.

Code:

[pods name=”property” where=”category.name IN ( ‘HB’ ) AND category.name NOT IN ( ‘Sold’ )” template=”HB For Sale” orderby=”post_date DESC”]

Conditional statements when using a table join in SQL can be a little tricky. In this case, you can take out the AND category.name NOT IN ( 'Sold' ) part because the first category.name IN ( 'HB' ) already excludes categories not named HB.

Maybe a good thing to figure out here is which properties exactly you want to show, maybe the where you are creating isn’t matching what you want to do exactly.

Thank you.

The logic is: a Pod can belong to many categories. I want to display those that are simultaneously in the category of ‘HB’ but also are NOT in the category of ‘Sold’

So, f’rinstance, there are 6 Pods who have the category of ‘HB’

Of these 6 Pods, only 3 do NOT also have the category of ‘Sold’

So, I want to display those 3 only.

Unfortunately, MySQL does not have the ability to perform this kind of logic in a query when a joined table has multiple matching rows.

Your best bet may be to write your own shortcode with PHP logic to first find the records that you want to match for the one category, then run another query to get those records with t.ID IN ( ... ) of those IDs and limit that query by those without the other category.

It’s all a bit complicated but that’s unfortunately what we have to work with here.

I added a feature request for this at https://github.com/pods-framework/pods/issues/6343

Thank you. I think that there is perhaps some kind of problem with my thinking here. If this were an ordinary SQL query, it would be expressed something like this:

SELECT *
FROM property
WHERE (category = ‘HB’ AND category <> ‘Sold’)

I haven’t gone into the db to look at the tables. It seems as though the basic problem is that a Pod can have more than one category assigned to it.

After a bit more experimentation, I find that the following code works for my exact purposes:

[pods name=”property” where=”category.name= ‘HB’ AND where=”category.name= ‘For Sale’ ” template=”HB For Sale” orderby=”post_date DESC”]

I don’t think that this is in any way an unusual or complicated query. Still thinking about why this strategy works – where others that seemed pretty much the same thing – don’t.

Hi @wpress2010

I haven’t gone into the db to look at the tables. It seems as though the basic problem is that a Pod can have more than one category assigned to it.

This isn’t a Pods feature, it’s how WordPress taxonomies work.

Combining IN and NOT IN is the only really stable solution here, unfortunately it’s not available through Pods at this moment.
You can use WP Query to do the same though.

The last query you’ve posted is actually an error. You cannot use where=" twice in the same code and especially not within the same param. I think that in this case the whole For Sale part is being ignored.

Cheers, Jory

Thanks, Jory. I checked it again, and it indeed is working as I described.
In my particular test, there are 8 Properties that have the Catgeory of ‘HB’ and of those 8, only 3 also have the Category of ‘For Sale’ The code returns just the 3.

I did have a look at WP Query, and it seems pretty straightforward.