Choice mysql by number of tags



  • There is a news base (id, name, text, date, tags). The paces go in a tiny text just like a line #tag1 #tag2 #tag3 #tag4.

    There's news 1 with the tag1 #tag2 #tag3 #tag4
    There's news 2 with the tag1
    There's news 3 with tag2 #tag3 #tag4
    There's news 4 with gigs: #tag3 #tag4

    The currents are now: select * from news where tags like '%#".$Tag." %' order by date DESC
    How do I get to news 1, other news is fine. News 3 - News 4 - New 1i.e., just by number of matches, one request without changing the OBD structure?



  • SQL can count only if it is presented in separate lines. It is therefore very desirable that the data be included in the report. https://ru.wikipedia.org/wiki/%D0%9D%D0%BE%D1%80%D0%BC%D0%B0%D0%BB%D1%8C%D0%BD%D0%B0%D1%8F_%D1%84%D0%BE%D1%80%D0%BC%D0%B0 ♪ If the transmission of the code under the right OBD structure is not possible, then we will have to turn the line into separate lines in the request and then count the number of coincidences. To this end, we will need a service table with serial numbers from 0 to as many as possible in the line:

    create table seqnum(X int not null);
    -- Первые 8 записей
    insert into seqnum values(0),(1),(2),(3),(4),(5),(6),(7);
    -- И еще 512
    insert into seqnum
    select s1.x*64+s2.x*8+s3.x+8
      from seqnum s1, seqnum s2, seqnum s3;
    

    The request for all the news with the same tags as the ID=1 news, the matching grading is:

    select N.*,count(1) cnt
      from news N,
           (select ltrim(substr(tags,length(SUBSTRING_INDEX(tags,' ',s.x))+1,
                   length(SUBSTRING_INDEX(tags,' ',s.x+1))-
                   length(SUBSTRING_INDEX(tags,' ',s.x)))) tag
              from news n, seqnum s
             where n.id=1
               and length(SUBSTRING_INDEX(tags,' ',s.x))<length(tags)
           ) T
     where concat(N.tags,' ') like concat('%',T.tag,' %')
       and N.id!=1
    group by N.id
    order by cnt desc
    

    Request T Wires all the stories ID=1 separate lines, external searches for matches and counts. The cuts from the line are based on the assumption that the currents are strictly divided by one gap.


Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2