Kimmer's avatar

Inserting multiple records with al kinds of related models

This is rather complicated. I’ll try a explain as clearly as possible.

Models:

I have 3 models Track, Song and Artist. A track has one song, a song can belong to many tracks, a song can have many artists and an artist can belong to many songs. (see relationships below at "Models relationships")

Database tables:

The Database tables are tracks, songs, artists and artist_song (pivot). (see the fields below at "Database tables and fields")

Scenario:

Via an API call to the Spotify API data is returned. The returned JSON contains data from songs which have one or more artists. (see JSON return below at "Returned API data")

What I want to do:

For every song in the JSON…

  • create a record in the songs table with the song info (“name” to “title” field)
  • create a record in the tracks table with the ID of the song
  • create a record for every artist in the artists table
  • attache the artists to the correct songs via the pivot table.

With as few queries as possible

What I can do:

I am able create all records but with queries in loops which could result in too many DB queries.

I can write the 3 songs in one query (see last code block) but I don’t know how to proceed with writing the tracks and artist for these songs.

So...

Anyone willing to nudge me in the right direction?

Thanks!

Models relationships

// Track
public function song() {
    return $this->belongsTo('App\Models\Song');
}

// Song
public function tracks() {
    return $this->hasMany('App\Models\Track');
} 
public function Artists() {
        return $this->belongsToMany('App\Models\Artist');
}

// Artist
public function Songs() {
    return $this->belongsToMany('App\Models\Song');
}

Database tables and fields (minimised to only the fields needed for this question)

tracks
    - id
    - song_id

songs
    - id
    - title

artists
    - id
    - name

artist_song
    - song_id
    - artist_id

Returned API data (example, minified to only the data needed for this question)

{
  “songs”: [
    {
      "artists": [
        {
          "name": "Dreadzone"
        }
      ],
      "name": "Digital Mastermind",
    },
    {
      "artists": [
        {
          "name": "Damian Marley"
        },
        {
          "name": "Stephen Marley"
        },
        {
          "name": "Rovleta Fraser",
        }
      ],
      "name": "Hey Girl",
    },
    {
      "artists": [
        {
          "name": "The Wailers"
        },
        {
          "name": "Alpha Blondy"
        }
      ],
      "name": "Jerusalem",
    }
  ]
}

Inserting the 3 songs in one query

$songs = collect();

foreach ($spotifyTracksData->songs as $song) {
    $songs->push(['title' => $song->name]);
}

$return = Song::insert($songs->toArray());
1 like
5 replies
aprylducote's avatar

Hey Laracasts Community,

Inserting multiple records with related models like Track, Song, and Artist requires careful handling of relationships. First, check if the Song and Artist already exist to avoid duplicates. If not, insert them and retrieve their IDs. Then, create the Track record and link it to the corresponding Song. Finally, update the pivot table (artist_song) to establish the many-to-many relationship between Songs and Artists. Using transactions ensures data integrity, and batch inserts improve efficiency. Let me know if you need specific implementation details...just visit the website and explore the world of entertainment... www.netmirrors.app

Great Job, and Happy Coding!!

Thanks a lot!!

1 like
vincent15000's avatar

Even if it needs several queries, I would use the ->updateOrCreate() function to update or store each new song.

Here is the global id, assuming that the data are a collection and not an array and assuming that you can't have two songs with exactly the same title (which could be wrong). Furthermore you can also identify if the artist already exists by using other fields like explained in the documentation.

https://laravel.com/docs/11.x/eloquent#updates

foreach ($songs as $song) {
	$artistIds = [];

	foreach ($artists as $artist) {
		$artist = Artist::updateOrCreate($artist);

		$artistIds[] = $artist->id;
	}

    $song = Song::updateOrCreate($song);

	$song->artists()->sync($artistIds);
}
1 like
Tray2's avatar

I did it this way

 public function __invoke(RecordFormRequest $request, TracksService $tracksService, ForeignKeyService $foreignKeyService)
    {
        $valid = $request->validated();
        $record = Record::create(array_merge($valid, [
            'genre_id' => $foreignKeyService->getGenreId($request->genre_name, 'record'),
            'format_id' => $foreignKeyService->getFormatId($request->format_name, 'record'),
            'artist_id' => $foreignKeyService->getArtistId($request->artist),
            'country_id' => $foreignKeyService->getCountryId($request->country_name),
            'record_label_id' => $foreignKeyService->getRecordLabelId($request->record_label_name),
        ]));

        $tracksService->storeTracks([
            'track_count' => count($valid['track_positions']),
            'track_positions' => $valid['track_positions'],
            'track_titles' => $valid['track_titles'],
            'track_durations' => $valid['track_durations'],
            'track_mixes' => $valid['track_mixes'] ?? null,
            'track_artists' => $valid['track_artists'] ?? null,
            'record_id' => $record->id,
            'record_artist' => $request->artist,
        ], $foreignKeyService);

        return redirect(route('records.index'));
    }

You can check the repo here.

https://github.com/Tray2/mediabase/tree/main

2 likes
vincent15000's avatar

@Tray2 In most of your answers in the posts, I notice that you rarely use ->fill() to populate a model.

1 like
Tray2's avatar

@vincent15000 I prefer passing the data directly to the create method instead, I could of course create a new instance of the model, fill it, and then save it. It is just a preference.

1 like

Please or to participate in this conversation.