There are 23 elements in a SQLite database. You need to make a request for 10 items

for example, from 1 to 10, from 11 to 20, and from 20 to 23 (10, 10, 3).

I do this:

"SELECT * FROM table1 WHERE parent_id=7 ORDER BY name LIMIT position, 10"

And the method itself is as follows:

 Cursor cursor = dataBase.rawQuery("SELECT * FROM " + MyDataBase.TABLE_DRUGS + " WHERE " + MyDataBase.PARENT_ID + "=" + id + " ORDER BY " + MyDataBase.NAME // NAME!!!!!!!!! NO _id!! + " LIMIT "+ page + ", " + CommonUtilities.MAX_SIZE_MAS_FOR_DOWNLOADING);

The problem is that the request for the last 3 records (21,22,23) returns 10 records (14,15, .., 22,23).

How to change the request to not duplicate records? more precisely to take only from a given position to the end, but not more than what is set in "LIMIT"?

    1 answer 1

    Since almost a month has passed, and the horse did not roll here, and I found the answer to my question, I will write how I am here.

     // на вход принимается айди=8, номер страницы=1, значения "от"=5 и "до"=9 //id,price_from,price_to - фильтры для ограничения записей, page - постраничная загрузка (1,2,3,4..) public ArrayList<ItemDrugs> getGoods (long id, int page, double price_from, double price_to){ String where = MyDataBase.PARENT_ID + "=" + id; // "parent_id=8 (например 8) where += (price_from != 0 ? " AND "+MyDataBase.PRICE + ">"+price_from : ""); // parent_id=8 AND price>5 where += (price_to != 0 ? " AND "+MyDataBase.PRICE + "<"+price_to : ""); // parent_id=8 AND price>5 AND price<9 int count = getCountInTable(MyDataBase.TABLE_DRUGS, where); // вызываем метод(на количество строк) и передаём условие, описанное выше. может быть 84 int needCount = page * CommonUtilities.MAX_SIZE_MAS_FOR_DOWNLOADING; //тут номер загружаемой страницы * на 20. (20,40,60...) int startCount = page == 1 ? 0 : needCount - CommonUtilities.MAX_SIZE_MAS_FOR_DOWNLOADING; // если страница 1я, то берём записи с 0 позиции или //если page=2, startCount = (needCount)40 - 20 = 20 (startCount - стартовая позиция) // здесь наш любимый LIMIT. при page = 2, count = 84, needCount = 40, startCount = 20, limit будет " LIMIT 20, 20" // а как закачать последние 4 записи? проверим: // ------------------------ при page = 5, count = 84, needCount = 100, startCount = 80, limit будет " LIMIT 80, 4" String limit = " LIMIT " + (count < needCount ? startCount + ", " + (count-startCount) : startCount + ", " + CommonUtilities.MAX_SIZE_MAS_FOR_DOWNLOADING); Cursor cursor_result = dataBase.rawQuery("SELECT * FROM "+MyDataBase.TABLE_DRUGS + " WHERE "+ where + " "+limit); Log.e(TAG, "SELECT * FROM "+MyDataBase.TABLE_DRUGS + " WHERE "+ where + " "+limit); ArrayList<ItemDrugs> arrayList = new ArrayList<>(); while (cursor_result.moveToNext()) { ItemDrugs item = new ItemDrugs(); item.setId(cursor_result.getInt(cursor_result.getColumnIndex(MyDataBase.ID))); item.setName(cursor_result.getString(cursor_result.getColumnIndex(MyDataBase.NAME))); item.setDescription(cursor_result.getString(cursor_result.getColumnIndex(MyDataBase.DESCR))); arrayList.add(item); } return arrayList; }

    Method that returns the number of records by condition or without it:

     public int getCountInTable(String table, String where){ Cursor cursor; if (where != null){ cursor = dataBase.rawQuery("SELECT COUNT("+ MyDataBase.ID+") FROM " + table + " WHERE " + where); } else { cursor = dataBase.rawQuery("SELECT COUNT("+ MyDataBase.ID+") FROM " + table); } if(cursor.moveToFirst()){ return cursor.getInt(0); } return 0; }

    Although it is possible to say "crutch", but the code is working, I did not find another way to work with LIMIT. Either "LIMIT 20" is a sample from 0 to 20 (not inclusive up to 20), or "LIMIT 20, 29" is a sample from 20 positions with a limit of 29 elements. But! if you have 25 entries, and you write "LIMIT 20, 29", then it will not return 5 entries to you (they are the last), but it will return all 29, but they are also duplicate!

    So - working "crutch". There will be questions on the code - contact;)