FIND_IN_SET in hasMany Relation

1

I have Two Tables One For Artist And second one Is For Tracks. Tracks have audio stream data like track title,track artist and artist_id column in Track table for Artist relationship. Artist table have artist detail like artist name artist bio and so on. In Track table artist_id column save artistid when user upload track then user select artist name from list and that artist saved into track table. User can select one artist or more artist… let me explain it… if user select single artist then its okay i will get artist detail and their tracks also with hasmany relationship but when user choose multiple artists then get problem… because when multiple artists save into tracks table… artist_id save like this 1001,1002,1009… so like record i need FIND_IN_SET() method for find values. hasMany relationship not showing this multi artist track only shows single artist track shows… hasMany give me show error

$this->hasMany(‘artistTracks’,[
‘className’=>‘CkTracks’,
‘foreignKey’=>‘artist_id’,
//‘bindingKey’=>‘artistkeyid’,
‘conditions’=>‘FIND_IN_SET(artistkeyid,artist_id)’
]);

now artistkeyid show me undefined column because artistkeyid exist into Arstist table as a show in screen shot… please tell me how can I resolve this problem FIND_IN_SET condition in hasMany.

Screenshot from 2023-03-19 13-13-17
Screenshot from 2023-03-19 13-13-171920×1093 221 KB

2

The conditions array needs to be structured like a ->where() call in a query instance.
So something like

'conditions' => [
    'name' => 'test',
    'amount >' => 3
]
3

never sir, It will not work as you think. artistkeyid not exist in track table but track have artist_id as foreign key for relationship with artist table. Artistkeyid exist in Artist Table as a primary key… When I access Artist list then Need to show artist Tracks also. I am using hasMany relationship in ArtistTable Model
$this->hasMany(‘artistTracks’,[
‘className’=>‘CkTracks’,
‘foreignKey’=>‘artist_id’,
//‘bindingKey’=>‘artistkeyid’,
//‘conditions’=>‘FIND_IN_SET(artistkeyid,artist_id)’
]);
‘conditions’=>‘FIND_IN_SET(artistkeyid,artist_id)’ I need this FIND_IN_SET method… You can see artistkeyid set into FIND_IN_SET method but it is return error as a undefined column, Event Try LIke this CkArtist.artistkeyid but this is also return Error as the same way undefined column.

Screenshot from 2023-03-19 17-51-16
Screenshot from 2023-03-19 17-51-161641×423 48.6 KB

4

Screenshot from 2023-03-19 17-51-44
Screenshot from 2023-03-19 17-51-441643×763 94.6 KB

this is Artist Model and I defined here hasMany Relationship for Artists.

When I fetch Artists then i also fetch tracks related to artists… In track table artist_id saved like this way 1001,1002(comma separated values) so if artist_id is single value Like only 1001 then track show with Artist list but if Track Artist_id have 1001,1002,1003**(comma separated)** values then its not work for that track. It will work if FIND_IN_SET(artistkeyid,artist_id) execute into hasMany relationship like do in screenshot. artistkeyid column exist in Artist table not in Track Table so that way it return Error Unknown column ‘artistkeyid’… give me solution how to access Artist.artistkeyid enitity into hasMany Relationship

5

First things first, 99.9% of the time when you feel the need to use FIND_IN_SET, this just means that your database schema is flawed, and that you need to look into better normalization!

So my question would be why do you store the data in that way in the first place? Why not use an M:N relation, ie belongsToMany, where a track can belong to many artists, and an artist can belong to many tracks?

btw, your FIND_IN_SET doesn’t work because hasMany associations (and belongsToMany for that matter) are always being retrieved in a separate query, which doesn’t have access to any parent data (except for the binding key values that are passed into the conditions).

6

Thanks ndm For Reply and such Great Advise… me also thought that should be use belongsToMany relationship. Actually this approach is also fine but as you mention above FIND_IN_SET Depend on Database schema, so yes its does not work on many database or I have manually set this schema for CMS. So Yes Now I Am Going to make belongsToMany relationship between Artist and tracks. Thanks once again ndm

7

hi ndm, I need your help. I created new table artists_tracks table for belongstomany relationship and setup:
$this->belongsToMany(‘CkArtists’,
[
‘targetForeignKey’ => ‘artist_id’,
‘foreignKey’ => ‘track_id’,
‘joinTable’ => ‘CkArtistsCkTracks’,

    ]);

Now the the problem is that when i add new track and select artist, then I checked database table artists_tracks add only one id if select two artist… and artist table also save new empty data.

I Do’t want to add new artist when new tracks submit but now empty artists also added in database and that artist saved into artists_tracks table.

8

Sounds like you’re possibly not structuring the data properly for patching/saving, or your saving logic is doing something wrong.

Make sure that you’ve read the docs on converting and saving belongsToMany data, and how to create form controls for it.

9

yes I am sending direct value into nested array. Now I set ck_artists=>[‘_ids’]=>[[0]=>1111,[1]=>10], This resolve my problem and now get exactly what i want… Thank God it resolve otherwise I am ready move my project to Laravel.
You have any I idea about inertiajs? actually it is working fine in laravel but not in cakephp. I think Cakephp have limitation there is no more plugin or libraries which easily configure with cakephp with less code.