How? Change tableview source to custom SQL query

If you're a new user of AppGini, feel free to ask general usage questions, or look for answers here.
Post Reply
User avatar
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 1156
Joined: 2019-05-21 22:42
Location: Germany
Contact:

How? Change tableview source to custom SQL query

Post by onoehring » 2021-08-22 10:37

Hi,

I would like to have the tableview not based on the actual table anymore, but I want to include some fields from linked (child) tables as well.
Thus I figure, I would need to change the source of TV to my custom SQL query AND I will need to change table headers as well: I will need a header/label for all fields in my custom query.
Could someone please point me a way (which file is best/the only I need to change)?
Is it maybe the root/tablename_view.php file only?
If so - is it possible to overwrite the code (variables) using a hooks file, so, that I will not need to repeat changes after regeneration of the application?

Why? The TV is being used as the most informative page which enables the user to search for things that are usually found in child tables only. Also the CSV export of the parent table can then include the fields from children which enables easy analysis using for example Excel.

Thank you already
Olaf

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: How? Change tableview source to custom SQL query

Post by pbottcher » 2021-08-23 16:01

Hi Olaf,

what you could try is to create a "dummy" table in AppGini that holds the data you want to show. Here you can change the SQL query to your needs to show all the data that you want ($options).

Hope that gives you a starting point
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

pfrumkin
Veteran Member
Posts: 157
Joined: 2020-02-18 17:58
Location: Albuquerque, New Mexico USA

Re: How? Change tableview source to custom SQL query

Post by pfrumkin » 2021-08-23 20:11

Hi Olaf,

To piggy-back off of what Pascal has suggested, could you use this dummy table to create a view in SQL (AG thinks it is a table, but drop the table in SQL and create a view based off of your new queries)? Maybe I am not understanding the problem. A limitation of this solution is that it becomes read-only and it can be a hassle to update the schema. This is basically how we do reporting.

~Paul

User avatar
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 1156
Joined: 2019-05-21 22:42
Location: Germany
Contact:

Re: How? Change tableview source to custom SQL query

Post by onoehring » 2021-08-24 04:48

Hi pbötcher & pfrumkin,

thank you for your ideas. I will probably use both and as far as I see, it's actually only the tablename_view.php file which I need to adjust: Change QueryFieldsCSV, QueryFieldsFilters, QueryFieldsQS, QueryFieldsTV and of course QueryFrom.
The idea to let AG do the work and generate the table and all other table related files and simply replace the source is good. If I can make use of a database view at this time I am not sure, but the reminder is gladly taken.

Thank you
Olaf

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: How? Change tableview source to custom SQL query

Post by pbottcher » 2021-08-24 08:25

Hi Olaf,

from my exerience it is not even necessary to go the route with the view. Leave the table as is from AppGini, use the init function to handle your query.
Make the table read-only as you will have fields that you add from other tables.
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.


pfrumkin
Veteran Member
Posts: 157
Joined: 2020-02-18 17:58
Location: Albuquerque, New Mexico USA

Re: How? Change tableview source to custom SQL query

Post by pfrumkin » 2021-08-25 18:09

Yes, I think this is much less maintenance than the view. However, this means you don't see the same data directly in the database as you do through AG, correct? From your description, this meets your need, you wanted to present the data in the TV.

I like it!

User avatar
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 1156
Joined: 2019-05-21 22:42
Location: Germany
Contact:

Re: How? Change tableview source to custom SQL query

Post by onoehring » 2021-08-26 04:59

Hi pfrumkin,

yes, initially I did want to see different (=additional) data in the TV than in the DV. Once I went through the process, I decided against this and kept the old way: Concat data from a child table into one field and save this to the parent table as copy. I am aware that this breaks nomalization, but the copied fields are read only in DV, thus can be changed in the child tables/registers only.

Olaf

Post Reply