Unique links with url and tags check

[sergiorgiraldo]

In the LinkStoreRequest (Http/Requests/Models/LinkStoreRequest.php) I want to change the unique validation

 Rule::unique('links')->where(function ($query) {
                    return $query->where('user_id', auth()->user()->id);
                }

to indicate a link is unique by the url and tags. Thus

youtube.com, tag 20230527

is differente from

youtube.com, tag 20230528

I am new to PHP, any hints how to accomplish this?

[sergiorgiraldo]

I came with this but still not working ...

 $currentDate = date('Ymd');
        return [
            'url' => [
                'required',
                'string',
                Rule::unique('links')->where(function ($query) use ($currentDate) {
                    return 
                        $query->where('user_id', auth()->user()->id)
                        ->doesntExist(function ($subQuery) use ($currentDate) {
                            return $subQuery->select(1)
                                ->from('links AS a')
                                ->join('link_tags AS b', 'b.link_id', '=', 'a.id')
                                ->join('tags AS c', 'b.tag_id', '=', 'c.id')
                                ->where('a.url', $url)
                                ->where('c.name', $currentDate);
                        });
                      }),
            ],
            'title' => 'nullable|string',
            'description' => 'nullable|string',
            'lists' => 'nullable',
            'tags' => 'nullable',
            'is_private' => 'sometimes|boolean',
            'check_disabled' => 'sometimes|boolean',
        ];

[sergiorgiraldo]

I couldnt do with clean php but i solved.
(1) In LinkStoreRequest.php

$currentDate = date('Ymd');

...

Rule::unique('LinksAndTags')->where(function ($query) use ($currentDate) {
        return $query->where('user_id', auth()->user()->id)
                    ->where('name', $currentDate);
}),

(2) In the database I created a view:

create view LinksAndTags AS
SELECT A.url,A.user_id, C.name
from links A 
inner join link_tags B on A.id = B.link_id
inner join tags C on B.tag_id = C.id