Create views in AppGini

Wish to see a specific feature/change in future releases? Feel free to post it here, and if it gets enough "likes", we'd definitely include it in future releases!
Post Reply
sjohn
Veteran Member
Posts: 86
Joined: 2018-05-23 09:32

Create views in AppGini

Post by sjohn » 2019-04-25 09:31

It would be a good thing if you could create views in AppGini.

You should have the possibility to create a "special" table as a copy of another table. The field definitions should be shown automatically from the "original" and should not be changeable. You should have the possibility to add new fields to the table. A field should be a definition of other fields in AppGini, and where you could reference to another field if you had a one-to-one relation. ( could be a field from a parent table ).
Also calculated fields. If you in a table have field1, field2, field3, and field1 and field2 hold numeric values, then you should be able to define a field7 =field1*field2 and then you in the view have a field7 that shows the calculated value of field1 * field2.

AppGini should then not create the view-table but create the view as defined in the view-table.
And the view-table should be treated as a normal table in AppGini, where you could specify if fields should be shown in table view and/or in detail view and other things apart from fields database definition.

tvloeimans
Posts: 22
Joined: 2019-03-20 13:46

Re: Create views in AppGini

Post by tvloeimans » 2019-04-25 18:52

I'm also looking for a way to do this.
©2019 - No rights reserved

User avatar
a.gneady
Site Admin
Posts: 1281
Joined: 2012-09-27 14:46
Contact:

Re: Create views in AppGini

Post by a.gneady » 2019-05-08 11:36

This is planned in AppGini roadmap indeed. Stay tuned!
:idea: AppGini plugins to add more power to your apps:
  • DataTalk is an innovative AppGini plugin based on ChatGPT that allows you to interact with your AppGini database using natural language questions, without writing any SQL. Check the demo video
  • Mass Update plugin: Update multiple records at once and improve your workflow efficiency.
  • Check our other plugins and get a generous discount of up to 30% when buying 2 or more plugins.

soewandi
Posts: 17
Joined: 2019-11-20 14:00

Re: Create views in AppGini

Post by soewandi » 2020-11-11 04:19

Is there create view feature in AG new release? This feature is very usefull.

User avatar
landinialejandro
AppGini Super Hero
AppGini Super Hero
Posts: 126
Joined: 2016-03-06 00:59
Location: Argentina
Contact:

Re: Create views in AppGini

Post by landinialejandro » 2020-11-11 22:42

sjohn wrote:
2019-04-25 09:31
It would be a good thing if you could create views in AppGini.

You should have the possibility to create a "special" table as a copy of another table. The field definitions should be shown automatically from the "original" and should not be changeable. You should have the possibility to add new fields to the table. A field should be a definition of other fields in AppGini, and where you could reference to another field if you had a one-to-one relation. ( could be a field from a parent table ).
Also calculated fields. If you in a table have field1, field2, field3, and field1 and field2 hold numeric values, then you should be able to define a field7 =field1*field2 and then you in the view have a field7 that shows the calculated value of field1 * field2.

AppGini should then not create the view-table but create the view as defined in the view-table.
And the view-table should be treated as a normal table in AppGini, where you could specify if fields should be shown in table view and/or in detail view and other things apart from fields database definition.
HI!
One thing that I do and that has worked for me in this case is to create a table in our project with the name of the view for example: _mytable. with all the fields that our view needs to have. Then you have to create the view itself in phpmyadmin.
The view should have the name of the new table without the underscore "_".

Code: Select all

CREATE OR REPLACE VIEW mytable AS
SELECT
    CONCAT_WS ('', ʻid`, 'item') as code,
    ʻId` as identifier,
    'item' as table,
    `title` as title,
   from
    ʻA_table`
LEFT JOIN `algo` AS algo
ON
    ʻAlgo`. ʻid` = ʻitem`. ʻid`
LEFT JOIN `algo2` AS algo2
ON
    ʻalgo2`. ʻid` = ʻitem`.`group`
even that select can be executed from php with the sql function.

once the project is generated. we will find the file _mytable_view.php.
In this file we will have to replace all references to table _mytable with mytable. of course, you have to remove the permissions to add records and delete records.
then it can be used and can only be viewed in TV mode.
it is somewhat complicated but it works.
Alejandro.
AppGini 5.98 - Linux OpenSuse Tumblewweed.

Some of my posts that may interest you:
:arrow: Landini Admin Template: Template for Appgini like AdminLTE
:arrow: Profile image plugin: add and changue image user profile
:arrow: Field editor in table view: Configurable fast edit fields in TV
:idea: my personal page

soewandi
Posts: 17
Joined: 2019-11-20 14:00

Re: Create views in AppGini

Post by soewandi » 2020-11-12 04:09

Thank you Landinialejanro for your tutorial. Actually I hope AG should have "create view" feature to make non expert like me easier. To Mr Ahmed, when will that feature become reality? :)

User avatar
a.gneady
Site Admin
Posts: 1281
Joined: 2012-09-27 14:46
Contact:

Re: Create views in AppGini

Post by a.gneady » 2020-11-14 18:27

Ahmed, when will that feature become reality?
Hmm ... sorry no ETA yet ... we need to study the effect of views in many parts of the generated code so it would need some research. Once we're confident it works smoothly, we'll include it the soonest possible into AppGini.
:idea: AppGini plugins to add more power to your apps:
  • DataTalk is an innovative AppGini plugin based on ChatGPT that allows you to interact with your AppGini database using natural language questions, without writing any SQL. Check the demo video
  • Mass Update plugin: Update multiple records at once and improve your workflow efficiency.
  • Check our other plugins and get a generous discount of up to 30% when buying 2 or more plugins.

soewandi
Posts: 17
Joined: 2019-11-20 14:00

Re: Create views in AppGini

Post by soewandi » 2020-11-17 10:14

Thank you for your response Ahmed. I like very much AG and keep develop the simple and powerfull AG. Bravo.
My best regards,
Soewandi

Alisson
Veteran Member
Posts: 81
Joined: 2017-02-25 20:32

Re: Create views in AppGini

Post by Alisson » 2020-11-22 20:42

I'm not an expert as well but I've been working with a free library called "Tabulator" to create views for some of my tables. Very easy to use with appgini hooks and with very good results.
I've used the appgini custom page tutorial described here: https://bigprof.com/appgini/help/advanc ... cess-pages
The code below will create a view from your desired table, just change "tablename" to the name of your database table.
As I say, I'm not an expert but if some of the experts here could let us know of any flaw with this, please let me know as well.

Code: Select all

<link href="https://unpkg.com/[email protected]/dist/css/tabulator.min.css" rel="stylesheet">
<script type="text/javascript" src="https://unpkg.com/[email protected]/dist/js/tabulator.min.js"></script>
<?php
    define('PREPEND_PATH', '../');
    $hooks_dir = dirname(__FILE__);
    include("$hooks_dir/../defaultLang.php");
    include("$hooks_dir/../language.php");
    include("$hooks_dir/../lib.php");
     
    /* grant access to all logged users */
    $mi = getMemberInfo();
    
    if(!$mi['username'] || $mi['username'] == 'guest'){
        echo "Access denied";
        exit;
    }

    $query = sql("SELECT * FROM `tablename`'", $eo);
    
    //$result = mysqli_query($query);
    $json_array = array();  
        while($row = mysqli_fetch_assoc($query))  
        {  
            $json_array[] = $row;
        }  
    $json = json_encode($json_array, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES | JSON_NUMERIC_CHECK);
?>

<div id="table"></div>
<script>
//define data array
var tabledata = <?php echo $json;?>;

//initialize table
var table = new Tabulator("#table", {
    data:tabledata,           //load row data from array
    layout:"fitColumns",      //fit columns to width of table
    responsiveLayout:"hide",  //hide columns that dont fit on the table
    pagination:"local",       //paginate the data
    paginationSize:7,         //allow 7 rows per page of data
    movableColumns:true,      //allow column order to be changed
    resizableRows:true,       //allow row order to be changed
    autoColumns:true, //create columns from data field names

});

</script>

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

Re: Create views in AppGini

Post by pfrumkin » 2020-12-07 15:00

I see how a feature to create views in AG would be a heavy lift. A lot of considerations. I use views primarily in a relational way, to join tables, not so much to create a read-only kind of view. Managing the joins would complex to automate.

I create a template table for the view in AG. Then I have a _refresh php script that drops the table and does the SQL to create the view. When I modify underlying tables with columns I want to include in my view, I have to drop the view, let the AG generate create and update the template table, then run my _refresh script. It's a pain. I am also still learning how necessary it is to maintain column type consistency in the view with the underlying tables. Most of the time it doesn't really matter because I am using the views strictly for reporting, not display and of course no edit.

~Paul

Post Reply