Arrrgh… help, please!

Resolved CodeMonkey613
(@codemonkey613)

11 years, 9 months ago
Been stuck on this over two hours. Please, someone help!!!

1) First problem: I created a pod “products” as a “Custom Post Type”. Has a field “product_price” that is “Currency” (field type). When I “order by”, it behaves like text, ordering like: $0.00, $1000, $200, $300, $5.
```
$products = pods('products', array(
       'page' => 1,
       'limit' => 10,
       'orderby' => 'product_price ASC',
       'where' => "post_status = 'publish'"
    ));
```
2) Second problem: I get error when searching by keywords. I have another field called “product_gender”, it’s a “Relationship” (field type) as “Simple Custom Defined List” (options are: Unknown, Male, Female). When I add “where clause”, SQL throws error: “Unknown column ‘product_gender’ in ‘where clause’. How am I supposed to do this correctly?
```
$products = pods('products', array(
       'page' => 1,
       'limit' => 10,
       'orderby' => 'ID ASC',
       'where' => "post_status = 'publish' AND (post_title like '%male%' AND product_gender like '%male%')"
    ));
```

Viewing 7 replies - 1 through 7 (of 7 total)

Plugin Contributor pglewis
(@pglewis)

11 years, 9 months ago

try: CAST(product_price AS DECIMAL) in your orderby

Post meta is always stored as a string since meta_value is a longtext field in wp_postmeta. Casting it to a decimal type before the sort should work around that.
Thread Starter CodeMonkey613
(@codemonkey613)

11 years, 9 months ago
Hi pglewis, thank you for the quick response. I tried ABS() before and couldn’t get it working. Upon your suggestion, I tried CAST() and got an error. But with minor modification I got it working:
```
CAST(product_price.meta_value AS DECIMAL)
```
Do you maybe have suggestion for my “second problem” too? Thanks.
Plugin Contributor pglewis
(@pglewis)

11 years, 9 months ago

On problem 2: I think it should be product_gender.meta_value

There is a very handy chart for these situations here:

http://pods.io/docs/code/pods/find/

The first column, 4th row of the table at the bottom of the page covers your scenario.

Thread Starter CodeMonkey613
(@codemonkey613)

11 years, 9 months ago

Hi pglewis, this case it doesn’t seem to help adding “meta_value”. It no longer throws the error, but it returns 0 results. Could it be because the field is a “Relationship / Simple (custom defined list)”?

I’ve got it filtering the “post_title” using keywords correctly. But it won’t recognize the contents of this “product_gender” field.

Plugin Contributor pglewis
(@pglewis)

11 years, 9 months ago

I’m not sure right off the bat. Try adding ?pods_debug_sql=1 to the URL (or &pods_debug_sql=1 if there are already URL params) to see the exact query Pods is running. That should give some insight.

Thread Starter CodeMonkey613
(@codemonkey613)

11 years, 9 months ago

Awesome, that helped a lot, thanks! Turns out that because I was using the query variable $search, pods was automatically using that to query the post_title, resulting in a SQL statement of “product_gender like ‘%male%’ AND post_title like ‘%male%'”. So I was getting 0 results because there is no point at which gender AND title both have the keyword.

So I just needed to change the variable from $search to $keywords so that pods would stop writing the $where statement automatically.

I also notice pods handles “post_status IN (‘publish’)” by itself, so I was needlessly adding that a second time.

Thanks so much! Took 4 hours but can finally relax lol 🙂

Plugin Contributor pglewis
(@pglewis)

11 years, 9 months ago

Glad you were able to get it ironed out. That URL param is definitely handy when you start troubleshooting more complex queries.

Viewing 7 replies - 1 through 7 (of 7 total)

The topic ‘Arrrgh… help, please!’ is closed to new replies.

Tags