Android SQLite Query with IN()

Question

I have the following Models

categories(id, name, is_active) places(id, name, category_id, .....)

Based on users preferences the categories will be 0 or 1. Based on this preference i want to query all the places whose categories.is_active = 1.

I think there is two approaches

First Approach

Query and get all Categories whose is_active = 1
Get a list of all the places from the above retrieved collection of Category ids.

Second Approach

Writing a RawQuery with JOIN and set the condition.

Which is the best way to do it to optimize speed ?

What i have tried ?

// Get Active Categories
public Cursor getActiveCategories() {
    return db.query(TABLE_CATEGORIES, new String[] { COLUMN_ID,
            COLUMN_NAME, COLUMN_SLUG, COLUMN_ACTIVE }, COLUMN_ACTIVE + "="
            + "'1'", null, null, null, COLUMN_NAME, null);
}


// Get Places
public Cursor getPlaces() {
    return db.query(TABLE_PLACES, new String[] { COLUMN_ID, COLUMN_NAME },
            " type IN ('1','2')", null, null, null, null);
}

In the above code how to insert the type IN ('1','2') dynamically so that i can populate that from the result from the first method ?

Joachim Isaksson · Accepted Answer · 2013-01-11 19:29:43Z

3

A single rawQuery will almost always be faster than two separate queries. Since you don't have any parameters in the rawQuery, it will be pretty simple, something like;

SELECT places.* 
FROM places 
JOIN categories
  ON places.category_id = categories.id
WHERE categories.is_active = 1

answered Jan 11, 2013 at 19:29

Joachim Isaksson

182k28 gold badges297 silver badges307 bronze badges

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

1 Comment

Harsha M V Over a year ago

Thanks a lot. If i want to add all the columns from the table places also. how can i do that ?

Collectives™ on Stack Overflow

Android SQLite Query with IN()

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related