Re: [sqlite] How do I get expression from column (smart folder orfilter implementation)

[Igor Tandetnik]

Yuzem  wrote:
> Thanks for the answer.
> I have another problem:
> I decided to use a view to do this, I will have to drop/create the view
> every time filters is updated but I don't know how to use multiple selects.
> I want to do something like this:
> 
> CREATE VIEW movies_filters AS SELECT 'rated',movie FROM movies WHERE rating
> != '' AND SELECT 'tagged',movie FROM movies WHERE movie in (SELECT movie
> FROM tags);

SELECT 'rated' as type, movie FROM movies WHERE rating != ''
UNION ALL
SELECT 'tagged' as type, movie FROM movies
WHERE movie in (SELECT movie FROM tags);

-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How do I get expression from column (smart folder orfilter implementation)

[Yuzem]

Thanks for the answer.
I have another problem:
I decided to use a view to do this, I will have to drop/create the view
every time filters is updated but I don't know how to use multiple selects.
I want to do something like this:

CREATE VIEW movies_filters AS SELECT 'rated',movie FROM movies WHERE rating
!= '' AND SELECT 'tagged',movie FROM movies WHERE movie in (SELECT movie
FROM tags);
-- 
View this message in context: 
http://old.nabble.com/How-do-I-get-expression-from-column-%28smart-folder-or-filter-implementation%29-tp30295959p30308922.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How do I get expression from column (smart folder orfilter implementation)

[Igor Tandetnik]

Igor Tandetnik  wrote:
> Yuzem  wrote:
>> Suppose I have this tables:
>> movies: movie,name,rating
>> tags: movie,tag
>> filters: filter,expression
>> 
>> In filters I have:
>> rated,rating != ''
>> tagged,movie in (select movie from tags)
>> 
>> But from the "filters" table (doesn't work):
>> SELECT filter,movie FROM filters LEFT JOIN movies WHERE movie IN (SELECT
>> movie FROM movie WHERE expression);
> 
> SQLite doesn't have anything like eval(). It can't take a string from a 
> column, then parse and interpret it as part of a SQL
> statement. 
> 
> You could probably write a custom function that would take (movie, 
> expression) as parameters, build a statement of the form
> 
> select 1 from movies where movie=:movie and ;
> 
> where  is replaced with the actual expression (e.g. using 
> sprintf), run this statement and return 1 if the statement
> produces any rows and 0 otherwise. Then you could write 
> 
> SELECT filter, movie FROM filters LEFT JOIN movies WHERE checkMovie(movie, 
> expression);

Correction: make it

SELECT filter, movie FROM filters LEFT JOIN movies ON checkMovie(movie, 
expression);

-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users