Ecto. Select data by computed column | Elixir / Phoenix
This post is about a basic technique for SQL. The reason I want to write this article is that I am not a good friend with SQL and I hope a person who is not familiar with will be able to have the technique.
Let’s see… We select data from our database tables with some conditions many times, it may be to display the data and create a list for UI.
Especially for UI, we often use “paginate” function.
As you know, the “paginate” has some properties which are “entities”, “page_number”, “page_size”, “total_pages” and “total_entries”.
Basically, those numbers are computed automatically so we do not need to care for it. However, when we use “paginate” ( maybe ), we have to know the knowledge that will be the technique.
Let me take a sample database for this post. We have a “countries” table, a “islands” table and “islands_peoples” table. And the schema is the following.
Sample DB Schemacountries table
-------------------------------------------------------
id | name(string) | islands_id(int) | population(int) |
islands table
----------------------------------------------------
id | name(string) | area(string) | population(int) |
islands_peoples table
------------------------------------------------
id | islands_id(int) | male(int) | female(int) |
languages table
------------------------------------------------
id | islands_peoples_id(int) | language(string) | - countries has many islands.
- islands_peoples has one islands.
- islands_peoples has many languages.
Select with Non-Computed Columns
Just in case, I will show you a sample code for comparison with this purpose. Here is a sample code which selects countries records by islands' population is less than 100.
It is quite a simple code. The point of this code and design is that we can select data by a ( or some ) condition which is “Column” itsself. It is the “population” of islands table in this above example.
P.S. We need to join the islands table, not preload that otherwise Repo.paginate will deliver the wrong properties. ( eg. total_entries )
|> Select with computed columns
Let’s consider the theme of this post. Imagine that we want to select the countries name and related islands info. with people whose language is English. For the countries info. and islands info., we just can get it by joining both tables.
However, country and island Do Not know the language info. Just islands_peoples know it.
So, the first thing we have to do is to know which islands_people use “English”. And besides, this job will be worked as the filter for select_computed_data/2 function so I will add this as function and apply to select_computed_data/2. Let’s take a look at the final code.
The point is that we want to use columns in Countries or Islands table because we select data ( information ) of those tables have. However, let me tell you once again, These two tables do not know which language people use. So, we have done in _filtered_by_language/2 is to collect islands_id which is related to a column in Islands table ( Islands.id ).
Finally, we can apply the “where” condition to the SQL so that we can get what we want! I personally think this technique(?) is very useful or will be your good knowledge!
Thank you.
