SQL functions and math operations for QQuery

At times, you may need to create database queries which retrieve values which are calculated values or custom SQL values. In QCubed-4, we call these virtual attributes. Each virtual attribute must be given a name and a definition. The definition can be taken from a query built with QQ operations, a custom sub-SQL clause, or a fully custom SQL statement.

This example demonstrates using virtual attributes to define values created with QQ operations. The standard SQL math operations included are:

You can also use QQ::MathOp to apply any math operator that your particular flavor of SQL might provide. For example, you can use QQ::MathOp to execute a bitwise shift operation ("<<") if you are using MySQL or Postgres, even though that operator is not included in the SQL standard.

Similarly, you can get the results of standard SQL functions, like:

And, you can use QQ::Func to get the results of any SQL function that your particular flavor of SQL provides.

Select names of project managers whose projects are over budget by at least $20

Karen Wolfe
John Doe
Karen Wolfe

PROFILING INFORMATION FOR DATABASE CONNECTION #1: 3 queries performed. Please click here to view profiling detail

The same as above, but also create a calculated virtual field and sort with it

John Doe
Karen Wolfe
Karen Wolfe

PROFILING INFORMATION FOR DATABASE CONNECTION #1: 4 queries performed. Please click here to view profiling detail

The same as above and filter out most of the other fields by using a Select clause

This also demonstrates how to use the \QCubed\Query\QQ::MathOp and \QCubed\Query\QQ::Neg functions.

John Doe
Karen Wolfe
Karen Wolfe

PROFILING INFORMATION FOR DATABASE CONNECTION #1: 5 queries performed. Please click here to view profiling detail

SQL Function Example

Use the \QCubed\Query\QQ::Abs and \QCubed\Query\QQ::Sub functions to retrieve projects both over-budget and under-budget by $20.

Mike Ho
John Doe
Karen Wolfe
Karen Wolfe

PROFILING INFORMATION FOR DATABASE CONNECTION #1: 6 queries performed. Please click here to view profiling detail