1

I have an SQLITE Database with the following tables

items(item_id,item_name,....);
tags(tag_id,tag_name);
items_tags(item_id,tag_id);

I'm using QSqlRelationalTableModel class to for the items table.

this class have this function QSqlTableModel::setFilter(const QString &filter);

The filter is a SQL WHERE clause without the keyword WHERE

for example: setFilter("item_name=arg");

what I want to do is to use the setFilter function to select only items with a specific tag/tags.

since I can only use the setFilter(QString &Filter) function which is basically a WHERE clause

I found this solution which I think is a bad one

I'll use this filter

setFilter(FilterString);


FilterString="item_id=arg1 or item_id=arg2 or item_id=argX or......"

the IDs will be obtained from this query

Query.prepare("select item_id from items_tags where tag_id=?");

the FilterString will be generated and incremented by a (do while loop) using Query.next() function that will loop over the resulted Query IDs

FilterString+="item=Query.value(item_id).toString();

and use like 2 if statements to determine when to add the or clause to the FilterString

this solution will definitely work but I think it's stupid because if there are many tagged items(say 5000) the Query string will contain 4999 or clause and another 5000 "id=x" so the Query will be more than 10000 characters long.

QString object can contain a lot more characters but is this Query will have a big impact on performance ?

what are the other alternatives, Even if it requires to switch to another DB like ORACLE or Mysql since SQLITE doesn't have many internal features

sadeq
  • 21
  • 1
  • 5
  • is it like a subscribe to some category or item tags to get updates of new items/arrivals ? – Redanium Feb 02 '18 at 00:18
  • no, I'm having a BuyList software, which are basically items I have brought for my self like Electronics, furniture ,other, I have three levels of categories and even a state field(whishlist,brought items) field....but the tags are for another thing I wan't to use the tags for something like (home theater project,setup,living room,important,optional) there are a lot of items that can be tagged with such tags like TV, furniture items and a lot of other items – sadeq Feb 02 '18 at 00:33
  • @sadeq, I am not finding a reason why you are using filter here, you already query the DB down to what you want .. both _query_ and filter take strings **you are doubling the work** – Mohammad Kanan Feb 03 '18 at 18:50
  • @sadeq, see this https://stackoverflow.com/questions/532192/using-in-in-a-where-clause-where-the-number-of-items-in-the-set-is-very-large – Mohammad Kanan Feb 03 '18 at 19:01
  • thanks, this is useful ! @MohammadKanan – sadeq Feb 09 '18 at 01:04

0 Answers0