Myujth

I’m developing a web app with a search functionality. Generally speaking, the user can search under specific categories or groups of categories. Example:

Mammal (group)
 Cat (category)
 Dog (category)

Mammal, Cat, and Dog are tables in the DB, and are represented by their own class in the source code. Common fields between Cat and Dog are stored in Mammal; both Cat and Dog have a set of unique fields. I’m trying to figure out the best way to execute a query (or queries) when a user searches under a group (rather than a specific category). For example, the user searches for “all mammals under the age of 4”. As part of the response, I want to return all the fields in the tables belonging to the Mammal category (Cat and Dog, in this case).

Given that the tables Cat and Dog have unique fields, it seems (according to my googling) that I would need to run multiple queries (one for each category). Is this, indeed, the case? If so, what is most efficient way of doing this? And if not, how would I run such a request with a single query?

Essentially, my question is this: What is the most efficient way of executing a query for the situation I’ve described above?

[EDIT]
DB example w/ queries:
https://www.db-fiddle.com/f/na9ctPmi6CjyDB4MNnjycb/3

In the example in the link above, there are two queries which, together, can get all the data for the user's search (described above). I'm wondering if there's a way to do this with a single query, or at least a single call to the DB.

So far, I've tried the naive approach with the multiple query calls. This works fine (insofar as there aren't any errors). My concern is that when the stored data accumulates to the several hundreds/thousands, this approach will become too slow. Furthermore, my current approach requires additional data processing in the source code. For example, if the user wants the top 5 results from the search, then I have to get the top 5 results from each table, create an aggregated collection of the DB results, sort the collection, and pick the top 5 results from the sorted collection to return to the user. I'm wondering if there is a way to accomplish all this on the DB side (assuming that it would be faster).

From your sqlfiddle:

select * from cat left join mammal on (cat.id = mammal.id) where age < 4
union all 
select * from dog left join mammal on (dog.id = mammal.id) where age < 4

A few points:

• You only want left join if, for example, you'd want to show lines for mammals that don't have corresponding dogs or cats. In your case, it doesn't seem like that's what you want, so it would be better to use an inner join (or simply join, which defaults to an inner one). That way, if there are no dogs, and only 2 cats, the results would only show 2 lines, instead of 2 cats + 1 non-existing dog.



• 
  

  Making one table per mammal type won't scale. What happens if your users now want to add coyotes? Or rabbits? Every single time a new mammal needs to be added to the system, you need to create a new table. The proper way to do this would be to create a join table that would detail:

  
  
  
  • 
    

    A table animal_type, with lines something like this:

    
    
    

    animal_type_id name

    
    
  
  
  • 
    

    Then you'd have a separate table called attribute_type with something like this:

    
    
    

    attribute_id name

    
    
  
  
  • 
    

    A separate table called animals:

    
    
    

    animal_id animal_type_id

    
    
  
  
  • 
    

    And then finally you'd have a separate table called animal_attributes:

    
    
    

    id animal_id attribute_id value

    
    
  
  

Now, you can simply add animals by inserting a row in animals, specifying the animal_type_id, which could be cat/dog/whatever. You simply need to have created the animal_type beforehand. And then, you add attributes to your animal by creating rows in animal_attributes, referencing the animal_id you just created, as well as the proper animal_attribute_id, which could be shared attributes like color, length, size, and unique cat/dog/animal fields.