Immediately to the point. There is a task to withdraw from the database on the page for example: a list of books and the author (name of author) of a particular book should be next to it. Just bring the author and next should be a list of books that he wrote. There are two tables in the database: Books

Writers

I assume that for this it is necessary to link tables (one to many). What I tried to do by example. But it does not occur to me how to make a request correctly and display it on the page exactly as I described above or link the tables correctly, please tell me. A simple request for a sample of data is obtained to form a 'SELECT * From books' which displays all the data from the database, but how to make the output of the books correctly and that the corresponding author should be displayed here I just blunted (. Thanks in advance for your understanding.

  • one
    I do not understand anything. Try to reformulate the question. This is the first. Second, do you need a raw SQL query, or do you use any modern tools? - Captain Flint
  • Well, literally the task sounds like this. It is necessary to display the author in the list of books next to her title. In the list of writers, each of them should display a list of books written by him. In fact, yes, you need an ordinary sql query that will select data by the specified criteria. - Arthur Mikhailov
  • 2
    Simply use the LEFT JOIN query in the SELECT query to add content from other tables to the query according to the necessary conditions. They can be explicit or taken from the fields of the main query. - Rootware
  • Your answer helped solve the problem, thank you) - Arthur Mikhailov

1 answer 1

1. A request that displays the author next to the book

SELECT b.*, w.name FROM books b LEFT JOIN writers w ON w.id = b.writer_id

Immediately I’ll note that the request intentionally indicated writer_id instead of the id_writer column you id_writer . The fact is that there are certain patterns of the names of columns , tables and relationships that should be followed for a supported and scalable project. For example, table names are plural, columns that refer to external tables are named after the {foreign table name}_{foreign table column} and so on. I advise you to read more about these patterns, then it will be easier to design the database.

2. A request that will bring the authors along with the books.

The fact is that because of the relationality of the database, we cannot receive collections, we can only receive strings. In this situation, the receipt of all the books of authors, we must break into two stages:

  1. Getting authors
  2. Getting books for each author

In this situation, the greedy load comes in handy , which saves us from the N + 1 anti-pattern . In many frameworks and ORM it goes out of the box:

 //пример из ORM Eloquent, входящей в Laravel: $author = \App\Author::with('books')->get();

This code in Laravel will make 2 queries in the database: 1. SELECT * FROM authors 2. SELECT * FROM books WHERE author_id IN (*ID авторов выше*) And then combine it into a single collection.