Modifiers

Modifiers are clauses or keywords that alter or refine the behavior of a query. They don't perform calculations or aggregations themselves, but rather change how the data is selected, sorted, grouped, or filtered.

TIP

Modifiers are available in the Select statement class.

Distinct

The distinct() method is used to remove duplicate rows from the result set.

$query = new QueryBuilder();

$query->from('posts')
      ->distinct()
      ->select('post_title')
      ->where('post_status')->is('publish');

This query selects all unique post titles from the posts table where the post status is 'publish'.

Group by

The group_by() method in the Select statement class allows you to add GROUP BY clauses to your SQL query.

$query = new QueryBuilder();

$query->select('column1', 'column2')
      ->from('table_name')
      ->where('condition')->is('value')
      ->group_by('column1');

You can also pass an array of columns to the group_by() method to group by multiple columns.

$query->select('column1', 'column2')
      ->from('table_name')
      ->where('condition')->is('value')
      ->group_by(['column1', 'column2']);

Having

HAVING clauses are an important part of SQL queries, particularly when working with aggregate functions and GROUP BY clauses. HAVING clauses are used to filter the results of aggregate functions in SQL queries. They are similar to WHERE clauses, but they operate on grouped data rather than individual rows.

HAVING clauses are typically used in conjunction with GROUP BY clauses.

$query = new QueryBuilder();

$query->select('department', 'AVG(salary) as avg_salary')
    ->from('employees')
    ->group_by('department')
    ->having('AVG(salary) > %s', 50000);;

This query groups employees by department and only shows departments where the average salary is over 50,000.

HAVING clauses are powerful for filtering grouped data and are essential when you need to apply conditions to the results of aggregate functions in your queries.

AND and OR

You can also use AND and OR to combine multiple conditions in a HAVING clause. This is done by chaining the and_having() and or_having() methods.

$query->select('department', 'COUNT(*) as employee_count')
      ->from('employees')
      ->group_by('department')
      ->having('COUNT(*) > %s', 10)
      ->and_having('AVG(salary) > %s', 50000)
      ->or_having('department = %s', 'Sales');

These methods allow you to build complex HAVING conditions in a readable and maintainable way, providing flexibility in filtering grouped data based on aggregate function results.