如何在Prisma中从MySQL关联表获取数据？数据模型定义是否正确

Hey there! Let's break down what's going on with your data model and how to fix it so you can fetch associated passenger, origin, and destination data properly.

Why You're Seeing Those Join Tables #

The core issue here is that your GraphQL model doesn't explicitly define the type of relationship between Ride and the other entities (Passenger, Origin, Destination). When you define a field like passenger: Passenger without specifying a relation type, your ORM (likely Prisma, given the @unique directive) defaults to creating a many-to-many relationship, which requires those join tables (_PassengerToRide, etc.) to manage the associations. That's why those foreign key fields never showed up on the Ride table directly.

Fixing the Data Model #

To get the one-to-many (or one-to-one) relationships you probably intend, you need to use the @relation directive to explicitly map foreign keys and reference the target entity's primary key. Here's the corrected model:

type Ride {
  rideId: String
  productId: String
  # Define relation to Passenger with explicit foreign key
  passenger: Passenger @relation(fields: [passengerId], references: [id])
  passengerId: ID!
  # Define relation to Origin with explicit foreign key
  origin: Origin @relation(fields: [originId], references: [id])
  originId: ID!
  # Define relation to Destination with explicit foreign key
  destination: Destination @relation(fields: [destinationId], references: [id])
  destinationId: ID!
  dateTime: DateTime
  feedback: String
}

type Passenger {
  id: ID! @unique
  firstName: String
  lastName: String
  # Optional: Reverse relation to fetch all rides for a passenger
  rides: [Ride] @relation(references: [passengerId])
}

type Destination {
  id: ID! @unique
  latitude: Float
  longitude: Float
  address: String
  # Optional: Reverse relation to fetch all rides ending here
  rides: [Ride] @relation(references: [destinationId])
}

type Origin {
  id: ID! @unique
  latitude: Float
  longitude: Float
  address: String
  # Optional: Reverse relation to fetch all rides starting here
  rides: [Ride] @relation(references: [originId])
}

type Report {
  productId: String
  # Fix relation for Report -> Passenger too
  passenger: Passenger @relation(fields: [passengerId], references: [id])
  passengerId: ID!
  description: String
}

Key Changes: #

• Added explicit foreign key fields (passengerId, originId, destinationId) to the Ride (and Report) type
• Used @relation to link each object field to its corresponding foreign key, specifying which primary key it references in the target entity
• Added optional reverse relations (like rides on Passenger) to enable querying from the opposite direction if needed

Fetching Associated Data #

Once you redeploy this corrected model, your Ride table will have the foreign key fields instead of join tables. You can then fetch nested associated data directly in your GraphQL queries like this:

query GetAllRidesWithDetails {
  rides {
    rideId
    productId
    dateTime
    feedback
    # Fetch nested Passenger data
    passenger {
      id
      firstName
      lastName
    }
    # Fetch nested Origin data
    origin {
      id
      latitude
      longitude
      address
    }
    # Fetch nested Destination data
    destination {
      id
      latitude
      longitude
      address
    }
  }
}

Quick Notes #

• If you're using Prisma, after updating the model, you'll need to run a migration to apply these changes to your MySQL database.
• The reverse relations are optional—only include them if you need to query, say, all rides for a specific passenger.

内容的提问来源于stack exchange，提问作者John Davis