Page 1 of 1

Create views in AppGini

Posted: 2019-04-25 09:31
by sjohn
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.

Re: Create views in AppGini

Posted: 2019-04-25 18:52
by tvloeimans
I'm also looking for a way to do this.

Re: Create views in AppGini

Posted: 2019-05-08 11:36
by a.gneady
This is planned in AppGini roadmap indeed. Stay tuned!

Re: Create views in AppGini

Posted: 2020-11-11 04:19
by soewandi
Is there create view feature in AG new release? This feature is very usefull.

Re: Create views in AppGini

Posted: 2020-11-11 22:42
by landinialejandro
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.

Re: Create views in AppGini

Posted: 2020-11-12 04:09
by soewandi
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? :)

Re: Create views in AppGini

Posted: 2020-11-14 18:27
by a.gneady
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.

Re: Create views in AppGini

Posted: 2020-11-17 10:14
by soewandi
Thank you for your response Ahmed. I like very much AG and keep develop the simple and powerfull AG. Bravo.
My best regards,
Soewandi

Re: Create views in AppGini

Posted: 2020-11-22 20:42
by Alisson
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>

Re: Create views in AppGini

Posted: 2020-12-07 15:00
by pfrumkin
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