CODEWITHSUNDEEP
androidsqlsqlite
Luuk D. Jansen
Luuk D. Jansen

Reputation: 4506

Getting only one row of a specific ordered set of records in Android and SQLite

I have a multilingual app, where the menu can have different translations. The user selects the preference of language by setting the level in the language table. The order of the menu itself is determined by the level of the MenuItem.

I created a quest to select the records, sorted first on menu level (to get the right order in the menu) and then on the language level (to get the right level of each item). But now I only wan to have the first item of each menu item (which would be the language with highest preference).

Initially I thought that I could use distinct, but that doesn't work on this occasion as the different records are different. I just only want to have the first on a particular menu level to be returned.

(I also have a commonId between those menu items representing the same menu item in different languages if needed (but in practice should results in the same as using level)

Any thoughts?

public List<MenuItem> findMenus(Long menuItem) throws Exception {
        if(menuItem == null) menuItem = -1l;

        SQLiteQueryBuilder _QB = new SQLiteQueryBuilder();
        _QB.setTables("MenuItem m INNER JOIN Language l ON m.languageCode=l.code");

        String[] rows = _fields.keySet().toArray(new String[0]);
        for(int i = 0; i < rows.length; i++){
            rows[i] = "m." + rows[i];
        }
        Cursor cursor = _QB.query(_db, rows, "m.parentId=? AND m.published=? AND l.enabled='1'", new String[] {String.valueOf(menuItem), String.valueOf(1)}, null, null, "m.level, l.level", null);

        List<MenuItem> list = new ArrayList<MenuItem>(cursor.getCount());
        if (cursor.moveToFirst()) {
            do {
                MenuItem item = getObject(cursor);
                list.add(item);
            } while (cursor.moveToNext());
        }
        cursor.close();
        return list;
    }

EDIT: MenuItem

ID  CommonId  Level Lang
------------------------
1   6         1     EN
2   6         1     DE
3   6         1     IT
4   21        2     DE
5   21        2     IT
6   8         3     EN
7   4         4     EN
8   4         4     IT

What I would like as a result is
ID  CommonId  Level Lang
------------------------
1   6         1     EN
4   21        2     DE
6   8         3     EN
7   4         4     EN


This would be the result if the language would have the following order
Lang  Level
----------
EN    1
DE    2
IT    3

Upvotes: 0

Views: 70

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Here is an approach that uses not exists:

select mi.id, mi.commonid, mi.level, mi.lang
from MenuItem mi join
     LangOrder lo
     on mi.lang = lo.lang
where not exists (select 1
                  from MenuItem mi2 join
                       LangOrder lo2
                       on mi2.lang = lo.lang
                  where mi2.level = mi.level and
                        lo2.level < lo.level
                 );

I think the use of the same name (level) in two different tables to mean two very different things can lead to confusion.

Upvotes: 1

Related Questions