Let’s wrap it up with a summary of what you accomplished in this lab. Congratulations! You did a great job.
Labs
Labs are hands-on coding projects that you build along with Tonya as she explains the code, concepts, and thought processes behind it. You can use the labs to further your code knowledge or to use right in your projects. Each lab ties into the Docx to ensure you have the information you need.
Each lab is designed to further your understanding and mastery of code. You learn more about how to think about its construction, quality, maintainability, programmatic and logical thought, and problem-solving. While you may be building a specific thing, Tonya presents the why of it to make it adaptable far beyond that specific implementation, thereby giving you the means to make it your own, in any context.
Test Slow Approach – Part 2
Let’s continue testing the slower approach. In this episode, you will explore the SQL queries when running each WP_Query. This is a fun one. You’ll discover how many queries are generated (i.e. how many times the code hits the database). Then you’ll change the term assignments to discover how the number of queries increases exponentially with the number of terms.
Run Fast Approach & Compare Results
Let’s run the fast approach tests. You’ll look at the SQL queries using Query Monitor as well as the data dumps you put into Core. We’ll talk about the code, how it works, and ways that you can enhance it for more versatile use cases. Then we’ll compare the two approaches. Wrap it Up Congratulations! You did a great job. We covered a lot of information in this lab including learning about database table relationships, how to join tables together, filtering down to what you need, aliasing the columns you get back, performance, scalability, and how to build a SQL […]
Deeper Dive into $wpdb->get_results
Let’s look closer at WordPress Core for the object $wpdb->get_results(). You’ll see that it’s a wrapper for the PHP constructs: mysql_query – PHP 5.5 or less (it’s been removed from PHP 7) mysqli_query – PHP 5 and 7 We’ll also discover that WordPress provides a filter event where the $query is exposed. This event is named “query.” We’ll convert our plugin to remove the snippet from get_results and build a callback for the “query” event. You can use this callback whenever you need to analyze or customize a query. You’ll also do a quick overview of the fast approach code.
WordPress SQL Handler $wpdb
We have our SQL query that we want to use for our project. Let’s talk about how to translate that into WordPress. How does WordPress communicate with the database? What tools are available? WordPress provides you with a set of tools to communicate with the MySQL database. This wrapper will save you time and lots of lines of code. The object you’ll use is the global $wpdb. At the time this lab was developed, WordPress does not provide a wrapper for this global object. Therefore, we have to use the global directly. In this episode, you’ll explore a couple of […]
Grouping Datasets
Let’s finish our native SQL query and learn about grouping datasets. We want to group the posts to their terms for a given taxonomy. You’ll walk through the process, step-by-step.
Joining Posts to Terms
It’s time to write the native SQL query that glues the taxonomy to the terms and then the terms to the posts. In this episode, you will write SQL in phpmyAdmin or Sequel Pro. We’ll go step-by-step, first fetching terms for the category taxonomy. Then we’ll filter our query to get terms only for certain post IDs. Then you’ll join in the posts database table and select specific content out of each.
Joining Metadata
In the last two episodes, you learned about how database tables are glued together through their relationships. Let’s see in action by writing SQL queries to fetch posts and associated post metadata in one query. You’ll see how INNER JOIN is different than LEFT JOIN. You’ll learn how to grab specific metadata with your query, such as a subtitle. Then you’ll do the same experiment with the users and user metadata tables. Let’s write some SQL.
Relational Types
Remember that MySQL is a relational database management system (RDBMS). Database tables can be of type one-to-one, one-to-many, and many-to-many. These types can be confusing. Let’s relate (no pun intended) the types to WordPress.
Types of Joins
SQL provides you with four different commands to join database tables together: INNER JOIN, LEFT JOIN, RIGHT JOIN, and JOIN. How do these work? What makes them different? And when do you use one or the other? Let’s relate it to WordPress posts and metadata as well as posts to terms. This chart provides a visual of the what is fetched for each join: Additional Resources: SQL Join Types A Visual Explanation of SQL Joins
