how to find distinct results that are ordered by the number of instances found in a MySQL search?

Question

I need to find distinct videos that are not viewed or uploaded by user logged in and are ordered by the number of instances found in a MySQL search

And it finds the needed videos as it should but it finds videos that contain more than 1 of the searched tags the number of tags searched for times and all with the same score

So if it searches for tag1 and tag2 and 1 video contains tag1 - and another video contains tag1 and tag2

The result is:
Video1 - single score
Video2 - single score
Video2 - single score
All 3 with the same score

And what I need is for it to find only distinct videos but which score reflects how many tags of the searched ones it contains

The needed result: (in correct order)
Video2 - double score (contains 2 tags)
Video1 - single score (contains 1 tag)

If you want to test you can click the link below and change
the 2 instances of userID = ... (1,2,3 to choose from)
and
the 2 instances of tags MATCH (vtn.tag) AGAINST ('...' IN BOOLEAN MODE) (politics,news,social,other to chose from)
(just remember to make the 2 instances of userID the same and the 2 instances of tags the same)

To test

To that end I have the following SELECT statement

SELECT v.id AS vID , v.date AS vDate , v.seriestitle AS vSeriestitle , v.seriesnumber AS vSeriesnumber , v.title AS vTitle , v.randomString AS vRandomString , u.username AS uUsername , u.randomString AS uRandomString , ( SELECT COUNT(*) FROM videoview AS vv WHERE vv.videoID = v.id ) AS videoviews , MATCH ( vtn.tag ) AGAINST ( '$tags' IN BOOLEAN MODE ) AS score FROM video AS v LEFT JOIN userlogininfo AS u ON v.userID = u.id LEFT JOIN videotag AS vt ON vt.videoID = v.id LEFT JOIN videotagname AS vtn ON vtn.id = vt.tagID WHERE MATCH ( vtn.tag ) AGAINST ( '$tags' IN BOOLEAN MODE ) AND v.userID != ? AND NOT EXISTS ( SELECT NULL FROM videoview AS vvn WHERE vvn.userID = ? AND vvn.videoID = v.id ) ORDER BY score DESC LIMIT ?

Or more readable:

SELECT 
    v.id AS vID , 
    v.date AS vDate , 
    v.seriestitle AS vSeriestitle , 
    v.seriesnumber AS vSeriesnumber , 
    v.title AS vTitle , 
    v.randomString AS vRandomString , 
    u.username AS uUsername , 
    u.randomString AS uRandomString , 
    ( SELECT 
        COUNT(*) 
    FROM videoview AS vv 
    WHERE vv.videoID = v.id ) AS videoviews , 
    MATCH ( vtn.tag ) AGAINST ( '$tags' IN BOOLEAN MODE ) AS score 
FROM video AS v 

LEFT JOIN userlogininfo AS u 
ON v.userID = u.id 

LEFT JOIN videotag AS vt 
ON vt.videoID = v.id 

LEFT JOIN videotagname AS vtn 
ON vtn.id = vt.tagID 

WHERE 
    MATCH ( vtn.tag ) AGAINST ( '$tags' IN BOOLEAN MODE ) 
    AND v.userID != ? 
    AND NOT EXISTS 
        ( SELECT NULL FROM videoview AS vvn WHERE vvn.userID = ? 
        AND vvn.videoID = v.id ) 
ORDER BY score DESC 
LIMIT ?

Harshil Doshi · Accepted Answer · 2017-11-19T00:55:22.110

1

You first need to group all the tag for a particular video title/id.

For this, you need group by v.title and group_concat(vtn.tag).

Group_concat(vtn.tag) will concat all the tags for a particular video title.

Unfortunately, we can't use group_concat(vtn.tag) inside Match().

Now, we can join a subquery that will select videoID and all relevant tags to that id inside select query. But the problem is, in order to make MATCH()..AGAINST() work properly, we need to have FULLINDEX on the column of the table.

Hence, the only solution I can see here is to create another table that contains videoID and all their tag in a single row.

SOLUTION:

CREATE TABLE `mergevideotag` (
  `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `videoID` int(11) NOT NULL,
  `tag` text NOT NULL,
   FULLTEXT(`tag`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Insert into mergevideotag(videoID,tag)
select vt1.videoID as id,
        Group_concat(distinct vtn1.tag order by vtn1.tag desc separator ' ') as tag
            from videotag vt1
            LEFT join videotagname vtn1
            on vt1.tagID = vtn1.id
            group by vt1.videoID
;

Final Select query:

  SELECT 
  v.id AS vID , 
  v.date AS vDate , 
  v.seriestitle AS vSeriestitle , 
  v.seriesnumber AS vSeriesnumber , 
  v.title AS vTitle , 
  v.randomString AS vRandomString , 
  u.username AS uUsername , 
  u.randomString AS uRandomString , 
  ( SELECT COUNT(*) FROM videoview AS vv WHERE vv.videoID = v.id ) AS videoviews , 
   m.tag as tag, 
  MATCH (m.tag) AGAINST ('other social' IN BOOLEAN MODE) AS score 
FROM video AS v 
LEFT JOIN userlogininfo AS u ON v.userID = u.id 
LEFT JOIN videotag AS vt ON vt.videoID = v.id 
LEFT JOIN videotagname AS vtn ON vtn.id = vt.tagID 
LEFT JOIN mergevideotag AS m ON vt.videoID = m.videoID            
WHERE 
  MATCH ( vtn.tag ) AGAINST ( 'other social' IN BOOLEAN MODE )
  AND v.userID != 2 
  AND NOT EXISTS ( SELECT NULL FROM videoview AS vvn WHERE vvn.userID = 2 AND vvn.videoID = v.id ) 
  group by v.id, m.tag
  order by score desc
;

Click here for DEMO

Hope it helps!

edited Nov 19 '17 at 00:55

answered Nov 18 '17 at 21:37

Harshil Doshi

3,497
3
14
37

Please try the answer as well as check the demo. – Harshil Doshi Nov 18 '17 at 21:38
@Hashil It looks like it should work after CREATE TABLE and INSERT INTO but the INSERT INTO that follows throws a '#1064 - There is an ERROR in SQL syntax near 'DISTINCT vtn1.tag ORDER BY vtn1.tag DESC seperator ' ') AS tag FROM videotag AS ' on line 1' – APM Nov 18 '17 at 23:16
It works fine in the demo and I couldn't find anything wrong about the statement. It looks like a typo or mistake in copy paste maybe. Can you please try it again? – Harshil Doshi Nov 18 '17 at 23:25
Have you figured it out? – Harshil Doshi Nov 19 '17 at 00:21
Remove the last line from select query : `select *from mergevideotag` is a different query. – Harshil Doshi Nov 19 '17 at 00:24
Also remove `;` after `desc` – Harshil Doshi Nov 19 '17 at 00:26
did both but var_dump($getVideo); still gives same notice – APM Nov 19 '17 at 00:30
Please run select query on database directly (not from php) and comment here whether it's working properly or not. – Harshil Doshi Nov 19 '17 at 00:33
Also, according to my knowledge, query inside prepare() should be written in single quotes. '' not the double quotes "". – Harshil Doshi Nov 19 '17 at 00:39
It throws a '#1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'v.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by' – APM Nov 19 '17 at 00:40
Ok. You need to change the mode. Write the statement given in following link: https://stackoverflow.com/a/36033983/2225030 – Harshil Doshi Nov 19 '17 at 00:46
Apparantly it should be 'group by v.id , m.tag' and it works perfectly... edit your answer to match and you'll get your checkmark for answered :) and a million times thank you... much appreciated your hard work on this :) – APM Nov 19 '17 at 00:48
Is it really worked by adding only those 2 columns in group by? Or did u remove some columns from select too? – Harshil Doshi Nov 19 '17 at 00:51
yes... only change was removing the v.title and add 'v.id , m.tag' to the group by and it found the right videos with the right score and ordered them perfectly - I changed nothing else – APM Nov 19 '17 at 00:54
Thank you. You can remove the update from your question as it may confuse the future visitors. – Harshil Doshi Nov 19 '17 at 00:58
it is now removed :) – APM Nov 19 '17 at 01:14

score 0 · Answer 2 · answered Nov 18 '17 at 19:07

0

select distinct, that would only give distinct result, every other things seems alright in your query

SELECT DISTINCT

answered Nov 18 '17 at 19:07

shahin mahmud

945
4
11

yes it gets only distinct videos but it still sets the score as the same which means I still can't order them correctly... so how do I change the score to reflect how many of the searched tags every video contains? – APM Nov 18 '17 at 19:17
1

you have to concatenate the tags for every video, and then have to math for that concatenated tags string to get correct score – shahin mahmud Nov 18 '17 at 20:44

how to find distinct results that are ordered by the number of instances found in a MySQL search?

2 Answers2