By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();
| version() |
|---|
| 8.0.26 |
CREATE TABLE `tl_news4ward_article` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`title` varchar(255) NOT NULL DEFAULT '',
`keywords` text,
`description` text,
PRIMARY KEY (`id`)
) ENGINE=innodb AUTO_INCREMENT=53 DEFAULT CHARSET=utf8;
ALTER TABLE tl_news4ward_article
ADD FULLTEXT(keywords,title,description)
INSERT INTO tl_news4ward_article VALUES (1, 'something', 'something', 'something');
CREATE TABLE `tl_news4ward_tag` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`pid` int(10) unsigned NOT NULL DEFAULT '0',
`tstamp` int(10) unsigned NOT NULL DEFAULT '0',
`tag` varchar(255) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `pid` (`pid`)
) ENGINE=innodb AUTO_INCREMENT=103 DEFAULT CHARSET=utf8;
CREATE
OR REPLACE VIEW `tl_news4ward_articleWithTags` AS
SELECT
t.*,
GROUP_CONCAT(tag) AS tags
FROM
tl_news4ward_article t
LEFT OUTER JOIN tl_news4ward_tag ON (
tl_news4ward_tag.pid = t.id
)
GROUP BY
t.id;
SELECT * FROM tl_news4ward_articleWithTags t1 INNER JOIN ( SELECT MATCH (keywords,title,description) AGAINST (' something1 something2' IN BOOLEAN MODE) AS score ,id FROM tl_news4ward_article ) t2
ON t1.id = t2.id;
| id | title | keywords | description | tags | score | id |
|---|---|---|---|---|---|---|
| 1 | something | something | something | null | 0 | 1 |