CODEWITHSUNDEEP
c#sqlsqlite
ahmad chaker
ahmad chaker

Reputation: 73

paging in sqlite table using c#

I'm trying to page through this table so I ended up with this query (which is not working).

SELECT MIN(user_uid) AS FIRST,
MAX(user_uid) AS LAST,
user_number, user_name
FROM user_table
WHERE user_uid > MAX(FIRST,{0}) AND user_uid < MIN(LAST,{1})
AND ( user_name LIKE '%{2}%' OR user_number LIKE '%{3}%' )
AND user_category={4} OR user_category={5}
ORDER BY user_uid LIMIT {6}

I'm getting parameters' values from my c# variables. I'm storing FIRST, LAST externally to use them for moving to next/previous pages.

I created this index too:

CREATE INDEX idx1 ON user_table (user_uid);

please note: I saw this answer, but this is the only way I thought about to get First and Last values.

How to page in this table efficiently?

Upvotes: 1

Views: 2008

Answers (1)

Underscore
Underscore

Reputation: 1057

Your query isn't working because Min(Column) and Max(Column) in SQL returns an evaluation (aggregate) of all rows so doesn't know what value to have for every row in the non-aggregate query in your question.

If you really need the min and max you could return them in separate select statements so your dataset has 3 tables (if you're using ADO).

However paging should generally be achieved using offset as in the case below (note that the order by should make the query deterministic so it returns the same results every time).

SELECT
user_number, 
user_name
FROM user_table
WHERE ( user_name LIKE '%{1}%' OR user_number LIKE '%{2}%' )
AND user_category={3} OR user_category={4}
ORDER BY user_uid LIMIT {5} OFFSET {6}

Where {5} is page size and {6} is page number * page size.

Sorry if this doesn't answer your question, it's not clear what you want FIRST and LAST for. Page number and size are generally kept track off by the UI. Additionally, I haven't tested the above in SQLite.

Note

The code you've posted above looks vulnerable to SQL injection if you're using something like String.Format() to build your query. Consider changing this.

Upvotes: 2

Related Questions