http401's avatar

Many-to-many relationship with categories useful only for specific models

Hello,

I recently had the need to develop a system where the categories of everything that can be categorized would all need to be in a single table, for this, polymorphic relationships would be used.

The difficulty encountered was that although the same table stores all the categories, these categories would need to be independent for each model, for example post categories are independent and can only be associated with Posts (articles on a blog, tags), categories of Properties are independent and can only be associated with Properties (houses, apartments...). Furthermore, there may be subcategories, such as Property Image categories (bedroom, living room, kitchen...), which are categories that can only be associated with images of their respective model.

To solve this problem, I decided to use a many-to-many polymorphic relationship, with the categories and categorizables tables with the following structure so that it was possible to use an Adjacency List Model. I would like to know what your opinions are about this solution, whether you think this was the most appropriate or whether there would be a smarter solution to this problem.

"https://imgur.com/b5ijbY3

0 likes
6 replies
Tray2's avatar

I have a similar solution in one of my projects where I create a single genres table.

public function up(): void
    {
        Schema::create('genres', function (Blueprint $table) {
            $table->id();
            $table->string('name');
            $table->foreignIdFor(MediaType::class);
            $table->timestamps();
        });
    }

That I then use for books, movies, games, and records. The foreign id is the type of the media associated with the genre.

public function up(): void
    {
        Schema::create('media_types', function (Blueprint $table) {
            $table->id();
            $table->string('name');
            $table->timestamps();
        });
    }

I then use a database view for each of the above mentioned types, and it only returns the genres for the specific media type.

 public function up(): void
    {
        DB::statement("CREATE OR REPLACE VIEW record_genre_views AS
           SELECT g.name
           FROM genres g,
                media_types mt
           WHERE g.media_type_id = mt.id
           AND mt.name = 'record';
        ");
    }
1 like
jlrdw's avatar

I would suggest separate tables for properties with their own relations setup.

Tray2's avatar

@jlrdw I think it all depends on the amount of data, and in the case described I would probably go with my solution because then I can do the create, update, and delete in a single controller, and just use the model when I need to fetch the categories in the PostsController's create and edit actions.

    public function __invoke()
    {
        return view('books.create')
            ->with([
                'authors' => Author::query()
                            ->select('id', 'last_name', 'first_name')
                            ->orderBy('last_name')
                            ->orderBy('first_name')
                            ->get(),
                'formats' => BookFormatView::query()
                            ->orderBy('name')
                            ->get(),
                'genres' => BookGenreView::query()
                            ->orderBy('name')
                            ->get(),
                'series' => Series::query()
                            ->select('id', 'name')
                            ->orderBy('name')
                            ->get(),
                'publishers' => Publisher::query()
                            ->select('id', 'name')
                            ->orderBy('name')
                            ->get(),
            ]);
    }

Of course I would probably use a postview to fetch the post and categories at well.

  public function __invoke(Request $request)
    {
        return view('movies.index')
            ->with([
                'movies' => MovieIndexView::query()
                    ->when($request['search'], function ($query, $search) {
                        $query->where('title', 'LIKE',  "%$search%");
                    })
                    ->when($request['released'], function ($query, $released) {
                        $query->where('release_year', $released);
                    })
                    ->when($request['genre'], function($query, $genre) {
                        $query->where('genre', $genre);
                    })
                    ->when($request['format'], function($query, $format) {
                        $query->where('format', $format);
                    })
                    ->orderBy('title')
                    ->orderBy('release_year')
                    ->get(),
            ]);
    }
1 like
jlrdw's avatar

@Tray2 I mainly mentioned separate because houses, etc will probably turn into a business app that requires income, expense, tax considerations, etc. Really different from a blog.

Or are these just images of houses? Or rental properties requiring a business and bookkeeping app.

Merklin's avatar

If the categories can be considered static, and new ones will not be added frequently, or not at all, you can use Enums.

Please or to participate in this conversation.