Struggling to make a search page for big database. IF the search has multiple optional forms, how do I query the db without hard coding every query permutation?

[bejp0a]

Hello. I am fairly new to sql and very new to sqlpage. Apologies for any misunderstandings or omissions.

I am hoping to use it as a simple search page (and ideally csv exporter) for a big database that I frequently need to query. Adding data or changing the data in the db is not in scope, I just need a search page that looks like this:

That looks great, but it does not really do what I want under the hood. what I want is for sqlpage to run a query based on what feilds are filled out and what feilds are not. So when "Field1" and "Field5" are filled out it runs:

SELECT * FROM "Table1" WHERE "field1" = "$Field1" AND "field5" = "$Field5";

and if 1, 3 5 are filled out then query should be

SELECT * FROM "Table1" WHERE "field1" = "$Field1" AND "field3" = "$Field3" AND "field5" = "$Field5";

but I cant figure out how to structure the sql in a way that avoids me having to write out all 64 permutations. I see in this discussion something that sort of works, but when I implement a slightly modified version of the CASE test in the answer to that example the speed of the query takes a massive hit, to the point that its essentially unusable.

This sqlite database is more then 1TB and I don't have great hardware, but even so the difference the case test and the specific search is staggering.

I see in other threads the author mentioning that arbitrary sql queries are not really supported, but I dont think thats what I want. I have a list of specific queries I want to run, I just dont want to have to type them all out (the number of feilds I want included will probably grow tbh)

here is my current index.sql, any advice or suggestions on how I can get this working would be greatly appreciated.

SELECT 'form' as component, 'Search Tables' AS title, 'Search' as validate, 'GET' as method;

SELECT 
    'Table' as name,
    'Table to Search' as label,
    'select'  as type,
    TRUE      as required,
    1 as value,
    '[{"label": "Choose", "value": "1"}, {"label": "Table 1", "value": "2"}, {"label": "Table 2", "value": "3"}, {"label": "Table 3", "value": "4"}]' as options;

SELECT 'Field1' as label, 'Field1' as name, 'text' AS type, 5 as width;
SELECT 'Field2' as label, 'Field2' as name, 'text' AS type, 5 as width; 
SELECT 'Field3' as label, 'Field3' as name, 'text' AS type, 2 as width;
SELECT 'Field4' as label, 'Field4' as name, 'text' AS type, 5 as width;
SELECT 'Field5' as label, 'Field5' as name, 'text' AS type, 5 as width;
SELECT 'Field6' as label, 'Field6' as name, 'text' AS type, 2 as width; 

SELECT
        'table'         AS component,
        "Results"       AS title,
        TRUE            AS sort,
        TRUE            AS search;

SELECT * FROM "Table1" WHERE $Table = 2
AND
	CASE
		WHEN COALESCE($Field1,'') <> '' THEN 
			"Field1" = "$Field1" 
		ELSE "Field1" LIKE "*"
	END
AND
	CASE
		WHEN COALESCE($Field2,'') <> '' THEN 
			"Field2" = "$Field2" 
		ELSE "Field2" LIKE "*"
	END
LIMIT 1;

-- if it was not so slow, I would modify the above query to include all 6 feilds

SELECT * FROM "Table1" WHERE $Table = 3 AND "lname" = "$Field1" AND "field2" = "$Field2";

-- above works fantastic but I don't always use field1 and field 2. 

thanks in advance!

[lovasoa]

Hello and welcome to SQLPage!

I would love to know a little bit more about what you are building: a 1TB SQLite file sure isn't common!

About searching only the fields that were filled by the user, I think what you are looking for is a simple sequence of AND and OR operators:

select * from table1
where
  ($field1 = '' OR field1 = $field1) AND
  ($field2 = '' OR field2 = $field2) AND
  ($field3 = '' OR field3 = $field3);

Or am I missing something?

[heejit]

or create a new field with concat all the fields and create index on it

[heejit]

or use generated column and create index on it.

[bejp0a]

I ended up using a spreadsheet and ctl h to make a list of every possible query based on the availible forms, as well as a case test to print out a number from 1-63.

But now I cant figure out how to get sqlpage (or indeed sqlite) to perform a specific query based on a case test. IE,

SELECT CASE
WHERE $FILTER = 1 THEN (SELECT * FROM table WHERE f1 = $f1)
WHERE $FILTER = 2 THEN (SELECT * FROM table WHERE f1 = $f1 AND f2 = $f2)
ELSE (SELECT '0')

returns an error with "error returned from database: (code: 1) sub-select returns 33 columns - expected 1", and I cant figure out how to work around it. I guess CASE is only supposed to return one value? Is there a better way to get sqlpage to perform a specific query that I have hard coded based on the value of a variable? not an arbitrary query, it just needs to pick from a list of predifines set of queries that I know are safe.

I attempted to get your proposed solution to work as well, but again its simply to slow to be usable. takes less then a second to run SELECT * FROM table WHERE f1=$f1 AND f2=$f2 AND f3=$f3 but yours is taking more then a minute before I hit ctl c. It does say its using index's but in a weird order, perhapes becuase I built the index wrong, so I'm recreating the db and will attempt to run your example query (dont have space to add any more indexes or to vacume the db, since vaccuming creats a full copy and no space for that) and report back.

EDIT: ah wait, maybe I needed to try something like this

SELECT * FROM table 
WHERE (WHERE $FILTER = 1 AND f1 = $f1)
     OR (WHERE $FILTER = 1 AND f1 = $f1  AND f2 = $f2)
     OR (WHERE $FILTER = 1 AND f1 = $f1  AND f2 = $f2 AND f3 = $f3)
      ) ;

will give that a go when the db is recreated

[lovasoa]

Hi ! As I stated above, the best solution is probably not to create many different database queries.

This solves your problem, is concise, and uses indices:

SELECT * FROM test_table
WHERE
  (f1=$f1 OR f2=$f2 OR f3=$f3) AND
  ($f1 = '' OR f1=$f1) AND
  ($f2 = '' OR f2=$f2) AND
  ($f3 = '' OR f3=$f3);

[lovasoa]

Ok, I just read your updated answer above. If you really need to run dynamic sql queries, you can try the following: #518 (reply in thread)