如何简化此查询？

Question

If you look at the following query:

$query = $this->db
        ->select('*')
        ->from('sets')
        ->where('type', 'd')
        ->or_where('type', 'e')
        ->or_where('type', 'f')
        ->or_where('type', 'g')
        ->or_where('type', 'h')
        ->or_where('type', 'i')
        ->or_where('type', 'j')
        ->or_where('type', 'k')
        ->or_where('type', 'l')
        ->or_where('type', 'm')

                    ...

        ->or_where('type', 'x')
        ->or_where('type', 'y')
        ->or_where('type', 'z')
        ->order_by('date', 'asc');

    return $query->get()->result();

In the 'type' column I have 'a' through 'z' bascially I would like all rows minus the ones with a, b, c. How can I write this in a more elegant way?

Answer 1

Since you want "basically all rows except those with a, b or c", you can make use of the where_not_in directive:

$query = $this->db
    ->select('*')
    ->from('sets')
    ->where_not_in('type', array('a','b','c'))
    ->order_by('date', 'asc');

See for example this answer (beware of the arrays!)

Update

Just to add spice, the question whether to use where_in or where_not_in is not actually so simple ("just use the one which will generate the shortest/simplest SQL"). If you have a large table and the cardinality of the sets you're going to look for is large, and you have an index on the field, then (depending on many factors) doing a negative search might be far less efficient than an (apparently) larger positive search.

Indexes are good at looking for what's in them, not for what's not in them. So you might actually find it beneficial to do:

->where_in('type', array_diff(  // Where type is...
    range('a', 'z'),            // ...everything...
    array('a','b','c')          // EXCEPT a, b or c
))

// The above solution works also if you look for non-consecutive letters.

MySQL might then do twenty very fast index lookups (d, e, f, ...), instead of only three compares on a possibly slow full table scan. Depending on how many items you're looking for, the indexing, disk performances, table size and (did I leave out the weather?) the weather, this second method might be faster even if the SQL is more complicated.

It's surely something to look at when you'll be in the query optimizing stage. Don't just go and index every column as you'll see suggested in some online so-called tutorials -- indexing can sometimes be harmful to performances, and overindexing by definition always is.