Creating a feed of underlying tables?

I have 3 tables, book, film and music. I want to present this data individually, as in a list of books and as a feed of all three types.

I cannot decide which route to take to implement the feed...

a) Create a MySQL view, based on a union of the three tables above. I'd then read this and it would automatically update when the underlying tables update. The view would have some null columns as the 3 underlying tables do not have completely the same set of fields.

b) Create a feed table with a one to one polymorphic relationship to either book, film and music. When I add a row to one of the underlying tables, I must create the reference on the parent feed table.

It's also worth noting that with either option when loading, I would still need to access the underlying book, film or music table as I would want to get all feed items (book, film or music) and eager load additional relationships defined on the book, film or music tables, for example, comments.

Any advice is appreciated.

sr57

5 years ago

Level 39

a) simpler

For your information, with postgresql you have table inheritance https://www.postgresql.org/docs/9.1/tutorial-inheritance.html

panthro

5 years ago

Level 4

Thank you @sr57, does any one else have an opinion?

Tray2

5 years ago

Level 73

I'd go with the database view. I use that technique in several places in a similar app I'm building. Another good thing about it is that you can choose which columns to retrieve and you can name them as you like.

The view is for display only and not for updating any data, that is done via the base tables.

I use this code in one of my migrations

DB::statement(
                "CREATE OR REPLACE VIEW book_views AS
                 SELECT
                    (SELECT GROUP_CONCAT(a.id ORDER BY a.id SEPARATOR ',')
                     FROM authors a, author_books ab
                     WHERE a.id = ab.author_id
                     AND ab.book_id = b.id) author_id,
                    (SELECT GROUP_CONCAT(concat(a.last_name, ', ', a.first_name)
                     ORDER BY a.last_name, a.first_name SEPARATOR ' & ')
                     FROM authors a, author_books ab
                     WHERE ab.author_id = a.id
                     AND ab.book_id = b.id) author_name,
                     b.id book_id,
                     b.title,
                    (SELECT ROUND(AVG(s.score), 1)
                    FROM scores s
                    WHERE s.item_id = b.id
                    AND media_type_id = 1) rating,
                    b.series,
                    b.part,
                    b.released,
                    g.id genre_id,
                    g.genre,
                    f.id format_id,
                    f.format,
                    CASE series
                        WHEN 'Standalone'
                        THEN b.released
                        ELSE (SELECT MIN(bi.released)
                              FROM books bi
                              WHERE bi.series = b.series)
                        END series_started
                FROM books b,
                     genres g,
                     formats f
                WHERE b.genre_id = g.id
                AND   b.format_id = f.id"
            );

That way I can make a much simpler query in my controller.

public function index()
    {
        $books = BookView::orderBy('author_name')
                         ->orderBy('series_started')
                         ->orderBy('part')
                         ->orderBy('released')
                         ->orderBy('title')
                         ->get();
        return view('books.index')->with('books', $books);
    }

Please or to participate in this conversation.