Reusing results of a query and table error

[mikeprag]

Hi,

Firstly, thank you so much for this framework. It is fantastic and very powerful.

I am just working my way through the different components and have 2 questions:

1. If I have a SQL query that I effectively want to bind to 2 components (lets say a chart and a table), how can I do this without issuing the SQL again i.e. At the moment, I can only get it to work if I Select pie chart component -> query -> Select table component -> query

2. If I introduce TRUE as sort, TRUE as search; into my component select, I get the following error:
   error returned from database: Invalid column name 'TRUE'.
   Am I missing something here (it works if I remove those 2 directives). I have tried all the examples from the docs and they all error in the same way.

Much appreciated for any help

Thanks

[francesco-cattoglio]

Hello there!
For the second point we need a code listing, at the very least the select 'component_name' as component query in full, to understand what is the exact problem.

W.r.t. the first question, I found myself in a very similar situation, and I personally solved it with a run_sql command.
My code is structured as follows:
1- the "real" query is in a file outside of the web_root of the project, so that nobody lands on it by mistake. This my_query.sql file fetches the data I need and stores it in a temporary table.

drop table if exists my_query_results;
create temp table my_query_results as (
    select
        id,
        h as height,
        l as length,
        product_name
    from product_details
    left join product_names using (id)
);

Inside the actual web page I have the following code:

set run_sql_result = sqlpage.run_sql('../path/to/utils/my_query.sql');

select 'table' as component;
select * from my_query_results;

select 'custom-chart-component' as component;
select * from my_query_results;

Since anything you call with sqlpage.run_sql() is on the same connection as the originating sql file, you can use any variable that you want, just like if you were writing everything in the same file. The table being temporary also means that it will be cleaned up when the database connection is dropped, it will not stay in your DB forever. Also, different connections will have a different temporary tables, so there should be no risk of multiple users stepping on other user's toes.

You can probably also keep everything in a single file, just creating the table before the component select and then dropping it just before the file end, but I needed to reuse that query from multiple web pages so I kept it in an external file.

P.S: the set run_sql_result is required otherwise SQLite gave me a syntax error, even if I never actually read the variable contents.

[lovasoa]

Invalid column name 'TRUE'

I guess you are using Microsoft SQL server, right ? SQL Server does not have boolean literals (TRUE and FALSE). Instead, it uses numeric values 1 and 0. So your query becomes

select 'table' as component, 1 as sort, 1 as search;

[mikeprag]

Thank you both...Yes, using SQL Server...I didn't realise that the component calls had to be in line with the target DB as well so that makes sense.

Will give the temp table route a go (but using SQL syntax).

Thanks for the quick repsonse

Mike

[accforgithubtest]

I am trying to migrate from MariaDB/Mysql to Postgres, and turns out the set <var_name> is the thing I miss the most in postgres.
The with in postgres has limited scope, and cannot be re-used across multiple select queries.

I have seen suggestions to use another temp table, however that seems more cumbersome compared to how easy/straight forward the set in mariadb/mysql is. Also When there are multiple re-usable queries, having many temp tables doesn't seem to be a clean solution either.

Are there other recommendations / suggestions on how this query re-usability approach can be implemented when using postgres, so that it follows some good practices while helping to re-use the sql queries ?

[lovasoa]

What do you mean @accforgithubtest ? Do you have an example of a set statement that fails with postgres ? If so, can you open a new issue ?

The variable system is implemented by sqlpage itself, not by the database. SQLPage parses your sql source code, and for every set x = y statement, it instead executes select y on the database, and then stores the result locally. Later when you use $x in another statement, sqlpage passes the saved value back to the database. This works on all databases that SQLPage supports: mysql, sqlite, mssql, and postgresql.