Question 165816 in Wordpress Question posted in
The official Wordpress documentation can be found here.

Can I select & cast in a WHERE statement for a WP-MySQL issue?

I have this database table:

post_id | key    | value
297     | title  | test
297     | price  | 50
250     | title  | test2
250     | price  | 75
100     | title  | test3
100     | price  | 100

The question is: how can I get all (grouped) post_ids with a where on price and ordered by title.

This is not working:

SELECT post_id
     , price*1 AS number 
  FROM table 
 WHERE number <= 75 
 ORDER 
    BY key 
 GROUP 
    BY post_id;
Tags:

2

Answers


  1. 0

    Assuming that there is strictly one price and title per post:

    SELECT post_id, t1.value price, t2.value title
FROM table t1 
JOIN table t2 USING (post_id)
WHERE t1.key = 'title'
  AND t2.key = 'price'
-- another WHERE conditions if needed
ORDER BY t2.value
    Login or Signup to reply.
  2. 0 
    DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(entity_id INT NOT NULL
,attribute VARCHAR(12) NOT NULL
,value VARCHAR(12) NOT NULL
,PRIMARY KEY(entity_id,attribute)
);

INSERT INTO my_table VALUES
(297,'title','test'),
(297,'price','50'),
(250,'title','test2'),
(250,'price','75'),
(100,'title','test3'),
(100,'price','100');

CREATE VIEW v_my_table AS
SELECT a.entity_id
     , a.value title
     , b.value price
  FROM my_table a
  LEFT
  JOIN my_table b
    ON b.entity_id = a.entity_id
   AND b.attribute = 'price'
 WHERE a.attribute = 'title';
 
SELECT * FROM v_my_table WHERE price+0 < 75;
+-----------+-------+-------+
| entity_id | title | price |
+-----------+-------+-------+
|       297 | test  | 50    |
+-----------+-------+-------+

UPDATE v_my_table SET price = 100 WHERE price+0 < 75;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

SELECT * FROM v_my_table WHERE price+0 < 75;
Empty set (0.01 sec)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search