This hands-on lab teaches you more advanced SQL including table relationship types, how to join multiple tables together, and how to write SQL queries that are faster and more scalable for your WordPress projects. You will use a practical example plugin to test two different approaches to solving the problem of fetching posts that are grouped by each term. You’ll test these approaches, explore the actual SQL queries, and learn about the impact of processing speed, performance, and scalability.
The best way to learn advanced concepts is to use examples you can relate to, i.e. that are in the context of WordPress. The problem you are solving is to get the posts grouped by each term while doing it as quickly as possible and ensure performance and scalability.
You’ll learn about table relationships and how to join them together to drill down to the information you need for your query. You’ll learn about the complex relationship between posts, terms, and taxonomies as well as their associated metadata. You’ll learn about how WordPress communicates with the database via $wpdb.
This lab presents two different approaches to solving the same problem. These approaches are very different and each has an impact on processing speed, performance, and scalability.
- Approach 1: Use
get_termsto get all the terms first. Then you’ll loop through each term and go fetch the posts for it. - Approach 2: Use one SQL query to fetch all of the information. That’s one database hit.
For each approach, you’ll learn about how many times the database is hit as well as take a deep dive into the actual SQL queries each generates.
What You Will Learn
Here is a summary of what you will learn:
- Relational types in MySQL, one-to-many, many-to-many, etc.
- How to join multiple database tables together to fetch the information you need from the database.
- Learn about different
JOINcommands. - Relationship between posts, terms, and taxonomy, including how to fetch what you need.
- Grouping results using the SQL command
GROUP BY - Ordering results using the SQL command
ORDER BY - Introduction to the WordPress global
$wpdb - Preparing results using
$wpdb->prepare() - Running the query using
$wpdb->get_results() - Performance and scalability
Prerequisites
In order to do this lab, you will need the following:
- Introduction to Database hands-on lab
- Groupby Lab Tester companion plugin
- A local web server, such as Local by Flywheel, VVV, MAMP, WAMP, Desktop Server, or some other application.
- A database interface such as Sequel Pro (which is what Tonya uses) or phpMyAdmin.
- Your favorite IDE or editor. (Tonya uses PhpStorm.)
- A local sandbox website
Once upon a time, there was a developer... You! This is going to be a good story...
Episodes
Total Lab Runtime: 02:46:48
- 1 Lab Introductionfree 14:27
- 2 Slow Approach Overviewpro 12:11
- 3 Test Slow Approach - Part 1pro 17:27
- 4 Test Slow Approach - Part 2pro 21:07
- 5 Relational Typespro 23:20
- 6 Types of Joinspro 09:30
- 7 Joining Metadatapro 12:37
- 8 Joining Posts to Termspro 20:32
- 9 Grouping Datasetspro 08:32
- 10 WordPress SQL Handler $wpdbpro 11:01
- 11 Deeper Dive into $wpdb->get_resultspro 05:54
- 12 Run Fast Approach & Compare Resultspro 10:10
What to Do Next?
Additional Resources
To learn more about the specific SQL commands you will be using, there are various courses and resources available to you. Some include:
