Re: [sqlite] How do I get expression from column (smart folder orfilter implementation)
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)
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 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