Geek Answers Handbook

MySQL complex query

I am creating a website in Wordpress that contains information about television programs. I use custom fields to select each message.

The table looks something like this:

+----+---------+----------+------------+ | id | post_id | meta_key | meta_value | +----+---------+----------+------------+ | 1 | 1 | name | Smallville | | 2 | 1 | season | 1 | | 3 | 1 | episode | 1 | | 4 | 2 | name | Smallville | | 5 | 2 | season | 1 | | 6 | 2 | episode | 2 | +----+---------+----------+------------+

Basically I need to select all the television shows with the name "Smallville" and sort them by season, then by episode. I thought it would be pretty simple, but everything I tried returns nothing.

Could you explain how can I do this?

+4
source share
5 answers

You can do something like this:

 SELECT t1.post_id, t1.meta_value AS name, t2.meta_value AS season, t3.meta_value AS episode FROM ( SELECT * FROM the_table WHERE meta_key = 'name' ) t1 INNER JOIN ( SELECT * FROM the_table WHERE meta_key = 'season' ) t2 ON t1.post_id = t2.post_id INNER JOIN ( SELECT * FROM the_table WHERE meta_key = 'episode' ) t3 ON t1.post_id = t3.post_id

This will give you the result:

 | post_id | name | season | episode | ------------------------------------------- | 1 | Smallville | 1 | 1 | | 2 | Smallville | 1 | 2 |

In this form, it is much easier for any operations.

You need to add:

 WHERE name = 'Smallville' ORDER BY season, episode
+11
source

Merge the lines using self-join and you are good to go:

 SELECT * FROM yourtable name INNER JOIN yourtable season on season.post_id = name.post_id and season.meta_key = 'season' INNER JOIN yourtable episode on episode.post_id = name.post_id and episode.meta_key = 'episode' WHERE name.meta_key = 'name' and name.meta_value = 'Smallville' ORDER BY season.meta_value, episode.meta_value
+7
source

A more general case: sort the conversion from your format to the more common format of relational databases:

 SELECT (SELECT meta_value FROM data t1 WHERE t1.post_id = t0.post_id AND meta_key = "season") AS season, (SELECT meta_value FROM data t1 WHERE t1.post_id = t0.post_id AND meta_key = "episode") AS episode FROM data t0 WHERE meta_key = "name" AND meta_value = "Smallville"

For actual sorting, you cannot reuse the season / episode values ​​(they are not yet assigned during sorting), so you need to copy / paste the subquery into the ORDER BY clause:

 ORDER BY (SELECT ... "season") ASC, (SELECT ... "episode") ASC,
+2
source

No need to do direct SQL. You have access to the SQL query through the WP_Query object. Check the filters surrounding the where clause in the WP_Query object (there is more than one way to get it) and just change the default parts of WP_Query before they are combined together.

Start by creating a WP_Query object that receives all messages of type postmeta and postmeta, and then binds a bit to the where clause to fulfill some additional conditions.

There is another filter that allows you to get the SQL query ordering section so that you can change it.

There is no reason to manually write SQL here, just change what has already been created for you.

+2
source

the idea is to insert the table into yourself 3 times, where for each of them I take the rows for the given meta_key:

 SELECT t1.meta_value name, t2.meta_value season, t3.meta_value episode FROM table t1 JOIN table t2 on t1.post_id = t2.post_id and t2.meta_key = 'season' JOIN table t3 on t1.post_id = t3.post_id and t3.meta_key = 'episode' WHERE t1.meta_key = 'name' AND t1.meta_value = 'Smallville' ORDER BY t2.meta_value, t3.meta_value
+1
source

More articles:


All Articles