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
Navigation of the schema using foreign keys
from LiteFarm
No prior knowledge is necessary, though a basic understanding of relational databases will help.
...
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 Northwind data set 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?
...
Below is a query that will join the user, userFarm, and farm tables by searching for name or email address of the user. By joining these tables, we see which farms each person has access to, their roles at those farms, and the details of the farm.
| Code Block |
|---|
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 |
...
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. The 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:
| Code Block |
|---|
AND deleted = FALSE |