As we saw in the last episode, SELECT tells the query what columns have to be in the output. Today let’s talk rows and the clauses telling the query what rows to take and where to take them from. After we specify the columns we need in the resulting recordset, we need to specify the source of recordset’s rows. This is what the FROM clause does. So, what do I type next? For the object entities like Catalogs and Documents The answer is perfectly straightforward. I specify the Metadata Class name then dot and the Metadata Object name, and here we go. The only detail to note here is that we use the Metadata Class name in the singular form, rather than in the plural we use in the 1C script. If there is a tabular section in the Metadata Object’s structure, we just add another dot, then the tabular section’s name, and this is it.
It gets a bit more confusing when it comes to the non-object entities like accumulation and information registers. Of course, we can use the same syntax here, but what we’ll get is just the main table of the register. Depending on its type and settings the register can have several other tables. For example, the Inventory register has the balance table containing the information on how many products we have left by now. If you’re not sure what tables are there or what their names are, just run the query builder and here are all the registers and all the tables we’ve got. As you can see, the Inventory registerhas four different tables to choose from: the main table, the balance table, the turnover table,and the table that combines both balances and turnovers in one.
Some of these tables are real database tables while others are so-called virtual ones. It means that you can pass some parameters to the table as if it’s a function or something. And this is where you can find the list of those parameters. For example, this is how I get the inventory balance for the specific product. And this is what it looks like in the query text. These are the balance virtual table parameters, this is my condition, and this is a query parameter I’ll pass later on. By the way, you can edit the query text right here and if you don’t make any mistakes,the query builder will parse you version and show it in its UI. Whenever you’re ready, just click this OK button, and here is you query text in the editor. Right-click anywhere inside the text, select Query Builder from the drop-down menu, and there you have it. Your query is back in the Query Builder’s UI.
Now. Did you notice this AS thingy after the table name? This is called an alias and it, basically, temporarily nicknames a table within a query, so you can use something shorter like this, or something more natural like this. One more thing definitely worth mentioning. Sometimes we need to get the data from more than one table, and it might seem to be a good idea just to list all of the tables right here in the FROM clause. So, here is a word of wisdom for you: don’t do this. Just donтАЩt. It’s an ancient SQL versions’ legacy that is here only for backward compatibility and universally considered to be a bad, bad practice. It’s confusing, it might be slow, and it doesn’t do anything you cannot get with JOINs. So, just use JOINs instead. How? This is what the next episode is all about.
Now let’s talk the WHERE clause which we use when we need to filter out the records we don’t need. We just specify any condition we want and that’s it. The thing to note here is that using literals in the query is not always a good idea. Sometimes it can slow down the query, so I suggest you make a habit of using parameters instead. It looks like overkill (and sometimes it is). But it’s worth it because it’s a much more robust solution in terms of performance. Another useful habit is using the virtual table parameters instead of WHERE clause whenever it’s possible. I can filter out everything but the Coke using condition like this but I can get the very same result passing this condition to the balance table parameters like that. And this will be much more solid option performance-wise. So, this is how we tell the query what rows to take from the database.