Database / Eloquent design question: User <> Books <> Author relationship

Hi Andreas,

Are you able to give a little more background on what the relation is between Users and Authors. What kind of app are you building? Do users buy books, read them etc? When you say Users create Authors, do you mean administrators would create and assign an Author to a book? Or that Users can add Authors to books generally (for whatever reason)?

The way I understand you is that a User creates a book. They then tell you what Authors made the book, and select it from a list or add a new one. You then create those authors and attach them to the Book->[hasMany]->Authors relationship. Does the user even need to know which Authors they created? If you insist on the name of the author being unique when making your DB migrations, you will get an error if they try to create the same Author.

If you want to remove duplication and insist that the User knows which Authors they created then you could use a Many-To-Many relationship between User and Author, and again insist through your DB migration that the Author name is unique. That way when a user creates an Author on a book, you also attach the Author to the User->authors() , and you can later pull out which Authors they created. If you pull a Book out, and you want the Authors from that where the only the Authors that the User created are visible, you should use something like:

Book::find(1)->authors()->where('user_id', Auth::id())->get()

Does this help at all? - You're my first post to try and help so I'm new to this game too! :)

Hi @Clmrie

thanks for taking the time to think about my problem so much :-) I wish I could start helping other people too but I am not quite there yet.

Now regarding your question: In my application users should be able to manage books and (among other things) also the authors of the books they added. So Users should know which Authors they have created, yes. As an example, a user could be a Librarian entering "Romeo and Julia" as a book and add "Will Shakespeare" as the author :-)

At the moment my pivot table only has a foreign key to the book.id and the author.id - no user is involved yet.

Because I thought that if the book is created by one specific user, it should be possible to somehow limit the authors via the book to the user?

Thanks Andreas

Hi @andreasb ,

I would model that like so:

class Book extends Model {

    public function authors() {
        return $this->hasMany(Author::class);
    }

    //if you need to record who created the book:
    public function user(){
        return $this->belongsTo(User::class);
    }
}

class User extends Model {

    public function authors() {
        return $this->hasMany(Author::class);
    }

    //the other side of the relation allowing you to see who created a book
    public function books(){
        return $this->hasMany(Book::class);
    }
}

class Author extends Model {
    public function books() {
        return $this->belongsToMany(Book::class);
    }

    public function users() {
        return $this->belongsToMany(User::class);
    }
}

It is the last line in your reply that doesn't make it very clear to me what you want. I could interpret your requirement in two ways:

1. If you want it so that a User can only see Authors they created, you can follow the example in my previous reply: Book::find($book_id)->authors()->where('user_id', Auth::id()))->get() -- or --
2. If you want it so that only the User who owns the Book (i.e. the first to create it) can then edit it or change the authors, you should guard it in some way - but if you want to obey single responsibility, I would say that the Eloquent Models are not the right place to put that. I would recommend using Laravel's Guard (Authorization) functionality (documentation is at http://laravel.com/docs/5.1/authorization). That should allow you to define a policy for Books so that you can only 'update' the Book if you are the authenticated User that owns it.

If you need any more help or if you want more explanation on the Guard facility just let me know - I am using it extensively in my apps at the moment. It's a great feature.

Hello @CImrie

thanks for letting me picking your brain :)

I will have to add the Guard feature, thank you, but for now I would like to concentrate on the underlying object model.

Now actually when reading the relationships you sketched above out loud, it certainly reads very well and I am at the moment currently implementing it. However, one this I am not sure about:

In the Author class, users() function - do I really want to set it in a way where an Author belongtstoMany Users? I think I'd prefer the Authors to be User-specific, even if both may add a "John Doe" Author - because chances are, that this is a different John Doe. And I don't want to start with adding D.O.B or any other identifiers to the Author, I really only need the Name.

Do you think this poses any problem?

EDIT: After re-reading and implementing it, there is a hitch: if a Book belongs to one User exactly, why would I have to define this relationship again in the Author object? Since a Book belongs to ONE User and an Author belongs to ONE User directly, isn't that clear that the User of the Author wil be the same as the User of the Book?

Or to rephase my confusion: In by Books migration, I set a foreign key to the Users table. Does that mean that if I 1)Have a foreign key from by Books table to my Users table and 2)in the Author object define that an Author belongs to a User, Laravel will automatically be able to tell me the User of an Author?

Christ, I think I will simply implement it and test this out for myself :D

Thanks again very much for your help Andreas

Okay, so now I think the proposed model does not make sense, does it? Because Book <> Author is an n:m relationship right? A book can have many authors, and an author can have many books. But correct me if I am wrong, if you use "hasMany" in Eloquent, you are referring to a 1:n relationship - so in the model described above, you use "hasMany" for the Book -> Author relationship, and in the Author -> Book relationship you use belongsToMany.

But according to the documentation the reverse of hasMany is belongsTo, not belongsToMany: http://laravel.com/docs/5.0/eloquent#one-to-many

Right? :-)

So I will use belongsToMany on both ends! Lets try again.. :)

OK - I think it is done: The obvious solution for me was to simple create the User <> Author relationship.

I thought I'd just had to add a foreign key, change the User model a bit and that's it - I can then query very easily which Authors have been created by the user.

Or is this approach somehow not "clean"?

Thanks Andreas

So, to finish this up: this works like a charm. However, after going through the material more, I think that a hasManyThrough relationship (a User hasMany Authors Through Books) would have been the better choice - this way, I will not have to track the user_id for every author.

I will implement tomorrow and let everyone who is interested (me? :-)) know if that works.

Hi @andreasb ,

Sure, You don't need a BelongsToMany Users relationship on Author, but the reason I suggested it was to remove duplication. If you don't care about that and I can see your point about needing DOB identifiers etc then just keep it as a OneToOne relation.

There is one issue I can think of popping up - if you wanted to find out which books were written by a particular author, you would not be able to just select like so: Author::where('name', $author_name')->first()->books

In that case you would probably need to get the collection of Authors ... $collection = Author::where('name', $author_name)->get() and then you would cycle through each of the authors and merge their books in to their own collection to get them, which is a bit more work.

Regarding the relation to User in both Author and Book - if you are creating a new Author instead of trying to re-use existing ones then you don't need one of those relations. If you were implementing it the way I would (re-using each Author and doing the necessary checks to know if they are unique) then you would need a relation on both as I suggested. It depends what you feel is right and what future needs you think you might have.

Sorry, yep, use belongsToMany on both ends - i think you are right. That part of the Laravel API has always confused me.. it always seems a little ambiguous what you are getting so I always end up having to consult the documentation.

Obviously the hasManyThrough relationship again implies you are creating separate Authors (with possible duplicates) for each Book. The reason I had them separate is because in my implementation you could have a case where a User created a Book but didn't own one or more of the Authors of that book. That being said I think this just goes to show there are many ways you can implement something. Good that you have been thinking about it and discussing it - just choose what fits for you :)

Hope I have helped a little! Good luck!

Good morning @Clmrie

first let me start off an state that I tried the hasManyThrough relationship and it doesn't work with my setup, since the Author <> Book relationship is a belongsToMany <> belongsToMany relationship using a Pivot table and on such a relationship it seems one cannot use hasManyThrough since it tries to access information on the Books table which is stored on the Pivot table - not sure if this is a bug or if I am donig something wrong.

Now coming to your reply: actually, it is very easy to get the list of books an author has created:

# php artisan tinker
Psy Shell v0.5.2 (PHP 5.6.12 ÔÇö cli) by Justin Hileman
>>> $author = App\Author::first()
=> App\Author {#711
     id: 1,
     user_id: 1,
     name: "Anna Annannan",
     created_at: "2015-10-10 08:57:44",
     updated_at: "2015-10-10 08:57:44",
   }
>>> $author->books()->lists('title')
=> Illuminate\Support\Collection {#719
     all: [
       "Andreas' first Book",
     ],
   }
>>>

Maybe to finish this off so other people can see how I solved this, here are my Objects with corresponding relationships:

class Author extends Person
{

    /**
     * Get the Book associated with an author
     * @return \Illuminate\Database\Eloquent\Relations\HasMany
     */
    public function books()
    {
        return $this->belongsToMany('App\Book')->withTimestamps();
    }

    /**
     * Get the User associated with the Author
     * @return \Illuminate\Database\Eloquent\Relations\BelongsTo
     */
    public function user() {
        return $this->belongsTo('App\User');
    }
}

class User .....

{

    /**
     * A user can have many books
     * @return \Illuminate\Database\Eloquent\Relations\HasMany
     */
    public function books()
    {
        return $this->hasMany('App\Book');
    }

    /**
     * A user can have many authors - but we fetch them through the
     * associated books of an user
     * @return \Illuminate\Database\Eloquent\Relations\HasManyThrough
     */
    public function authors()
    {
        return $this->hasMany('App\Author');
    }
}

class Book extends Model
{
    /**
     * A book belongs to exactly one user
     * @return \Illuminate\Database\Eloquent\Relations\BelongsTo
     */
    public function user(){
        return $this->belongsTo('App\User');
    }

    /**
     * Get th authors associated with this book
     * @return \Illuminate\Database\Eloquent\Relations\HasMany
     */
    public function authors()
    {
        return $this->belongsToMany('App\Author')->withTimestamps();
    }
}

In this way, I can easily get all authors which belongs to a user:

$authors = Auth::user()->authors()->lists('name', 'id');

(obviously using Guard to detect whether the item a user is working on belongs to him)

Thanks again for all your input :-)

Andreas

Hi @andreasb ,

Looks great! Just a quick point about you saying it is easy to get the Author - that wasn't quite what I meant. My point was that if you end up with duplicate Authors that are really the same person, when you call $author->books()->lists('title') it only finds the books attached to this particular Author, but not others that have the same name. If it works for you though it doesn't matter :P

Good luck with the project!