Codeigniter query builder SELECT query WHERE column value in in a range of letters

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?

Not sure whether CI's DBAL supports it but in mysql there is BETWEEN. Otherwise you could create it yourself using IN and PHP's range() function result. — PeeHaa
– PeeHaa, Commented Oct 13, 2013 at 20:41

Community · Accepted Answer · 2017-05-23 11:50:13Z

3

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.

edited May 23, 2017 at 11:50

CommunityBot

11 silver badge

answered Oct 13, 2013 at 20:41

LSerni

57.7k10 gold badges70 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user2869880 Over a year ago

it would be where_in instead

AlexP Over a year ago

+1 You could use SELECT * FROM foo WHERE NOT EXISTS(SELECT 1 FROM foo WHERE type IN (a,b,c)) Which would invert the negative search into a positive one.

mickmackusa Aug 23 at 15:23

See also: Construct a CodeIgniter WHERE condition with the value BETWEEN two column values

Lajos Veres · Accepted Answer · 2013-10-13 20:43:23Z

3

Maybe something like this?

$query = $this->db
        ->select('*')
        ->from('sets')

        ->where_in('type', range('d','z'))

        ->order_by('date', 'asc');

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

answered Oct 13, 2013 at 20:43

Lajos Veres

13.7k7 gold badges47 silver badges57 bronze badges

Comments

user2869880 · Accepted Answer · 2013-10-13 20:41:14Z

0

select * from sets where type not in ('a','b','c')

answered Oct 13, 2013 at 20:41

user2869880

Comments

Nil'z · Accepted Answer · 2013-10-13 20:42:24Z

0

Make an array of the characters from d to z like:

$chars = array('d', 'e'...., 'z');

Now, use where_in clause like this:

$rs = $this->db->select('')->where_in('type', $chars)->get('table');

answered Oct 13, 2013 at 20:42

Nil'z

7,4831 gold badge20 silver badges28 bronze badges

Comments

Ally · Accepted Answer · 2013-10-13 20:46:22Z

0

$this->db->where('type !=', 'a');
$this->db->where('type !=', 'b');
$this->db->where('type !=', 'c');
$this->db->order_by('date', 'asc');
$query = $this->db->get('sets');

This should work.

Also note there is no need to use SELECT or FROM when selecting everything and using GET.

answered Oct 13, 2013 at 20:46

Ally

9551 gold badge15 silver badges33 bronze badges

Collectives™ on Stack Overflow

Codeigniter query builder SELECT query WHERE column value in in a range of letters

5 Answers 5

Update

3 Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Update

3 Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related