How to get data from another table referenced by a foreign key?

Question

This is the table I want to get the data from, I am using express to make the get request from the next application:

model Provider {
  id          String    @id @default(cuid())
  name        String
  email       String
  password    String
  phone       String      
  photo       String?
  service     Service?  @relation(fields: [serviceId], references: [id])
  serviceId   String?
  location    Location? @relation(fields: [locationId], references: [id])
  locationId  String?
  createdAt   DateTime  @default(now())
  text        String
  starAverage Float
  medals      Medals[]
  comment     Comment[]
}

model Service {
  id       String     @id @default(cuid())
  type     String
  provider Provider[]
}

I want to get the service type of the provider service table, not the serviceId, this is my route.

router.get('/list', async (req: Request, res: Response) => {
  const allClients = await prisma.client.findMany()
  res.json({allClients})
})

This is how I get the rest of the API data using axios

const [providers, setProviders] = useState([] as any[])

  useEffect(() => {
    axios.get('http://localhost:5140/providers/list')//my list of all providers
    .then(res => {
      console.log(res)
      setProviders(res.data)
    }).catch(err => {
      console.log(err)
    })
  }, )
  const renderedProviders = Object.values(providers).map(provider => {
    return (
      <div
        className="card"
        style={{ width: "18rem"}}
        key={provider.id}
      >
        <img className="card-img-top" src="..."/>
        <div className="card-body">
          <h3>{provider.name}</h3>
          <p>{provider.starAverage} estrekas</p>
          <p>{provider.serviceId}</p>
        </div>
      </div>
    );
  });
  return (
    <div className="d-flex flex-row flex-wrap justify-content-between">
      {renderedProviders}
    </div>
  )

Currently only the serviceId of the provider can be obtained, not the type of service

Answer 1

To get data from another table referenced by a foreign key in the database, you can use the JOIN clause in your SQL query. The JOIN clause allows you to combine rows from two or more tables based on related columns between the tables.

This is how to use the JOIN clause to get data from two tables (users and orders) that are related by a foreign key.

SELECT users.*, orders.*
FROM users
JOIN orders ON orders.user_id = users.id

The

JOIN clause combines rows from the users and orders tables based on the user_id column. The id column in the code>orders table and the users table. The SELECT clause specifies the columns to retrieve from the users and orders tables.

Edited How can I reference it in Express routes and http requests from axios?

You can use sequelize.query (Sequelize is a promise-based Node.js ORM) method to execute raw SQL queries.

app.get('/users/:id', (req, res) => {
  const { id } = req.params;
  const query = `
    SELECT users.*, orders.*
    FROM users
    JOIN orders ON orders.user_id = users.id
    WHERE users.id = :id
  `;
  const replacements = { id };
  sequelize.query(query).then(([results, metadata]) => {
    res.send(results);
  });
});

sequelize.query method is used to execute a raw SQL query with a JOIN clause to get data from the Users and Orders tables.