Versions Compared

Old Version 9

changes.mady.by.user Lite Farm

Saved on

compared with

New Version 10

changes.mady.by.user Lite Farm

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

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

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:

Code Block
AND deleted = FALSE

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 by searching for .

Image Added

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 of the userand 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.

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
For example
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 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:
 Code BlockAND deleted = FALSEIn 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:
Image Added
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. 
Image Added
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.
Image Added