Databases
StackOverflow2013
Postsdbo
POHow to check against all joins when generating a score using MYSQL
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to check against all joins when generating a score using MYSQL
    primarykey
    data
    text
    <p>I'm using MYSQL to generate a score for each result returned by a query. The results are then ordered by the score. </p> <p>The part that doesn't seem to be working properly is when I'm trying to add a score for each tag that has been searched and the result is assigned to. So lets say I do a search for the tags "example", "test and "tag" and one of my results is assigned to the tags "example", "test", "someothertag" it should come up with a score of 10 since there are 2 matches.</p> <p>What is actually happening is I'm getting a score of 5 if there is a match, regardless of how many tags are matched. and 0 if no tags are matched.</p> <p>Here is an example of one of the queries that is generated from a search.</p> <pre><code> SELECT DISTINCT results.*, ( 5*(MATCH(tags.name) AGAINST('"self employed"' IN BOOLEAN MODE)) + 5*(MATCH(tags.name) AGAINST('"rental income"' IN BOOLEAN MODE)) + 5*(MATCH(tags.name) AGAINST('"commission income"' IN BOOLEAN MODE)) + 5*(MATCH(tags.name) AGAINST('"bankruptcy"' IN BOOLEAN MODE)) + 5*(MATCH(tags.name) AGAINST('"condo approval"' IN BOOLEAN MODE)) + 1*usefulness + 10*shares ) AS score FROM results INNER JOIN categories c on results.ID = c.RESULT_ID INNER JOIN tags ON results.id = tags.result_id WHERE c.name in ('purchase', 'condo', 'va') AND ( tags.name = 'self employed' OR tags.name = 'rental income' OR tags.name = 'commission income' OR tags.name = 'bankruptcy' OR tags.name = 'condo approval' ) AND ( results.scope = 'all' OR results.scope = 'hi' ) AND published = 1 GROUP BY results.ID having count(distinct c.c_id) = 3 ORDER BY score DESC LIMIT 8 OFFSET 0 </code></pre>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USFlimzy
    1. USMartin Hunt
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POHow to check against all joins when generating a score using MYSQL
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POHow to check against all joins when generating a score using MYSQL
      1. USMartin Hunt
      1. VTBountyStart
    3. VO
      singulars
      1. POHow to check against all joins when generating a score using MYSQL
      1. This table or related slice is empty.
      1. VTUpMod
    1. COYou probably don't need to use a fulltext index to check the tag names in this query. It'd be faster and more concise to just do a direct equality comparison.
      singulars
      1. POHow to check against all joins when generating a score using MYSQL
      1. USSam Dufel
    2. COIt will probably be more clear to people if you can write down the schema and some sample data for your tables, and a listing of what you would like the query to generate for that sample data.
      singulars
      1. POHow to check against all joins when generating a score using MYSQL
      1. USMikeb
    3. COGroupin might result in loosing matches. Have you tried to add COUNT to your five 5*(MATCH(tags.name)... items ?
      singulars
      1. POHow to check against all joins when generating a score using MYSQL
      1. USnicolas
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload