QofQ: Processing data at application level with Query of Queries

Query of Queries is an important method for reprocessing query results at the application level. 

QofQ is the method you should use if you are having trouble with the amount of data and/or the number of queries you have during the development process. 

You are working on dense data, you are performing different operations on the same dataset. Finally, if you are trying to produce results by sending queries to the same table(s), then you do not have a good grasp of query of queries. Because after pulling the same dataset to the application level, it is possible to generate queries from the existing query in the application memory. In this way, you do not keep the database busy over and over again and you do not slow down your work due to traffic.

Before you start

If you are going to use the query of queries method, make sure you get the only data in the main query that you will use in other queries. Because query of queries does not support inner or outer join! Although it supports all other concepts, it will not be possible to use more than one query together. It only allows to combine multiple queries belonging to the same set with the union concept, with the rule of adding one under the other.

B plan

We immediately went to work and wrote our query, which is why we want to create totals by grouping somewhere. For example, subtotals according to VAT rates. In the best condition, it is possible to create a new query and run it into the database. But don't we already have this data? Then, you may find that you have duplicated the code when you think about how to calculate the data we have and try to reduce database operations by creating arrays and variables in the middle of the loop operation. Of course, if you have done the situations in the article titled "DevLog - Filter, Map and Reduce Operations", I can say that it is accepted, but otherwise I can say never. At best, we have a recordset, that is, the query result, and if we think that if we actually run this query again, we can do our job, we are in the right place. Our plan B is ready!

Our query simply retrieves records from the database in the tbl_bill_satirlar table after 10.10.2010. Do not say that there is no such query, let's assume that there is. We output this to a table with the loop operation. Everything is great so far, what about the total operations? Grand totals are more straightforward. As if a query like this would do the trick.

Sounds so simple? Of course, it is very simple, when we synchronize the dbtype value as a query, we can use a query created in the request directly as a table. To read the total amount from this query, it will be sufficient to say query_totals.total_amount. We don't need to go to the database again. Of course, the process was simple, let's talk about something a little more difficult. Such as the sum of the VAT amounts according to the VAT rates

Again, we do it using the group by part as in a standard query. In this way, we get as many lines as the VAT rates in the invoice lines, and we do not disturb the database for this operation. So far, we have actually completed the query of queries. We can also use the classic where section. In fact, as I mentioned before, it is possible to combine tables with union again. This topic is actually a very simple concept for anyone who can query.

If more is required

Sometimes we may need more. For these operations, we need to do the operations that form the basis of the No-Sql concept called Pipe. By using a query of queries, you can enter the pipe operation and at this stage, both joins and subsets can be created. I don't know which of these procedures you would like. As I mentioned before, you can understand better if you read the article I specified with "DevLog - Filter, Map and Reduce Operations". You will need to use the concepts in this article to use your result by creating pipes and creating subsets or structs for your data.

If we consider a simple scenario, we also had to retrieve the lines while creating the invoice list. However, assuming that we are pulling the rows collectively with the ids in the invoice query in a different query, we can simply get them as subsets.

In this way, during the loop process, we filter the rows belonging to the relevant invoice from the query_invoice_satirlar query and get them as a subset without establishing a join. This will run better than the classic database query. If we want to do a pipe operation

In this example, the invoice header line is combined with the subset line and filled into an array. If the invoice variable in the array is subset of the struct of the invoice row, it will contain the query for this invoice. It also makes it possible to make the return value within the loop operation without executing the operation. Here, by using valueArray, we have taken an array of invoice ids and given the invoice id value to the query over the element with the map operation, and the index value to the queryGetRow function to get the invoice row. Although we did not carry out the process with exactly no-sql logic, we did the process partly in no-sql and partly in sql format. It is possible to do the same operation in no-sql format by using the methods in the article named "DevLog - Filter, Map and Reduce Operations".

It will also be possible to use this operation as subsets on more than one subset and/or at more than one depth. However, it is useful to know the following functions for these operations.

valueArray(query, column_name) array

Returns the values found in the specified column in a query as an array. The query name (as a variable) and the column name (as a string) must be specified.

queryGetRow(query, row_index) struct

Returns the specified row in a query as a key-value in the struct. Key is the column name and value contains the value of the column.

The above two functions are required to switch from sql base to no-sql base. However, you can also use queryMap, queryFilter, queryReduce operations by staying in the sql layer. However, the most important point to remember is that staying in the sql layer (performing direct operations on the query, including qoq) will limit your flexibility or cause you to spend a lot of effort to gain flexibility. Because the data in the queries are both primitive and adding new columns is not an easy matter. However, since the no-sql approach will make you use structs and arrays, it allows you to use different columns and data types in each operation.

Correct approach

Query of queries usually aim to read a pure dataset and perform operations on it at the application layer. Query manipulation operations are often used to create subsets. Of course, it can be done by making changes to the existing columns with a function such as queryMap (for example, converting TL values to USD would be to change the values of the columns such as the amount in the query). However, these manipulations are not the subject of query of queries.

First of all, it should be decided which data will be presented in a document, it should be checked whether the data will be read only or not, and it should focus on issues such as whether it is possible to cluster the subsets together or if the amount of data is high in separate clusters. If one or more cases are suitable for query of queries, this operation can be done without hesitation. However, it is not always reasonable to use qoq, if the process is done in a single dataset and the result is reflected in the document without processing, it may be better not to consider this situation within the scope of qoq. If we are to comply with the clean code approach, it will be the right option to immediately associate the subqueries after the main query in order to separate the model and view codes, and to reflect them to the view codes by making the necessary query calculations. If your table columns are not too many (the structs can fill up more memory when they take too much value), you should convert the data into arrays and structs with no-sql logic and proceed with a pipe. Thus, you or the developers who will continue to develop after you can easily read the processes on the model and, if necessary, open new data fields and reflect the desired features. Do not forget that with calculations and variables that may be overlooked in spaghetti codes, you increase the risk of making mistakes / overlooked by the software developer.