Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.
I have a table with called “songs” which contains songs that have a “title”.
I also have a table called “artists” which contains artists that have a “spotify_id”.
A song can have many artist and an artist can blog to many songs so I have a pivot table “artist_song” which connects the “songs” and “artists” tables
In my models I have...
class Song extends Model
{
public function Artists() {
return $this->belongsToMany('App\Models\Artist');
}
}
class Artist extends Model
{
public function Songs() {
return $this->belongsToMany('App\Models\Song');
}
}
Via an API call this response comes in (extremely minified to only contain elements needed for this issue)
{
"artists" : [ {
"id" : “Artist1”
}, {
"id" : “Artist2”
} ],
"name" : “Cool Song“,
}
So far so good. Now I want to check if the song exists in the database by it’s title and all artists for this song.
The result can only be positive if the title and all artists are identical. Based on the example, should the song "Cool Song" exist in the database with “Artist1” and “Artist2” and the API call returns “Cool Song“ with only “Artist1”, the result of the query should be negative because not identical.
The amount of artists will be variable depending on the song. Most songs only have one artist but some songs can have a lot of artists.
What would the Eloquent query look like? A very helpful colleague quickly typed something like this on a notepad.
$artists = $track->artists;
$existingSong = Song::with('Artists', function($query) use ($artists) {
foreach($artists as $artist) {
$query .= $query->where('spotify_id', $artist->id);
}
return $query;
})->get()->where('title', $track->name);
return $existingSong;
But this results in this error:
ErrorException in Str.php line 72:
mb_strpos() expects parameter 1 to be string, object given
Thanks!
this might do it:
$ids = $track->artists->pluck('ids')->toArray();
return Song::whereIn('spotify_id', function ($query) use ($ids) {
$query->select('spotify_id')->from('artists')->whereIn('artist_id', $ids->toArray());
})->where('title', $track->name)->exists();
dont know if I got the artist table right. is it artists?
Thanks for your reply @robrogers3. Yes the table is “artists”
Sadly I can’t get it working.
Without editing your code returns:
Call to a member function pluck() on array
After some searching I managed to have this code return an array of ids from the incoming API return and solve the above error.
$ids = collect($track->artists)->pluck('id')->toArray();
Your code with the first line replaced with the edited version returned this error.
Call to a member function toArray() on array
I removed the ->toArray() from line three but the resulting code…
$ids = collect($track->artists)->pluck('id')->toArray();
return Song::whereIn('spotify_id', function ($query) use ($ids) {
$query->select('spotify_id')->from('artists')->whereIn('artist_id', $ids);
})->where('title', $track->name)->exists();
… returns this error
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'artist_id' in 'where clause' (SQL: select exists(select * from `songs` where `spotify_id` in (select `spotify_id` from `artists` where `artist_id` in (0jHCJWgwdcqysvbKWjXSI6, 41ekW4MXG59xJMXR8dX1OG)) and `title` = Jerusalem) as `exists`)
'artist_id' does not exist in the artists table so I changed the code to this
$ids = collect($track->artists)->pluck('id')->toArray();
return Song::whereIn('spotify_id', function ($query) use ($ids) {
$query->select('spotify_id')->from('artists')->whereIn('spotify_id', $ids);
})->where('title', $track->name)->exists();
This returns no error but, sadly, does not find the existing song.
Humm, well I got the query wrong. Given your db design, does it make sense to you?
So, I'm guessing there is no artist_id in songs (perhaps you have a pivot table I'm missing?)
looking at your updated query, i the $ids your are collecting are wrong. You are using artist.ids instead spotify.ids, but you need spotify ids. Do you want to look for spotify.ids or artist.ids. if spotify, you need those ids. do artists have a spotify id? If so just do that,
$ids = collect($track->artists)->pluck('spotify_id')->toArray();
note it's weird you have to do a collect(), $track->artists should return an eloquent collection.
$track is the JSON return I get from Spotify after an API call. This is the minified version of the JSON return. This return holds the data I'm using to find the existing song in the database. I need to know if that song already exists.
{
"artists" : [ {
"id" : “Artist1”
}, {
"id" : “Artist2”
} ],
"name" : “Cool Song“,
}
No there is no artist_id in the songs table. There’s a pivot table. songs and artists have a many to many relationship. These are my migrations. I removed the columns that have no use for the issue in this threat.
Schema::create('songs', function (Blueprint $table) {
$table->increments('id');
$table->string('title');
});
// all references to artists are in the Pivot table ‘artist_song’
Schema::create('artists', function (Blueprint $table) {
$table->increments('id');
$table->string('spotify_id', 30);
});
// all references to songs are in the Pivot table ‘artist_song’
// Pivot table
Schema::create('artist_song', function (Blueprint $table) {
$table->integer('song_id')->unsigned()->index();
$table->foreign('song_id')->references('id')->on('songs')->onDelete('cascade');
$table->integer('artist_id')->unsigned()->index();
$table->foreign('artist_id')->references('id')->on('artists')->onDelete('cascade');
});
So I’m searching for spotify_id in the artists table.
I’m fairly new to all of this. I don’t really know why collect() is needed. My code is in a class I made which is in a folder “library” that I created. Maybe I need a certain namespace or a ‘use’?
Edit: I was wrong, nm
I've been occupied with other things for a couple of days but I've been searching for a solution on this problem again for several hours. Anyone willing to help me crack this, please?
This seems to work.
It seems to search for a song in the database which
public static function songExistsByTitleArtists($track) {
// Create array from artists IDs of a track coming in from a /tracks call to the Spotify API.
// $artists = ['0jHCJWgwdcqysvbKWjXSI6', '41ekW4MXG59xJMXR8dX1OG'];
$artists = collect($track->artists)->pluck('id');
$query = song::query();
// Loop over the IDs in the $artists array and create a whereHas() statement for every ID
foreach ($artists as $artist) {
$query->whereHas('artists', function ($q) use($artist) {
$q->where('spotify_id', $artist);
});
}
// Compose the query
$existingSong = $query
->where('title', $track->name) // Compare incoming track title with song title in DB
->has('artists', count($artists)) // Compare if the amount of artist in existing track is the same as the amount of IDs in the $artists array
->first();
return $existingSong;
}
Hello! It seems like you're working with a many-to-many relationship between your "songs" and "artists" tables, connected through the "artist_song" pivot table. To perform an eloquent query that retrieves values from both tables where all values are present, you can use the whereHas method. This method allows you to add constraints to your query based on the existence of a related model.
In your case, you can use something like:
$songsWithArtists = Song::whereHas('artists', function ($query) { $query->whereNotNull('spotify_id'); })->get();
This query will retrieve all songs that have associated artists with a non-null "spotify_id". Adjust the conditions within the closure according to your specific requirements.
By the way, if you're into music and looking for a great platform, check out. It's an excellent website for music lovers where you can discover, listen to, and download your favorite songs. The installation is pretty straightforward – just visit the website and explore the world of music... www.spotifyguru.com
Please or to participate in this conversation.