Relations in web2py

Joins

Based on a shared column between two or more tables, a JOIN clause is used to merge rows from those tables. Here we will learn about the two most commonly used joins in web2py, the inner join and left outer join.

Inner Join

Using a join, more especially an INNER JOIN is another technique to accomplish the same goal. When a query connects two or more tables, like in the example below, web2py performs automatically and transparently.

>>> rows = db(db.person.id == db.thing.owner_id).select()
>>> for row in rows:
...    print row.person.name, 'has', row.thing.name

Output

Joey has Boat
Joey has Chair
Chandler has Shoes

The rows now contain two results as web2py did a join. But the two fields can have a similar name, so you must specify which field you want to access at the extraction time.

For example,

row.person.name
Or
Row.thing.name

You can also use an alternate syntax for INNER JOINS.

>>> rows = db(db.person).select(join=db.thing.on(db.person.id == db.thing.owner_id))
>>> for row in rows:
...    print row.person.name, 'has', row.thing.name

Output

Joey has Boat
Joey has Chair
Chandler has Shoes

Left Outer Join

Due to his lack of possessions, Carl is not included in the list above. You must do a LEFT OUTER JOIN if you want to pick on people (whether they have or not) and their things (if they do). The select's argument "left" is used to do this. Here's an illustration:

>>> rows = db().select(db.person.ALL, db.thing.ALL,
...                   left=db.thing.on(db.person.id == db.thing.owner_id))
>>> for row in rows:
...    print row.person.name, 'has', row.thing.name

Output

Joey has Boat
Joey has Chair
Chandler has Shoes
Ross has None

Where:

left = db.thing.on(...)

Does a left join query. Here, the condition needed for the join is the argument of db.thing.on (the same used above for the inner join). It is essential to be clear about the fields to choose in a left join scenario.

Several left joins can be concatenated by giving a list or tuple of db.mytable on(...) to the left parameter.

Group and Counting

Sometimes, you want to group rows based on specific criteria and count them while performing joins. Consider counting the quantity of possessions each person has. Web2py also supports this. You must first have a counting operator. The second step is to owner-join the person table to the item table. Third, group the rows by person, count them as you group them, and then select all rows (person + thing):

>>> count = db.person.id.count()
>>> for row in db(db.person.id == db.thing.owner_id
...              ).select(db.person.name, count, groupby=db.person.name):
...    print row.person.name, row[count]
... 

Output

Joey 2
Chandler 1

The count operator is used as a field. One of the significant issues is the way to retrieve the information. The count in every row is not visible on the field, so where does it go? It does not go anywhere. It just goes into the storage object.

Many to Many Relations

This is somehow similar to one-to-many relations but also different in its own way. Like in one to many, we have a thing to have one owner, but one person can own many things, but here, the thing can have many owners along with the owner containing many things.

We can understand this with the help of an intermediate table that connects the person and things table. You can do this as shown below:

>>> db.define_table('person',
...                Field('name'))
>>> db.person.bulk_insert([dict(name=’Joey’), dict(name='Chandler'), dict(name='Ross')])
[1, 2, 3]
>>> db.define_table('thing',
...                Field('name'))
>>> db.thing.bulk_insert([dict(name='Boat'), dict(name='Chair'), dict(name='Shoes')])
>>> db.define_table('ownership',
...                Field('person', 'reference person'),
...                Field('thing', 'reference thing'))

Output

<Table person (id, name)>
<Table thing (id, name)>
<Table ownership (id, person, thing)>
[1, 2, 3]

Summarising the above relationship, it can be written like this:

>>> db.ownership.insert(person=1, thing=1)  
>>> db.ownership.insert(person=1, thing=2)  
>>> db.ownership.insert(person=2, thing=3)

You can add a new relation, as we have three-way relation tables so we can make a set to perform our operations with ease.

>>> persons_and_things = db((db.person.id == db.ownership.person) &
...                        (db.thing.id == db.ownership.thing))
>>> db.ownership.insert(person=3, thing=1)

Now you can search for a person or the owner of a particular thing like this:

>>> for row in persons_and_things(db.person.name == 'Joey').select():
...    print row.thing.name
>>> for row in persons_and_things(db.thing.name == 'Boat').select():
...    print row.person.name

Output

Boat
Chair
Joey
Ross

Frequently Asked Questions

What is web2py?

Web2py is a web application framework that is free and open-source, written in the Python programming language.

Why web2py?

Users can learn easily server-side web development, and it is lightweight and speedy.

What is meant by Web application framework?

A software framework known as a web application framework (WAF) is made to facilitate the creation of web applications, which include web resources, web services, and web APIs.

What is Database Abstraction Layer?

A database abstraction layer is an API (Application Programming Interface) that unifies the communication between a computer application and databases such as Oracle, SQL Server, MySQL, IBM Db2, and PostgreSQL.

Is web2py an MVC model?

The Ruby on Rails and Django frameworks inspired the creation of web2py. Web2py is similar to these frameworks in that it emphasizes rapid development, prefers convention over configuration, and adheres to the model-view-controller (MVC) architectural pattern.

Conclusion

In this blog, we have learned about different types of relations in web2py, like one to many and many to many. We have also discussed various types of joins like inner join and left outer join, followed by suitable examples with proper explanations.

Examples in code form.

To learn more about Web2Py, see Web2py, Web2Py init, Web2py Introduction, Web2Py Installation, Troubleshooting, and Application creation.

Nevertheless, you may consider our paid courses to give your career an edge over others.

Do upvote our blogs if you find them helpful and engaging!

Happy Learning!