Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

thebookcollector's avatar

Validation rule - does book title exist with exact same illustrators

So my book database used to only allow one author and one illustrator per book. Sometimes book titles are the same, but as long as at least the author or illustrator were different, it was okay for the book to be saved to the database.

I had a books table, an authors table, and an illustrators table. Books held an author_id and an illustrator_id and authors and illustrators housed the names and ids of the authors and illustrators. Pretty basic stuff. I had a pretty simple validation rule to check for this when a user entered a new book:

'title' => ['required', Rule::unique('books')->where(function ($query) use ($data) {
                return $query->where('title', $data->title)
                    ->where('author_id', $data->author_id)
                    ->where('illustrator_id', $data->illustrator_id);
            })],

That worked great. If a user tried to enter a new book where the title AND author AND illustrator were the same, they'd get an error letting them know that book already exists.

But now I've updated the database schema to allow for multiple authors and illustrators for a given book. This means I've removed the author_id and illustrator_id fields from the books table and added authors_to_books and illustrators_to_books tables. Where, authors_to_books for example, has book_id and author_id as fields. Similar to how tags may look and behave in a DB schema. All relationships have been set up in the models.

My question is - how do I update that validation rule above to now check that a book isn't being entered with the same title AND author(s) AND illustrator(s) as another book that exists? Seemed pretty straight forward when dealing with just one relationship (two tables each), but now there are 2 relationships and 3 tables each so I'm having trouble wrapping my head around it.

0 likes
8 replies
kevinbui's avatar

We can start checking relationship existence, I assume this is your Book model now:

class Book extends Model
{
    public function authors()
    {
        return $this->belongsToMany(Author::class, 'authors_to_books');
    }

    public function illustrators()
    {
        return $this->belongsToMany(Illustrator::class, 'illustrators_to_books');
    }
}

Then the validation rule can be:

[
'title' => ['required', Rule::unique('books')->where(function ($query) use ($data) {
    return $query->where('title', $data->title)
        ->whereHas('authors', fn ($query) => $query->whereKey($data->author_id))
        ->whereHas('illustrators', fn ($query) => $query->whereKey($data->illustrator_id));
    })],
]
1 like
thebookcollector's avatar

@kevinbui I think I understand what is being suggested but I must be missing something because I'm getting:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'has' in 'where clause'

Expanded query:

SELECT
  count(*) AS aggregate
FROM
  `books`
WHERE
  `title` = testbook
  AND (
    `title` = testbook
    AND `has` = authors
    AND `has` = illustrators
  )
kevinbui's avatar

@thebookcollector I think I know what is going on here. This is just a database query via the books table, not the Book model. So it doesn't recognise the authors and illustrators relationships. I will put a different answer.

Tray2's avatar

Why don't you use the ISBN as the unique identifier for the books?

thebookcollector's avatar

@Tray2 Valid question. A lot of books in collections don't have an ISBN because they are older or part of very small print runs.

kevinbui's avatar

Seemingly, the unique rule is not adequate to solve your problem.

Whenever validation involves multiple fields like this, I always reach out to the after validation hook.

I assume your are using a form request object. You might want to do something like this:

class YourRequest extends FormRequest
{
    public function prepareForValidation()
    {
        $this->getValidatorInstance()->after(function ($validator) {
            $existing = Book::where('title', $this->input('title'))
                ->whereHas('authors', fn ($query) => $query->whereKey($this->input('author_id')))
                ->whereHas('illustrators', fn ($query) => $query->whereKey($this->input('illustrator_id')))
                ->exists();

            if ($existing) {
                $validator->errors()->add( 'title', 'Another book with the same title, author and illustrator already existed!');
                
            }
        });
    }
}

btw, I have solved a different thread using the same trick, slightly different. You might want to have a look.

1 like
thebookcollector's avatar

@kevinbui This is very close! This prevents the user from entering a new book if the title is the same as another and it has *any* author and *any* illustrator that is the same.

I'm trying to only prevent when it's a full match of title and *all* authors and *all* illustrators.

In other words, we have a book that already exists in the DB:

title = book1
author(s) = john, mary
illustrator(s) = peter

User trying to enter new book:

title = book1
author(s) = john, mary
illustrator(s) = peter
Result: This fails correctly.

User trying to enter new book:

title = book1
author(s) = john, mary, joseph
illustrator(s) = peter
Result: This currently fails but should succeed because the authors are different (2 of the same, but an additional author)

User trying to enter a new book:

title = book1
author(s) = john
illustrator(s) = peter
Result: This currently fails but should succeed because the authors are different (1 same author but the original had 2)

Anyway, all this to say you've helped me get on the right track. If you happen to see this and think "oh, that's an easy fix" then I'm definitely happy to hear about it, but I'm not looking for someone to do everything for me. I'm researching and trying out some different things.

Thank you!

Please or to participate in this conversation.