Building MVP with Flask Day 7 – Left Join with SQLAlchemy

Hi everyone.

Yesterday, I managed to fill the SQLite database with some dummy data and display it using Jinja2 template tags. Today, I would like to polish my design a little bit more. My idea is to use Bootstrap 4 cards design to display an ordered list of products. Why not use table layout you ask?

Well, because Bootstrap 4 tables are great for a comprehensive list of items, where each item is represented by plain text. Plain text is boring. I want images, badges, and alerts grouped into a single cell. With Bootstrap 4 cards, I can do that with a built-in grid system using columns and rows. I will lose responsiveness thought.

Bootstrap 4 cards are not responsive and resizing your screen will break the layout. My approach is to create a separate mobile web version, that will sit on a subdomain. Whenever a user visits my web from mobile, Cloudflare will redirect him to my mobile version.

It sounds more complicated than just bending my design with CSS media queries. We will see. I will make a decision when I see significant traffic coming from mobile phones.

Okay. Back to my design. Let's add some HTML.

Cool. Each Bootstrap 4 card displays one product and its name.

How to do left join with SQLAlchemy

To add the company name to each card, I need to figure out how to do left join in Flask-SQLAlchemy. I designed my database so company data are stored in a separate Company Table. If I left join Company table to the Products table, I will be able to get a dataset I want.

Will this ORM query work?

products = Product.join(Company, Company.id == Product.company_id, isouter=True)

Nope. Got Internal server error.

Gunicorn logs reveal why.

AttributeError: type object 'Product' has no attribute ‘join'

Ok, seems I forgot query syntax in the query.

products = Product.query.join(Company, Company.id == Product.company_id, isouter=True)

Nice, no error this time. Tables are joined.

ORM query above will translate into plain SQL like this.

SELECT product.id AS product_id, product.name AS product_name, product.company_id AS product_company_id
FROM product LEFT OUTER JOIN company ON company.id = product.company_id

But where is the column from the left joined Company table?

I try to print query results set into the command line to see what's happening.

for row in products:
    print(row.Product)
    print(row.Company)

The refreshing Flask page ends with an error.

print(row.Product)
AttributeError: 'Product' object has no attribute 'Product'

By looking at SQLAlchemy documentation I overhaul query syntax.

products = db.session.query(Product, Company).join(Company, Company.id == Product.company_id).all()

Query result set above will finally return both tables and their columns.

[(<Product 1>, <Company 1>), (<Product 2>, <Company 2>)]

Now I should be able to access the company name column in the template.

Great.

Importing custom CSS

The product title is displayed together with the company name, but it is ugly as f. Let's polish design a little more with our CSS styles.

To do that, I have to create a new folder called "static" inside my project folder and create a new CSS file style.css. Then I need to include style.ccs into the header section.

<link rel= "stylesheet" type= "text/css" href= "{{ url_for('static',filename='styles/style.css') }}">

When I try to load the CSS file in my browser, I get not found error.

Instead of a dynamic URL, I try to add the absolute path to my CSS file.

http://financia.local/static/css/styles.css

No luck. I try to declare a static path in __init__ file like below.

app = Flask(__name__, static_url_path='/static’)

Still not found error. This is driving me nuts and my time is running out. I will give it a shot next time.

Useful Jinja2 template functions

Today, I want to finish my layout with a few template tricks. Like, how to round float numbers with Jinja2 syntax.

product["price"]|round(1)

Or how to convert float number to integer number.

{{product["price"]|round(0)|int}}

And what if I don't want trailing zeroes behind my float numbers? I will use rstrip.

{{product["float"].rstrip('0').rstrip('.')}}

Ok, time to call it a day. I didn't progress much today.

In the next session, I want to focus on solving the puzzle with static files. Without it, I won't be able to serve images, javascript codes, and CSS files.

Thanks for reading and see you next time!