This article will cover:
An introduction to SQL queries
The overall structure of the LiteFarm database schema
Navigation of the LiteFarm schema using foreign keys
Common or particularly valuable queries for extracting data from LiteFarm
No prior knowledge is necessary, though a basic understanding of relational databases will help.
Things you’ll need to follow-along:
A tool to open a “.erd” file - DBeaver is a free example
A Postgres client
Read-only access to the beta environment: Talk to Lite Farm about this
The first page of this cheat sheet
This .erd 👇 (accurate as of March 22nd 2022)
Reference videos
Nathan Williams does a really good introduction to SQL broken down into easy-to-learn videos, so I’ve linked to the most relevant videos in his playlist for all but the first video below. I won’t be using pgAdmin or using the data sets he does (which he covers in the first and second video, respectively) for our LiteFarm specific session, but it probably makes sense to watch those videos (and use that tool and dataset, respectively) when you’re getting started so that you can more effectively follow along with the videos we do cover. I’ll also refer back to specific videos as we progress through some of the LiteFarm examples.
Since this is an introduction, I’ve decided to omit a few of the less relevant or more complicated actions you can take using SQL. If you feel pretty comfortable with everything above, feel free to keep going. Depending on future needs, we may do a future training covering these topics too:
What is a database schema?
A database schema is a structured collection of database tables (tables). Following object oriented principles, tables are generally created to hold information about a conceptual idea such as a task, farm, or user. There are important exceptions to this rule, but in general, this is the case. Each table has certain attributes or columns that fit within the concept of the table. For example, the user table would have columns such as username, email, and name that relate to every user. Each row in that table is a one instance - or record - of that table. Using our user table example, you could have:
id | Username | Name | |
|---|---|---|---|
1 | jdoe | jdoe@gmail.com | Jane Doe |
2 | LightningBoltBoiZ | zeus@olympus.gr | Zues |
The above user table has 2 records. One for Jane Doe and another for Zues. Videos 1 - 10 in the reference videos section above give you background on many ways you can modify or display specific columns on individual tables - so please watch them and experiment. However, where things get really interesting is video 11. In this video, Nathan shows us how to connect different tables together using primary and foreign keys using an operation called a “JOIN”. This is a somewhat difficult concept, but is absolutely essential to getting the most of the LiteFarm database.
A note on ‘deleted’
In LiteFarm, we almost always use “soft deletes”. This means that when a user “deletes” data in the app what’s actually happen is we’re setting a flag in the database that says that data is now deleted, e.g. deleted = true. A “hard delete” is when the row of data is actually just removed from the table altogether. In LiteFarm, the user interface then knows to not display those records that have deleted = true. As such, you’ll also need to make sure and exclude deleted records when you’re pulling information from the database or risk pulling incorrect information. In most cases this just means you’ll need to add the following to whatever table you’re querying:
AND deleted = FALSE
Users, userFarms, and Farms
Commonly, we’ll need to track down information about:
A user
Their farm
Who has access to which farm(s)
For this, we’ll need to join the user, userFarm, and farm tables.
Looking at the ERD we can see:
The users table has the primary key: user_id
The farm table has the primary key: farm_id
The userFarm table has two foreign keys: user_id & farm_id
What this means is that we can JOIN these tables based on their shared keys. Below is a query that will do exactly this by finding users based on either their name or email address and then finding any userFarms and farms that the selected user is connected to via join. By joining these tables, we see which farms each person has access to, their roles at those farms, and the details of the farm.
SELECT * -- You can modify the * to be just the fields you want, e.g. first_name FROM users u JOIN "userFarm" uf ON u.user_id = uf.user_id -- This combines the user and what farms they can access JOIN farm f ON uf.farm_id = f.farm_id -- This pulls in the information about the farm WHERE email LIKE '%%' OR -- When searching by email, remove the "OR" and the name portion CONCAT(first_name, last_name) LIKE '%%' -- Put in the name you're looking for and remove email
A quick note: “--” tells SQL to ignore anything after it. It’s a way to add human readable context or “comments” to your queries.
Using the above as a base, we can make a few small tweaks in order to ask the database to show us every person with the email address “kcussen@litefarm.org”, their full name, their role on a farm, and the name of the farm where they have that role:
In this case, there is 1 user, with access to 2 different farms (Joykill Farms and Cultivando sonrisas) where they are the Farm Owner and Farm Manager, respectively.
A quick note: Role_id’s can be translated into things like “Farm Owner” by querying the ‘Role’ table, e.g. “SELECT role FROM roles WHERE role_id = 1”. This table doesn’t change, so you should quickly be able to memorize which number is which role. I’ve shown the Role table contents below:
We could also turn this query around and find information about farms by making the farm the target of the query (the table named after ‘FROM’). We could then pull in the people working on those farms by joining on the farms that are returned. For example, we might want to know everyone that is a “Farm owner” on a farm in the US.
A quick note: Here I’m identifying the country based on the country_id foreign key held in the farm table. You can query the ‘countries’ table to see which country_id’s map to which country. The United States is 212. Canada is 37. Farms created on LiteFarm prior to May 2021 may not have a country_id (due to a bug that was fixed at that time), so you may need to query based on a combination or country_id, address, and currency if you’re trying to find older farms.
There are innumerable ways to query information about farms and users, so please bring any specific problems you may have in mind to our training and we’ll work them through together.
Locations
