0

Given:

"users" table:

[{
 id: 1,
 school_ids: [1, 2, 3] // text type cast as array
}]

Then:

"schools" table:

[{
  id: 1,
  name: 'school 1",
},
{
  id: 2,
  name: 'school 2",
},
{
  id: 3,
  name: 'school 3",
}]

I currently have a relationship in my User model as:

public function schools() {
  return $this->belongsToMany(School::class, 'school_ids');
}

which currently doesn't work.

  1. I was wondering if I can make it work where if it reads the school_ids it will cast it as an array and calling User::schools() will give me a collection of School Model?
  2. If #1 can be done, I wonder if I can implement it on Laravel nova (?)
I am L
  • 4,288
  • 6
  • 32
  • 49
  • 1
    Storing an array as a string in `school_ids` is not the proper way to do a `belongsToMany()`; That requires a `pivot` table (`school_user`), with `school_id` and `user_id`. Consider normalizing your database and migrating this information accordingly. – Tim Lewis Jun 10 '21 at 13:55
  • @TimLewis now that you mentioned it, I do agree with that suggestion as it would be very hard to know which user is enrolled on that school if you query it from schools. – I am L Jun 10 '21 at 14:00
  • 1
    Exactly; if you tried to query to find which User has school 1, for example, it would be `SELECT * FROM users WHERE school_ids LIKE '%1%';`, which would find school 1, 11, 12, 13, 14, etc... There's methods around this, like `FIND_IN_SET`, etc, but if they're separated in a pivot table, it's _much_ easier. `SELECT user_id FROM school_user WHERE school_id = 1` would get you a list of Users associated with School 1, and not 11, 12, etc. You could then join/subquery and get the actual `users` rows – Tim Lewis Jun 10 '21 at 14:02
  • @TimLewis and I guess using LIKE %1% is much slower than joining a table? or it depends on the query? – I am L Jun 10 '21 at 14:07
  • 1
    In most instances, yes, `LIKE` is a pretty slow operation. `join()`, and checking against an id using `=` or `IN()` is _much_ faster, especially if you index properly. – Tim Lewis Jun 10 '21 at 14:10
  • 1
    @IamL Please post your chosen solution as an answer below and mark it as the accepted answer rather than editing it into the question in an attempt to "close" the question via words. "Closure" means something specific on Stack Exchange sites. See the [help] for more info. – TylerH Jun 10 '21 at 15:13

2 Answers2

1

By relation i guess you can't but you can make something like this

public function schools() {
  return Schools::whereIn('id',$this->school_ids)->get(); // Will return collection of Schools object 
}
anas omush
  • 322
  • 1
  • 7
  • yep that's can be done, althought I won't be able to use that when I implement it on laravel nova as it is quite field specific. Thanks! – I am L Jun 10 '21 at 13:54
0

So to make my life easier, I decided to create a pivot table instead that links the school and user.

I am L
  • 4,288
  • 6
  • 32
  • 49