The Database - Part 3: Relationships, Addons and Joins

Hello there! Let's dive into the exciting world of data relationships in Xano and learn how to effortlessly query and display your data on the front-end. In this guide, we'll cover one-to-one, one-to-many, and many-to-many relationships, and show you how to leverage Xano's powerful add-ons feature to fetch and combine data from multiple tables.

Setting the Stage

Before we begin, make sure you've watched the previous videos in the database series, where we covered the basics of databases, SQL vs. NoSQL, and traditional database querying. We'll be working with a job board application that allows companies to post jobs at various locations.

Let's set up our workspace by adding some sample data:

1. Companies: We have two companies, Xano and Google.
2. Locations: We have three locations: Los Angeles, Paris, and Berlin.
3. Jobs: We have six jobs in total, with different companies and locations.
4. Users: Each user is an owner of a company. For example, Michael is the owner of Xano, and you are the owner of Google.

Now that we have some data to play with, let's explore how to query and display it using Xano's add-ons.

One-to-One and One-to-Many Relationships

Let's start by fetching data for a company, including its associated owner (one-to-one relationship) and the jobs associated with the company (one-to-many relationship).

1. Go to your API and navigate to the `get company` route.
2. In the output section, click Customize and remove the `locations` field, as we won't be using it for now.
3. Click the Add-on* button and select *User from the table dropdown.
4. Choose Single Item since we want to fetch a single owner for each company.
5. Map the `user.company` field to the `company.id` field.
6. Name this add-on "owner" and click Done.
7. Add another add-on by clicking Add-on again.
8. This time, select Job* from the table dropdown and choose *List of Items.
9. Map the `job.company` field to the `company.id` field.
10. Name this add-on "jobs" and click Done.
11. Click Save and run the API.

You should now see the company name, the owner's details, and the list of jobs associated with each company in the response. Neat, right?

Many-to-Many Relationships

Now, let's explore how to fetch locations and their associated jobs (a many-to-many relationship).

1. Go to the API and navigate to the `get location` route.
2. In the Library* section, click *Add-ons and create a new add-on named "job of location".
3. Set the Table Reference on the input to `location`.
4. Select the Job table from the dropdown.
5. Click the pencil icon next to the Join* field and select the `locations` array from the *Job table.
6. Add a condition that the `location.id` from the input equals the `location.id` from the virtual table created by the join.
7. Click Done* and *Save.
8. Go back to the API and add the "job of location" add-on to the output section.
9. Name this add-on "jobs" and click Done.
10. Customize the response by removing the `locations` field from the `jobs` add-on.
11. Click Save and run the API.

You should now see each location along with the list of jobs associated with it. Brilliant!

Crafting the Perfect Front-end Experience

With these API endpoints in place, you can now effortlessly display data on your front-end application. One endpoint will show the company name, owner details, and associated jobs, while another will display locations and their corresponding jobs.

Imagine a page that lists different cities, with each city showing the available job openings. On another page, you can display the company details, including the owner's information and the jobs they have posted. All of this can be achieved with just a few API requests, thanks to Xano's powerful add-ons feature.

Conclusion

Congratulations! You've learned how to query and display data with one-to-one, one-to-many, and many-to-many relationships using Xano's add-ons. This technique allows you to fetch and combine data from multiple tables in a single API call, streamlining your development process and providing a seamless front-end experience for your users.

Keep exploring Xano and unleash your creativity! If you have any questions or need further assistance, don't hesitate to reach out. Happy coding!