Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 9 Next »

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.

  • 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)

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.

  1. What is SQL? (4 minutes)

  2. Basic SQL queries (7 minutes)

  3. ORDER BY and LIMIT (3 minutes)

  4. Filtering using the WHERE clause (11 minutes)

  5. Basic aggregations (6 minutes)

  6. Advanced aggregations (6 minutes)

  7. String functions (12 minutes)

  8. Rounding (3 minutes)

  9. Formatting dates using TO_CHAR (4 minutes)

    1. TO_CHAR reference sheet

  10. Date arithmetic using DATE_TRUNC (7 minutes)

  11. Joins for beginners (15 minutes)

  12. Combining queries (5 minutes)

  13. Case statements (6 minutes)

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:

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

Email

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.

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.

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

For example, we can make a few small tweaks to the above 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 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

  • No labels