Complicated relationship with 2 Thorough Models

Question

I have complicated relationship that includes 2 thorough models. I want to create custom method that solves my purpose. I have MySQL DB as datasouce.

3 Main Models.

Artist, Album, Songs.

Album and Song are related 2 each other with N:N relationship through model "Tracklist". Artist and Tracklist are connected through thorough model "TracklistArtist". I wish to get list of albums and songs of particular artist through this relationship. Though I achieved with patches but it returns duplicate list and same time I want unique record list. Please help.

I am just mentioning Model relation object here instead of complete model-object.

artist.json

  {
    "name": "Artist",    
    "relations": {
      "tracklists": {
        "type": "hasMany",
        "model": "Tracklist",
        "foreignKey": "",
        "through": "TracklistArtist"
      }
    }     
  }

album.json

{
"name" : "Album",
"relations": {
    "songs": {
      "type": "hasMany",
      "model": "Song",
      "foreignKey": "",
      "through": "Tracklist"
    }
  }
}

song.json

{
"name" : "Song",
  "relations": {
    "albums": {
      "type": "hasMany",
      "model": "Album",
      "foreignKey": "",
      "through": "Tracklist"
    }
  }
}

tracklist.json

{
  "name": "Tracklist",
  "relations": {
    "album": {
      "type": "belongsTo",
      "model": "Album",
      "foreignKey": ""
    },
    "song": {
      "type": "belongsTo",
      "model": "Song",
      "foreignKey": ""
    },
    "artists": {
      "type": "hasMany",
      "model": "Artist",
      "foreignKey": "",
      "through": "TracklistArtist"
    }
  }
}

tracklist-artist.json

{
  "name": "TracklistArtist",
  "relations": {
    "artist": {
      "type": "belongsTo",
      "model": "Artist",
      "foreignKey": ""
    },
    "tracklist": {
      "type": "belongsTo",
      "model": "Tracklist",
      "foreignKey": ""
    }
  } 
}

I have checked patched from mysql-connector of loopback. Here is link. Check Here

 Artist.tracklists({
                id: $stateParams.artistId,
                filter: {
                   groupBY:"albumId",
                   include: {relation: "album", scope: {}},
                }
            }).$promise.then(function (data) {
            });

Can show your actual code / query ? http://stackoverflow.com/help/mcve — Cyrbil, Aug 31 '15 at 07:46
That could help, plus the relevant part of code you use to get list of albums and songs of particular artist — Cyrbil, Aug 31 '15 at 08:02
I have mentioned model relationship here. As I need to create custom function so I haven't mentioned code What I wrote. — Sankalp, Aug 31 '15 at 08:22
Great :) Last thing, what module/framework/orm are you using for the database part ? — Cyrbil, Aug 31 '15 at 08:29
I am using Loopback, what they are internal using I don't know. — Sankalp, Aug 31 '15 at 08:31

Cyrbil · Answer 1 · 2015-08-31T12:22:39.267

0

You propably will need to chain your include to get every relations with something like:

Artist.find({
  include: {
    relation: 'Tracklist',
    scope: {
      include: {
        relation: ['Album', 'Song']
      }
    }
  },
  where: {id: 12345}
}, function() { ... });

Edit:

Artist.find({
  include: {
    relation: 'Tracklist',
    scope: {
      include: {
        relation: ['Album']
      },
      scope: {
          include: {
            relation: 'Tracklist',
            scope: {
                include: {
                   relation: ['Song']
                }
            }
        }
    }
  },
  where: {id: 12345}
}, function() { ... });

edited Aug 31 '15 at 12:22

answered Aug 31 '15 at 08:52

Cyrbil

6,341
1
24
40

That I have tried, it gives me duplicate entries of "Albums". Because I have many tracklists. I need unique list of albums and songs. – Sankalp Aug 31 '15 at 08:55
From your scheme, a song can be in many albums so it is normal that you get multiple entry of album if a song is reference in many albums, isn't it ? – Cyrbil Aug 31 '15 at 10:05
Ofcourse relation you mentioned is correct. Artist sings variours songs in album. On same time he has sung same song in different album. Its ok. This is what the logic I want. I need "Unique Separate list of Albums and Songs". The patch I wrote earlier is same thing you gave example. I tried but I wish to create unique list with custom function. – Sankalp Aug 31 '15 at 10:16
Maybe join albums, then includes song for this album... will edit my answer once I have so time.. – Cyrbil Aug 31 '15 at 10:55
@Sankalp: Could you give a try with my second example ? – Cyrbil Aug 31 '15 at 12:23
I would expect to find either `Album` or `Song` list at a time. Not by any relation. These 2 are separate list and want one at a time. But I want `UNIQUE` list. Am I clear with my point? – Sankalp Aug 31 '15 at 12:50
Well, you can also use a raw sql query that will probably be the more efficient... – Cyrbil Sep 01 '15 at 07:09
Well I have gone thorough patch also. but that didn't work on relational model. I am editing my question with my try. Please check along with this patch. https://github.com/strongloop/loopback-connector-mysql/pull/44#issuecomment-136604124 – Sankalp Sep 01 '15 at 07:24

Complicated relationship with 2 Thorough Models

1 Answers1