SQL view can be defined as a virtual table that fetches data from different tables joined using SQL queries. This can be useful for cases when we want to display data from different tables joined together in the way we want and also allows us to do different operations for the view, including filter, group by, etc. The main advantage of using this is we don’t need to store the view in the database; instead, we can use the set of queries to fetch the data dynamically in the way we need regularly.
Please refer to the blog below to learn more about Creating a SQL view in Odoo.
There are a lot of Postgres operations that can be used for different data manipulations.
Aggregate functions:
Aggregate functions can be used to return a single value after performing calculations of multiple values. Except for Count(*) function, all other aggregate functions ignore NULL values. Major aggregate functions include
a) Count()
b) Sum()
c) Avg()
d) Min()
e) Max()
Count():
It can be used to return the number of rows from a table.
Eg:
select count(id) from sale_order;
It will return the number of sale orders created in the sale_order table.
Sum():
select sum(amount_total) from sale_order;
It will return the total sum of the sale order amount.
Avg():
It will return the average of selected values.
select avg(amount_total
) from sale_order
Min() and Max():
These will return the minimum and maximum values from a set of values.
select min(amount_total) from sale_order
select max(amount_total) from sale_order
Joins:
We often need to combine rows from different tables in order to get the desired output, and for that, we can use the JOIN clause. Whenever we need to join two or more rows from different tables, we will have to do it based on a related column between them.
Joins are of different types:
INNER JOIN:
It can be used to select all the rows from both the tables we need to combine based on the conditions given. We will have to establish or find a connection between the two tables using a common field. We can also use JOIN only instead of INNER JOIN.
Syntax:
SELECT FROM table1 INNER JOIN table ON table1.relational_column = table2.relational_column;
Eg:
Query to fetch partner name with sale order reference:
Select res_partner.name,sale_order.name from res_partner inner join sale_order ON res_partner.id = sale_order.partner_id
LEFT JOIN:
It can be used to return all the records from the left table even if there are no matches in the right table. As the inner join returns, all the records from both tables that satisfy the conditions but the left join will only check the conditions for the right table, and if there is no matching row on the right side, it is set as NULL.
Syntax:
SELECT table1.column1,table1.column2,table2.column1,….FROM table1 LEFT JOIN table2
ON table1.relational_column = table2.relational_column;
Eg:
Select res_partner.name,sale_order.name from res_partner left join sale_order ON res_partner.id = sale_order.partner_id LIMIT 5;
RIGHT JOIN:
It is similar to the left join, but it will return all the records from the right side even if there are no matches on the left side.
Syntax:
SELECT table1.column1,table1.column2,table2.column1,….FROM table1 RIGHT JOIN table2 ON table1.relational_column = table2.relational_column;
Eg:
SELECT res_partner.name, res_country.name FROM res_partner right outer JOIN res_country ON res_partner.country_id = res_country.id LIMIT 5;
FULL (OUTER) JOIN;
It can be defined as a combination of both LEFT JOIN and RIGHT JOIN. The rows for which a match does not exist will be set as NULL.
Syntax: SELECT table1.column1,table1.column2,table2.column1,…. FROM table1 FULL JOIN table2 ON table1.relational_column = table2.relational_column;
Eg:
SELECT res_partner.name, res_country.name FROM res_partner FULL outer JOIN res_country ON res_partner.country_id = res_country.id ;
SELF JOIN:
This is used for cases when we need to join the data in the same table, so the table is joined with itself.
Syntax:
Select column_name(s) from table1 T1, table1 T2 where condition;
Eg:
Query to get partners from the same city:
select a.name as partner1, b.name as partner2, c.name as state from res_partner a join res_country_state c on a.state_id = c.id inner join res_partner b on c.id = b.state_id where a.id <> b.id and a.state_id = b.state_id order by state;
CROSS JOIN:
It is used to get all the combinations of rows between two tables hence known as cartesian join. Unlike other joins, this does not require a relational column.
Syntax:
SELECT ColumnName_1, ColumnName_2, ColumnName_N FROM [Table_1 CROSS JOIN [Table_2]
Eg:
select a.name,b.name from sale_order a cross join res_partner b
Apart from the aggregate functions and join clause, we have other essential operations available:
GROUP BY:
It is used to group the rows having the same values and it is often used with aggregate functions to get the desired result.
syntax:SELECT column_name(s) FROM table_name where condition GROUP BY column_name(s) ORDER BY column_name(s);
Eg:
Query to get the number of partners in each country in Odoo:
select c.name as country,count(*) as number from res_partner a inner join res_country c on a.country_id = c.id group by c.name order by c.name
HAVING:
It can be added to the group by filtering the records based on conditions.
Syntax:
SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s) HAVING condition ORDER BY column_name(s);
Eg:
query to fetch the total sale order amount of each partner with the condition of the amount is greater than 100.
select a.name,sum(amount_total) as sum from sale_order join res_partner a on sale_order.partner_id = a.id group by a.name having sum(amount_total) > 1000.
Though the HAVING and WHERE clause is used to filter the records, there is a huge difference between them.
The difference between ‘group by’ and ‘having’ can be described as: ‘group by’ is used to filter the records from the table, while ‘HAVING’ is used to filter the records from groups. While the WHERE clause cannot include aggregate function, HAVING can include aggregate function. WHERE clause is implemented in row operation and can be used in SELECT, DELETE, AND UPDATE statements. In contrast, the HAVING clause is implemented in column operation and can be used only for SELECT statements. Also, HAVING can only be used with the group by clause.
These are the essential database operations that can be performed while manipulating data in Odoo.