Re: [sqlite] FTS3 Question
Scott Hess wrote: I think you're going to have to run some code to generate the string to match against. The problem is that you need to take all of the 'query' fields from 'category' and combine them into a string like 'query1 OR query2 OR query3 OR ...'. I'm not aware of a way to do this with straight SQL. You could perhaps build an aggregate function which took strings and combined them, then it might be something like: SELECT guid FROM data WHERE text MATCH (SELECT string_join(query, ' OR ') FROM category); This function already exists, and is included in SQLite. It is called group_concat(). See http://www.sqlite.org/lang_aggfunc.html for details. SELECT guid FROM data WHERE text MATCH (SELECT group_concat(query, ' OR ') FROM category); HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS3 Question
What I'm trying to do is get the query strings that are stored in category executed against the text stored in data. Category is essentially a fixed set of content, whilst data changes. I could just step through category and execute each query individually, but I was looking for a way to do it in a single operation. Basically ' which contain _any_ of the 'query' items from 'category'? ' -Original Message- On Behalf Of Scott Hess Should the 'data' table be joinable with the 'category' table in some way? Are you trying to match rows in 'data' which contain _all_ of the 'query' items from 'category', or which contain _any_ of the 'query' items from 'category'? Do you mean to have a WHERE clause or anything on what you're pulling in from 'category'? As presented, you've got I do this, it doesn't work, which I can agree with. But I can't quite figure out what your intention for works is :-). -scott On Sat, May 17, 2008 at 12:49 AM, Mike Marshall [EMAIL PROTECTED] wrote: I have an FTS3 table created as follows CREATE VIRTUAL TABLE data USING fts3(guid, text) And a standard table created thus CREATE TABLE category (label, query) What I would like to be able to do is an SQL query of the form SELECT guid FROM data WHERE text MATCH SELECT query FROM category But I can't seem to get it to work. Should it work? And if it should can someone point out what I am doing wrong. Thanks Mike ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS3 Question
I think you're going to have to run some code to generate the string to match against. The problem is that you need to take all of the 'query' fields from 'category' and combine them into a string like 'query1 OR query2 OR query3 OR ...'. I'm not aware of a way to do this with straight SQL. You could perhaps build an aggregate function which took strings and combined them, then it might be something like: SELECT guid FROM data WHERE text MATCH (SELECT string_join(query, ' OR ') FROM category); Otherwise, just do the join in your application code and feed it back to the match. -scott [BTW, 'query' and 'text' are probably not strong column names, being part of SQL syntax already.] On Mon, May 19, 2008 at 11:36 PM, Mike Marshall [EMAIL PROTECTED] wrote: What I'm trying to do is get the query strings that are stored in category executed against the text stored in data. Category is essentially a fixed set of content, whilst data changes. I could just step through category and execute each query individually, but I was looking for a way to do it in a single operation. Basically ' which contain _any_ of the 'query' items from 'category'? ' -Original Message- On Behalf Of Scott Hess Should the 'data' table be joinable with the 'category' table in some way? Are you trying to match rows in 'data' which contain _all_ of the 'query' items from 'category', or which contain _any_ of the 'query' items from 'category'? Do you mean to have a WHERE clause or anything on what you're pulling in from 'category'? As presented, you've got I do this, it doesn't work, which I can agree with. But I can't quite figure out what your intention for works is :-). -scott On Sat, May 17, 2008 at 12:49 AM, Mike Marshall [EMAIL PROTECTED] wrote: I have an FTS3 table created as follows CREATE VIRTUAL TABLE data USING fts3(guid, text) And a standard table created thus CREATE TABLE category (label, query) What I would like to be able to do is an SQL query of the form SELECT guid FROM data WHERE text MATCH SELECT query FROM category But I can't seem to get it to work. Should it work? And if it should can someone point out what I am doing wrong. Thanks Mike ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS3 Question
Thanks for the help Scott, you've confirmed what I had concluded. Thanks again M -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Scott Hess Sent: 20 May 2008 17:00 To: General Discussion of SQLite Database Subject: Re: [sqlite] FTS3 Question I think you're going to have to run some code to generate the string to match against. The problem is that you need to take all of the 'query' fields from 'category' and combine them into a string like 'query1 OR query2 OR query3 OR ...'. I'm not aware of a way to do this with straight SQL. You could perhaps build an aggregate function which took strings and combined them, then it might be something like: SELECT guid FROM data WHERE text MATCH (SELECT string_join(query, ' OR ') FROM category); Otherwise, just do the join in your application code and feed it back to the match. -scott [BTW, 'query' and 'text' are probably not strong column names, being part of SQL syntax already.] On Mon, May 19, 2008 at 11:36 PM, Mike Marshall [EMAIL PROTECTED] wrote: What I'm trying to do is get the query strings that are stored in category executed against the text stored in data. Category is essentially a fixed set of content, whilst data changes. I could just step through category and execute each query individually, but I was looking for a way to do it in a single operation. Basically ' which contain _any_ of the 'query' items from 'category'? ' -Original Message- On Behalf Of Scott Hess Should the 'data' table be joinable with the 'category' table in some way? Are you trying to match rows in 'data' which contain _all_ of the 'query' items from 'category', or which contain _any_ of the 'query' items from 'category'? Do you mean to have a WHERE clause or anything on what you're pulling in from 'category'? As presented, you've got I do this, it doesn't work, which I can agree with. But I can't quite figure out what your intention for works is :-). -scott On Sat, May 17, 2008 at 12:49 AM, Mike Marshall [EMAIL PROTECTED] wrote: I have an FTS3 table created as follows CREATE VIRTUAL TABLE data USING fts3(guid, text) And a standard table created thus CREATE TABLE category (label, query) What I would like to be able to do is an SQL query of the form SELECT guid FROM data WHERE text MATCH SELECT query FROM category But I can't seem to get it to work. Should it work? And if it should can someone point out what I am doing wrong. Thanks Mike ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS3 Question
Should the 'data' table be joinable with the 'category' table in some way? Are you trying to match rows in 'data' which contain _all_ of the 'query' items from 'category', or which contain _any_ of the 'query' items from 'category'? Do you mean to have a WHERE clause or anything on what you're pulling in from 'category'? As presented, you've got I do this, it doesn't work, which I can agree with. But I can't quite figure out what your intention for works is :-). -scott On Sat, May 17, 2008 at 12:49 AM, Mike Marshall [EMAIL PROTECTED] wrote: I have an FTS3 table created as follows CREATE VIRTUAL TABLE data USING fts3(guid, text) And a standard table created thus CREATE TABLE category (label, query) What I would like to be able to do is an SQL query of the form SELECT guid FROM data WHERE text MATCH SELECT query FROM category But I can't seem to get it to work. Should it work? And if it should can someone point out what I am doing wrong. Thanks Mike ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] FTS3 Question
I have an FTS3 table created as follows CREATE VIRTUAL TABLE data USING fts3(guid, text) And a standard table created thus CREATE TABLE category (label, query) What I would like to be able to do is an SQL query of the form SELECT guid FROM data WHERE text MATCH SELECT query FROM category But I can't seem to get it to work. Should it work? And if it should can someone point out what I am doing wrong. Thanks Mike ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS3 Question
On May 17, 2008, at 9:49 AM, Mike Marshall wrote: SELECT guid FROM data WHERE text MATCH SELECT query FROM category Perhaps something along these lines: select data.guid fromdata joincategory on category.guid = data.guid where data.text match category.query Or something :) -- PA. http://alt.textdrive.com/nanoki/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
