title: SQL - Query to return the most relevant results from a search
body: <p>I developed a website with a search box and, when someone searches for any word ("car", for example) I run a query on my database that checks if the searched word is contained in the keywords table. I mean, if is there "car" in the keywords table I'll return on the website the content related with that keyword.</p>
  
  <p>The database has 2 tables used for the search: The contents table (id_content, name, description, alias, ...) and the Keywords table (id_keyword, keyword, id_content), which means that every content have at N keywords, 1 name, 1 description and 1 alias.</p>
  
  <p>But now I want to redefine the search a little better by checking if the search matches not only the keyword but also the name, alias and description. The only problem is that I'm not sure how to do it, since some content will have only a matching keyword, other will have matching name and keyword (just an example) and some will actually have a matching name, description, alias and keyword.</p>
  
  <p>My objective is for my search to retrieve first the full match ones (name, description, alias and keyword), then the 3 match ones, then the 2 match and, finally, the ones that have only 1 match (despite the match being the name, description, alias or keyword).</p>
  
  <p>I'm not sure if I'm being clear enough, feel free to ask for details.</p>
  
  <p>Thank you.</p>
  
  <hr>
  
  <p>EDIT 1</p>
  
  <p>Function to get the objects by keyword:</p>
  
  <pre><code>public function get_object_id_by_keyword($keyword, $limit = 3){
      $id = "";
      $sql  = "SELECT id_object FROM addons_keywords WHERE keyword = '".$keyword."' limit " . $limit;
      $res  = $this-&gt;db-&gt;query($sql);
      if($res-&gt;num_rows() == 1){
          $id = $res-&gt;row(0)-&gt;id_object;
      }
      else{
          $id = array();
          for($i=0; $i&lt;$res-&gt;num_rows(); $i++){
              array_push($id, $res-&gt;row($i)-&gt;id_object);
          }
      }
  
      return $id;
  }
  </code></pre>
  
  <p>SQL Code go get a "full match":</p>
  
  <pre><code>SELECT id_object
  FROM addons_keywords 
  WHERE keyword = 'searched'
  
  UNION 
  
  SELECT id_object
  FROM objects
  WHERE name LIKE '%searched%'
  AND description LIKE '%searched%'
  AND alias LIKE '%searched%'
  </code></pre>
  
  <p>From here, what I can do is to query the full match and, in case there is not enough content (content = object and $limit is the number of results I need), I'll do the query next to the "full match", that would be a "3 match". My problem is that a 3 match can either be a name+description+alias, a name+alias+keyword, description+alias+keyword, etc...</p>
  
  <hr>
  
  <p>EDIT 2</p>
  
  <p>SQL code to a "1 match" (not sure if the union is correct):</p>
  
  <pre><code>SELECT id_object 
  FROM addons_keywords 
  WHERE keyword = 'searched'
  
  UNION
  
  SELECT id_object
  FROM objects
  WHERE name LIKE '%searched%'
  OR description LIKE '%searched%'
  OR alias LIKE '%searched%'
  </code></pre>
  
  <hr>
  
  <p>EDIT 3</p>
  
  <p>Just realized that the "full match" SQL is incorrect, since it will return me the id_object of an object without the searched description (just tested it). Maybe UNION isn't the way to go... Or is it?</p>