CODEWITHSUNDEEP
javaandroidsqliteandroid-sqlite
Harsha M V
Harsha M V

Reputation: 54989

Android SQLite Query with IN()

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

  1. Query and get all Categories whose is_active = 1
  2. 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 ?

Upvotes: 2

Views: 155

Answers (1)

Joachim Isaksson
Joachim Isaksson

Reputation: 181097

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

Upvotes: 3

Related Questions