How to retrieve and display data with relational DB many to many relationship

Question

This question is about relational database. I am creating an app from the Seatgeek api. Now with many to many relationship you will have to create a junction. Events and Performers have many to many relationship. Hence I created the junction.The eventid is the parent column whereas the performerId is the entity column. Next I created the required method in my Dao.

I created a method in my ViewModel to subscribe to the EventDetails.

it goes like this

private fun subscribeToEventDetails(eventId: Int) {
        viewModelScope.launch {
            try {
                val event = getEventDetails(eventId)

                onEventsDetails(event)
            }catch (t: Throwable) {
                onFailure(t)}
        }
    }

private fun onEventsDetails(event: EventsWithDetails) {

val eventDetails = uiEventDetailsMapper.mapToView(event)

_state.update { EventDetailsViewState.EventsDetails(eventsDetails)}
}

The getEventDetails logic is

class GetEventDetails @Inject constructor(
    private val eventRepository: EventRepository,
    private val dispatchersProvider: DispatchersProvider,
    private val performerRepository: PerformerRepository,
) {

    suspend operator fun invoke(eventId: Int): EventWithDetails {
        return withContext(dispatchersProvider.io()) {
            eventRepository.getEvent(eventId)
        }
    }
}

Everything is working smoothly except one thing.

In EventWith details class. Performer is found there.

Now remember in my post. I already talked about

Event and Performer Having many to many relation.

data class EventWithDetails(val id:Int,
val title:String,
val media:Media,
val details: Details,
val dateTimeLocal:LocalDateTime,
val visibleUntilUtc: LocalDateTime,
val type:String
)

data class Details(val description: String,
val stats: Stats,
val venue: Venue,
val performers: List)

data class UIEventDetailed(val id:Int,
val title:String,
val image:String,
val performer:List,
val description:String,
val averagePrice: Int,
val highestPrice: Int,
val listingCount: Int,
val lowestPrice: Int,
val medianPrice: Int,
)

so I created a mapping between EventWithDetails and UIEventDetailed.

I am able to get all data except list of performer. Every thing you see in details holds eventId as a foreign key I guess that's why. However with Performer and Event I had to create a junction.

My question is how will you be able to get performer data. I did debug though and it shows that no data was in performers

Here is the results I get from debugging

EventWithDetails(id=5866659, title=Alpha 9, media=Media(
images=[Image(huge=https://seatgeek.com/images/performers-landscape/alpha-9-8b77b7/231372/huge.jpg, x320=)]), 

details=Details(description=,
stats=Stats(averagePrice=71, highestPrice=72, listingCount=72, lowestPrice=1, medianPrice=0, visibleListingCount=49, lowestPriceGoodDeals=0, lowestSgBasePrice=71, lowestSgBasePriceGoodDeals=1), 

venue=Venue(id=7336, name=Exchange LA, address=Address(address1=618 S. Spring Street, address2=Los Angeles, CA 90014, city=Los Angeles, state=CA, postalCode=90014, country=US), 
timeZone=America/Los_Angeles, 
info=Info(capacity=1500, numUpcomingEvents=6, venueScore=0.44, url=https://seatgeek.com/venues/exchange-la/tickets, hasUpcomingEvents=true), location=Location(lat=34.0452, lon=-118.251)),

performers=[]),

dateTimeLocal=2023-01-14T21:00, 

visibleUntilUtc=2023-01-15T09:00, type=concert)

Any help will be highly appreciative. If you need more info please do let me know.

ryankuck · Accepted Answer

For many to many relationships, a third table should be used. We have Events, and Performers, so a third Performances table can be added instead of using any lists.

With this system we eliminate the list of performers stored on the event and vice versa. That is 100% handled by the performances table. Starting with say an Event you have the event id and you can look up all the performances relevant, and then for each performance get all the performers. Since you have 3 independent tables and daos, you can add a performer, performance, or event separately without affecting the other 2 tables. There could be meta data for each performance like duration, title, etc which is rightly independent of events and the performers so good single responsibility principle here.

Many to many database design, third table?

Sample new Dao:

@Dao
interface PerformancesDao {
    @Query("SELECT events.* 
        FROM events 
        JOIN performances ON events.id = performances.eve_id 
        JOIN performers ON performances.perf_id = performers.id 
        WHERE performers.name = :name")
    fun getEventsByPerformer(name: String): List
}

Sample new Entity:

@Entity
data class Performances (
    @PrimaryKey(autoGenerate = true)
    var id: Long = 0,

    @ColumnInfo(name = "perf_id")
    var perfId: Long,

    @ColumnInfo(name = "event_id")
    var eveId: Long,

    // more columns for performance details
)

How to retrieve and display data with relational DB many to many relationship

Answers (2)

Related Questions