Joins

Joins are used to combine rows from two or more tables, based on a common field between them. The library allows you to perform four type of joins: INNER join by using the join method, LEFT join by using the left_join method and RIGHT join by using the right_join method.

When using the join methods, the table names are automatically prefixed with the WordPress table prefix, so you don't need to include it in your table names.

Parameters

The join(), left_join() and right_join() methods all take the same parameters:

1. The table to join
2. The alias for the joined table
3. The first column for the join condition
4. The operator for the join condition
5. The second column for the join condition

The Join clause class handles adding the appropriate SQL syntax for each join type and manages the joins internally.

Inner join

The join() method is used to perform an INNER JOIN.

$query = new QueryBuilder();

$query->select()
    ->from('posts', 'p')
    ->join('postmeta', 'pm', 'p.ID', '=', 'pm.post_id')
    ->where('p.post_type')->is('post')
    ->and_where('pm.meta_key')->is('featured')
    ->set_columns('p.ID', 'p.post_title', 'pm.meta_value');

NOTE

Names of columns in these example are fictitious.

This query joins the posts and postmeta tables, selecting posts that have a specific meta key. The inner join ensures that only posts with matching entries in the postmeta table are returned.

Left join

The left_join() method is used to perform a LEFT JOIN.

$query = new QueryBuilder();

$query->from('posts', 'p')
      ->left_join('postmeta', 'pm', 'p.ID', '=', 'pm.post_id')
      ->where('p.post_type')->is('post')
      ->set_columns('p.ID', 'p.post_title', 'pm.meta_value');

NOTE

Names of columns in these example are fictitious.

This query joins the posts and postmeta tables, selecting posts that have a specific meta key. The left join ensures that all posts are returned, even if they don't have a matching entry in the postmeta table.

Right join

The right_join() method is used to perform a RIGHT JOIN.

$query = new QueryBuilder();

$query->from('posts', 'p')
      ->right_join('postmeta', 'pm', 'p.ID', '=', 'pm.post_id')
      ->where('pm.meta_key')->is('featured')
      ->set_columns('p.ID', 'p.post_title', 'pm.meta_value');

NOTE

Names of columns in these example are fictitious.

This query joins the posts and postmeta tables, selecting posts that have a specific meta key. The right join ensures that all entries in the postmeta table are returned, even if they don't have a matching entry in the posts table.