Custom Key Name with field from different table

Published 1 year ago by Tipoff

I have two database tables that I want to use in my model with the main one being a subset of authors, those with published books. The model should only include authors that are in this table but I want to use a custom Key Name for the model of the slug that is associated with the author in the larger authors table. I can't find anything on how to accomplish this and would appreciate your help. Here's what I have so far in App/BookAuthor:

class BookAuthor extends Model
{
    public $table = "book_authors";
    public $table = "authors";

    /**
     * Get the route key for the model.
     *
     * @return string
     */
    public function getRouteKeyName()
    {
        return 'slug'; //This field is only in the authors table
    }
tekmi
tekmi
1 year ago (110,680 XP)

I'm not sure if you can have multiple tables attached to the same Eloquent model class. I think in ORM that implements ActiveRecord pattern, it should be one table per one model class.

Maybe you could create two Eloquent models like Author -> authors, BookAuthor -> book_authors and try to distinguish them in your controllers?

Or maybe you actually don't need two separates tables... If you had just one table authors with column type, you could easily distinguish the author types in the where clause

Tipoff
Tipoff
1 year ago (26,000 XP)

Thank you, Tekmi. I thought that might be the case since I couldn't find anything on this. If I used the authors table and used a where clause to limit it to only authors that had a matching id in the book_authors table, what would be the best way to approach that?

Borisu
Borisu
1 year ago (104,110 XP)

You might try to do a relationship on the same model to a pivot table.

public function book_author()
{
    return $this->belongsToMany(Author::class, 'book_authors')->withPivot('extra_column_of_data_1', '2nd_column');
}

This way you have one model and basically two tables with data... Play around with it.

tekmi
tekmi
1 year ago (110,680 XP)

Somehow I still feel like the book_authors is actually the smaller subset of authors. If so, it's hard (at least for me) to map it two different entities.

I try to see it from the perspective of relations between two entities, which usually boils down to three choices:

  • One to One
  • One to Many
  • Many to Many

If you wanted to keep authors and book_authors in separate tables, treating them as separate entities, how would you envision the relations between them?

  • (One) author can act as (One) book author?
  • (One) author can appear (Many) times as a book author?
  • (One) author can appear (Many) times as a book author and (One) book author can appear (Many) times as an author?

If I didn't talk to you and just see the table book_authors, I would probably assume that you have somewhere the Books entity and Authors entity.

And that the relation between them is as follows: (One) Book can be written by (Many) Authors, and (One) Author can write (Many) Books.

Tipoff
Tipoff
1 year ago (26,000 XP)

Thank you both for the insight. I agree that it would be better to use book_authors as a pivot table between authors and books and also another table for contributions. That table would essentially key if the author was a solo author for the book, a co-author, editor, contributor, etc.

The authors table however will have more than just authors who wrote books. It will also include authors of articles and other content. I have an App/Author model that displays them in another section, but I would like to display just the Authors who have written books in this books section of the website with a different view. It seems the best way to accomplish this would be to have this model also use the authors table and just restrict the authors using a where clause for those included in the pivot table. I think Jeffrey has some videos explaining the best way to do this.

tekmi
tekmi
1 year ago (110,680 XP)

Wasn't it this episode of Jeffrey's Forum series: https://laracasts.com/series/lets-build-a-forum-with-laravel/episodes/18 that you are referring to?

Maybe in your case using the polymorphic relations could be an interesting idea.

This pivot table could have a column which accepts either the App\Contribution or App\Book type.

Anyway I would be interested in seeing your ERD once you pick up your solution. (If it's not a problem of course)

Anyway, Good luck @DrewRoberts

Borisu
Borisu
1 year ago (104,110 XP)

Ok so why don't you just use a column in your authors pivot table to say what they wrote:

| author_id | wrote_id | wrote_type |

This way you can expand to a polymorphic relation and have many different models like Article, Book etc. When querying those models you'll always get the author and vice versa. I think this is maybe the best way for you. Alternatively you can just add a column "wrote" to your authors table and filter that.

$bookAuthors = Author::where('wrote', 'book')->get();

// or even

public function scopeWroteBook($query)
{
    return $query->where('wrote', 'book');
}

$bookAuthor = Author::wroteBook()->get();

Just go with the simple solution, look how it feels and then decide if you want a complex pivot table etc.

Please sign in or create an account to participate in this conversation.