1. Fabrik 3.9 has been released. If you have updated Joomla to 3.9, this is a required update.
    Dismiss Notice

A list what gets data from a view table

Discussion in 'Community' started by Egocentralis, Jan 17, 2021.

  1. Egocentralis

    Egocentralis Member

    Level: Community
    Hello,

    We have a list/form on our site with one ID field, one name field (werknemer) and five radio button fields. The list is connected to a DB TABLE called ( g4e_orakel_vorige_eindmaand) I have tried to apply filters on this list but nothing seemed to work.

    Then, after looking in phpmy admin i discovered that this was a VIEW table. So, it gets the data from somewhere else. I looked every field in the list, all settings in the form, but I couldnt find how the VIEW table gets and returned the data.

    Then I exported the table row to see if i could find a clue to create the desired filter. I saw a long query but still i dont know how or where i can change it. I copy the query overhere:

    CREATE VIEW `g4e_orakel_vorige_eindmaand` AS select `p`.`id` AS `id`,concat(`p`.`achternaam`,', ',`p`.`roepnaam`,' ',`p`.`tussenvoegsel`) AS `medewerker`,ifnull((select `e`.`uren` from `g4e_orakel_eindmaand` `e` where ((`e`.`persid` = `p`.`persid`) and (`e`.`maand` = month((curdate() - interval 1 month))) and (`e`.`jaar` = year((curdate() - interval 1 month))))),'0') AS `uren_gesloten`,ifnull((select `e`.`km` from `g4e_orakel_eindmaand` `e` where ((`e`.`persid` = `p`.`persid`) and (`e`.`maand` = month((curdate() - interval 1 month))) and (`e`.`jaar` = year((curdate() - interval 1 month))))),'0') AS `km_gesloten`,ifnull((select `e`.`klant` from `g4e_orakel_eindmaand` `e` where ((`e`.`persid` = `p`.`persid`) and (`e`.`maand` = month((curdate() - interval 1 month))) and (`e`.`jaar` = year((curdate() - interval 1 month))))),'0') AS `klant_gesloten`,ifnull((select `e`.`overig` from `g4e_orakel_eindmaand` `e` where ((`e`.`persid` = `p`.`persid`) and (`e`.`maand` = month((curdate() - interval 1 month))) and (`e`.`jaar` = year((curdate() - interval 1 month))))),'0') AS `overig_gesloten` from `g4e_orakel_personeel` `p` where (`p`.`status` = '1') ;

    A few questions:

    1. Where can i find this query in my list/form/group or element so i can change it?
    2. How can i get the persid or userid in the list so i can create a filter? I do want to show only users/employees belonging to a company_id (bedrijf_id)
    3. How can i create a view table via fabrikar? or... to be short can someone explain to me how to work with this?
    Long story short... HELP.. because i do not know what to do with this list.. or how to edit it.

    Thanks in advantage
     
  2. troester

    troester Well-Known Member Staff Member

    Level: Community
    1. + 3. You can't. MySQL views are created/managed directly in your database, e.g. with phpMyAdmin. Somebody must have created this MySQL view.

    2. persid and userid must be columns in your view and elements in your Fabrik form/list

    In general: A MySQL view is readonly, so a Fabrik list pointing to a MySQL view should have record add/edit/delete access=nobody and "Alter field types"=no (in list "Advanced" tab).
     
  3. Egocentralis

    Egocentralis Member

    Level: Community
    Oke, so, the only thing what is in the list is the full employee name... no user id or something like that. Then its not possible to create a filter if I understand you right?

    The only thing I can do is trying to write a sql query like the one above to include the user id and the company id to make it possible to add a filter?

    If so, then i have to put this on my wishlist... because thats abracadabra to me :)
     
  4. Nagtegaal

    Nagtegaal Member

    Level: Community
    Egocentralis likes this.
  5. hominid4

    hominid4 Member

    Level: Community
    To adjust the View within phpMyAdmin - within in the left column, click on your view table ("Your Database >> Views >> Your Table"), and then when your View table is showing in the right window, click on the 'Structure' tab at top and click the "Edit view" text link at the bottom of the table, you can update your 'Select' query there.

    You'll then need to add any new columns to your Fabrik elements.

    As Nagtegaal mentioned, you can still make Fabrik elements filters no matter that your list is based on a View.
     
    Egocentralis likes this.
  6. Egocentralis

    Egocentralis Member

    Level: Community
    Ok, that will be a nice project to do, sooner or later i will start to understand queries, fabrikar, joomla.... its almost a real adventure... never worked with this before... but slowly i manage.

    I will try to do this, and if i have questions i will post them here.

    And yes the tabelnamen klinken nederlands.
     
  7. Egocentralis

    Egocentralis Member

    Level: Community
    Ok, I do have two questions:

    Filtering an element:

    Is it correct that when filtering an element, people have to give a keyword on the frontend and then the element gets filtered? Because what I need is the list to be allready filtered because i want people not to see details of other companies... But i think, that people have to filter the list themselves?

    When editing the view query via edit view in phpmyadmin. I think i can rewrite the query and add an extra column in the view. But, do I have to create an element first in the list with the column name, or do i have to create the element after changing the view table?
     
  8. lousyfool

    lousyfool Active Member

    Level: Community
    (1) Use a list pre-filter for this: http://fabrikar.com/forums/index.php?wiki/list-pre-filters/
    There you can pre-filter by the users' company ID, so that on list load they see only those records.

    (2) First edit the MySQL view and add a column. But Fabrik won't know about it, of course. So, next you'll need to add a Fabrik element with the column name to a group in that Fabrik list.
     
    Egocentralis likes this.
  9. Egocentralis

    Egocentralis Member

    Level: Community
    Ok, i managed to get this like i want. I had some big issues with undefined algorythm, superuser, etc and how many of ( ) i had to use. but its working..

    ifnull((select `e`.`bedrijf_id` from `dbrqdg58wpf72x`.`g4e_orakel_eindmaand` `e` where ((`e`.`persid` = `p`.`persid`) and (`e`.`maand` = month((curdate() - interval 1 month))) and (`e`.`jaar` = year((curdate() - interval 1 month))))),'0') AS `bedrijf_id`

    So, i pasted this line in the query...

    select `p`.`id` AS `id`,concat(`p`.`achternaam`,', ',`p`.`roepnaam`,' ',`p`.`tussenvoegsel`) AS `medewerker`,ifnull((select `e`.`uren` from `dbrqdg58wpf72x`.`g4e_orakel_eindmaand` `e` where ((`e`.`persid` = `p`.`persid`) and (`e`.`maand` = month((curdate() - interval 1 month))) and (`e`.`jaar` = year((curdate() - interval 1 month))))),'0') AS `uren_gesloten`,ifnull((select `e`.`km` from `dbrqdg58wpf72x`.`g4e_orakel_eindmaand` `e` where ((`e`.`persid` = `p`.`persid`) and (`e`.`maand` = month((curdate() - interval 1 month))) and (`e`.`jaar` = year((curdate() - interval 1 month))))),'0') AS `km_gesloten`,ifnull((select `e`.`klant` from `dbrqdg58wpf72x`.`g4e_orakel_eindmaand` `e` where ((`e`.`persid` = `p`.`persid`) and (`e`.`maand` = month((curdate() - interval 1 month))) and (`e`.`jaar` = year((curdate() - interval 1 month))))),'0') AS `klant_gesloten`,ifnull((select `e`.`bedrijf_id` from `dbrqdg58wpf72x`.`g4e_orakel_eindmaand` `e` where ((`e`.`persid` = `p`.`persid`) and (`e`.`maand` = month((curdate() - interval 1 month))) and (`e`.`jaar` = year((curdate() - interval 1 month))))),'0') AS `bedrijf_id`,ifnull((select `e`.`overig` from `dbrqdg58wpf72x`.`g4e_orakel_eindmaand` `e` where ((`e`.`persid` = `p`.`persid`) and (`e`.`maand` = month((curdate() - interval 1 month))) and (`e`.`jaar` = year((curdate() - interval 1 month))))),'0') AS `overig_gesloten` from `dbrqdg58wpf72x`.`g4e_orakel_personeel` `p` where (`p`.`status` = '1')
     
    hominid4 likes this.
  10. Nagtegaal

    Nagtegaal Member

    Level: Community
    good job, now add the element to your list with the name 'bedrijf_id'
     
  11. Egocentralis

    Egocentralis Member

    Level: Community
    yes i did that.. and it works good...

    now i have another question about a mail thing.. do i need to make another topic since it is on another place off the website??
     
  12. lousyfool

    lousyfool Active Member

    Level: Community
    Yes please.
     
    Egocentralis likes this.

Share This Page