I would like to search files by name and after the tag.
If the tag assigned to the file matches the word you are looking for, this file is also counted.
I have three tables :
tags
id | name
tags_files
id | id_tag | id_file
files
id | name | category
Now i only search by name in "files" table.
$how_many = $this->wpdb->get_var($this->wpdb->prepare("SELECT COUNT(*) FROM {$this->table_files} WHERE name LIKE %s AND category IN ($subcategories)", '%' . $this->wpdb->esc_like($search) . '%'));
Every file has assigned tags in "tags_files" table.
How solve this?
=========================
If want you can add search in category like this:
WHERE (tags.name LIKE '%search%' OR files.name LIK '%search%' ) AND ($this->files.category IN ($subcategories))
SELECT files.* FROM files
LEFT JOIN tags_files ON tags_files.id_file = files.id
LEFT JOIN tags ON tags.id = tags_files.id
WHERE tags.name LIKE '%search%' OR files.name LIK '%search%'