How can i fetch data from another table referenced by foreign key?-CodePudding

Heres my tables that a wanna fetch the data, i am using express to make a get request from the next app:

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 wanna fetch the type of the service of the services table of a provider, not the serviceId, and 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 am fetching the data of the rest API 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>
  )

for now a only get the serviceId of a provider, not the type of the service

CodePudding user response：

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

this how you can use a JOIN clause to fetch 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 in the orders table and the id column in the users table. The SELECT clause specifies the columns to be retrieved from the users and orders tables.

Edited how you can reference this in the express route and in the http request from axios ?

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

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);
  });
});

the sequelize.query method is used to execute a raw SQL query that includes a JOIN clause to fetch data from the users and orders tables.