Custom SQL Queries in QCubed
Although the QCubed Query can generate the SQL query code for most of your application, you will undoubtedly need to write your own custom queries, to either perform more refined Load methods, execute searches, generate reports, etc.The framework offers multiple ways for performing your own custom SQL queries, from completely free form queries using just the database adapter, itself, to completely structured object-oriented queries using QCubed Query. In general, there are three main ways to perform queries, with pros and cons for each:
Ad Hoc Queries: Completely custom, ad hoc queries can be executed by accessing the database adapter, itself. The advantage of this is that you have complete, total free form control over how you want the query to run. Moreover, you can also run "NonQuery" commands like UPDATE and DELETE. The disadvantage is that because the queries are completely free form, there is no structure, and the QCubed generated ORM cannot take advantage or use your query results at all.
Custom Load Queries: These custom SQL SELECT statements do require a bit more structure, but by adhering to a structure/form that QCubed expects, you can utilize code-generated InstantiateDbRow and InstantiateDbResult methods to convert your query results into instantiated data objects. You still get the benefit of writing, more or less, completely custom SQL SELECT statements, but you now have the added benefit of taking advantage of your code generated ORM. The drawback is that if/when you make changes to your data model, you may need to go back and revisit your custom-written SQL code to ensure that the appropriate fields are being selected to match what the QCubed ORM is expecting.
QCubed Query: This is a fully structure, object-oriented approach to performing SQL SELECT queries, without needing to write a single line of SQL code. Utilizing code generated code and per-table-specific QCubed Query nodes, the QQ API offers almost the full set of functionality that free form Custom Load Queries provide, but with the added advantage that whenever you make changes to your data model and re-code generate, you do not have to worry about updating any hard-coded SQL statements in your code. Of course, the drawback is that QQ is a new methodology for performing queries, so there will be a learning curve.
As a final note, all the examples here are coded below on the page, itself. However, it is always a good practice to have query code like this written within the classes, themselves. Especially for any Load-related methods, QCubed tries to be consistent in following the Singleton design pattern with static "loadBy" and "loadArrayBy" methods, so the SELECT queries for any table can reside in that table's ORM class, itself. For more on this, be sure to view the code generated commented out sample code in your custom ORM subclasses in /includes/data_classes. See Customized Load Methods in Section 2 for more information.
Ad Hoc Query: Selecting the Projects, their managers and team member count
ACME Payment System, managed by Karen Wolfe (with 7 team members)ACME Website Redesign, managed by Karen Wolfe (with 5 team members)
Blueman Industrial Site Architecture, managed by John Doe (with 5 team members)
State College HR System, managed by Mike Ho (with 6 team members)
Ad Hoc NonQuery: Updating Project #3's budget to 2500
Updated. (Use View Source above to see the code for this)
Custom Load Query: Select all Projects with Budgets over $5000, ordered by Descending Budget
State College HR System has a budget of $80500.00ACME Website Redesign has a budget of $9560.25
ACME Payment System has a budget of $5124.67
QCubed Query: Select all Projects which have a Budget over $5000 and under $10000, ordered by Descending Budget
ACME Website Redesign has a budget of $9560.25ACME Payment System has a budget of $5124.67