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, 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: 2 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: 3 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: 4 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: 5 queries performed. Please click here to view profiling detail