-
New Commercial Services Section
We have now opened a commercial services section here on the forum for registered users. If you have a Fabrik project that you wish to have someone work on for you, post it under Help Wanted. If you are an application developer and wish to earn some money helping others, post your details under Fabrik Application Developers.
Both of these are unmoderated. It will be up to both parties to work out the details and come to an agreement.
-
Joomla 5.1
For running J!5.1 you must https://fabrikar.com/forums/index.php?wiki/update-from-github/ or include the new file manually https://fabrikar.com/forums/index.php?threads/joomla-5-1-and-fabrik-cannot-find-files-error.54473/post-285151 See also Announcements
create a Fabrik List from custom query
- Thread starter blondie63
- Start date
Jaanus
Super Moderator
Not always readonly. When your MySQL view queries only one table (no joins to other tables) and only the existing fields (no extra data like SELECT 1 as number) and doesn't use union - then this view would be updateable.
Trick - if you create a new list (and new table), add needed elements to it (let's say autocreated id and date_time + firstfield, secondfield), then you can drop the table from database and create a view WITH THE SAME NAME instead that makes query to your other table (select othertable.id, othertable.field1 as firstfield, othertable.field2 as secondfield FROM othertable WHERE ... and you can add, modify or delete the data in othertable through this view! You can also make list joins with this, no matter to the real tables or views.
Trick - if you create a new list (and new table), add needed elements to it (let's say autocreated id and date_time + firstfield, secondfield), then you can drop the table from database and create a view WITH THE SAME NAME instead that makes query to your other table (select othertable.id, othertable.field1 as firstfield, othertable.field2 as secondfield FROM othertable WHERE ... and you can add, modify or delete the data in othertable through this view! You can also make list joins with this, no matter to the real tables or views.
cheesegrits
Support Gopher
Shhhhhhh!
There's a reaosn we tell people it'll be read-only, which is because the factors in MySQL which govern whether a view can be written are more complex than your description, as they vary from version to version, and we really don't want to get in to supporting writeable views, as it'd be a nightmare.
So please bear in mind that we DO NOT RECOMMEND attempting to use a view as a writeable list, and even if MySQL considers it writeable, Fabrik may or may not be able to write to it. For instance, in certain circumstances we do things like looking up the PK of a table, which will produce no result on a view.
So yes. Technically it's possible. But as far as official support is concerned, a List built on a view is not writeable.
If you want to do a Wiki page on "using views in Fabrik", that would be very useful. But it does need to be accompanied by a very large, bold, italic, flashing red "HERE BE DRAGONS!" warning!
-- hugh
There's a reaosn we tell people it'll be read-only, which is because the factors in MySQL which govern whether a view can be written are more complex than your description, as they vary from version to version, and we really don't want to get in to supporting writeable views, as it'd be a nightmare.
So please bear in mind that we DO NOT RECOMMEND attempting to use a view as a writeable list, and even if MySQL considers it writeable, Fabrik may or may not be able to write to it. For instance, in certain circumstances we do things like looking up the PK of a table, which will produce no result on a view.
So yes. Technically it's possible. But as far as official support is concerned, a List built on a view is not writeable.
If you want to do a Wiki page on "using views in Fabrik", that would be very useful. But it does need to be accompanied by a very large, bold, italic, flashing red "HERE BE DRAGONS!" warning!
-- hugh
Jaanus
Super Moderator
Thanks, Hugh! I will do it as soon I can (I mean the wiki).
BTW - thanks to one of the recent fixes one good reason to use "updateable views" is lost as well. I mean the issue where the joined data was incorrectly deleted when it was prefiltered out and therefore not present in form. I hope this fix works.
BTW - thanks to one of the recent fixes one good reason to use "updateable views" is lost as well. I mean the issue where the joined data was incorrectly deleted when it was prefiltered out and therefore not present in form. I hope this fix works.
h.kleinveld
Member
I have a similar thing, I want to create a custom query which needs to be something like this, and I don't think I can do it via URL filtering or the content plugin:
Select * from table1 WHERE
(value1<0 AND value2 >0) OR
(value1=0 AND value2=0 AND value3>0) OR
(value1=0 AND value2=0 AND value4>0) OR
(value1=0 AND value2=0 AND value5>0)
I have 7 values with all kinds of different combinations. (it's results from blood tests, and it should filter a list of supplements etc. A set of value 1&2 should never conflict, and the other 5 variables can either be >0 or <0 so I need a lot of combinations. So via a custom query would be the best. I can program in PHP that it creates the custom query, but how do I get fabric to then parse it nicely through the list template?
Select * from table1 WHERE
(value1<0 AND value2 >0) OR
(value1=0 AND value2=0 AND value3>0) OR
(value1=0 AND value2=0 AND value4>0) OR
(value1=0 AND value2=0 AND value5>0)
I have 7 values with all kinds of different combinations. (it's results from blood tests, and it should filter a list of supplements etc. A set of value 1&2 should never conflict, and the other 5 variables can either be >0 or <0 so I need a lot of combinations. So via a custom query would be the best. I can program in PHP that it creates the custom query, but how do I get fabric to then parse it nicely through the list template?
cheesegrits
Support Gopher
You can't create custom queries and have Fabrik render them as lists.
You could probably create a set of pre-filters to do it.
Or you can create a MySQL view, and create a read-only Fabrik list on that.
-- hugh
You could probably create a set of pre-filters to do it.
Or you can create a MySQL view, and create a read-only Fabrik list on that.
-- hugh
h.kleinveld
Member
Maybe, I just have something like 7x7 different combinations.
h.kleinveld
Member
I need to view it.
Basically I have a blood test result that says: increase/decrease or leave value1, increase/decrease or leave value2, increase/decrease value3,4,5,6,7. 1&2 are linked, and 3,4,5,6,7 should not effect 1 and 2.
Basically I have a blood test result that says: increase/decrease or leave value1, increase/decrease or leave value2, increase/decrease value3,4,5,6,7. 1&2 are linked, and 3,4,5,6,7 should not effect 1 and 2.
h.kleinveld
Member
Here's some screenshots.
The first is a database of what supplement or activity has what effect on the blood. The second is an overview of the result of a blood test.
result of bloodtest:
The problem is is that it's not a simple AND filter.
Select * from table1 WHERE
(value1<0 AND value2 >0) OR
(value1=0 AND value2=0 AND value3>0) OR
(value1=0 AND value2=0 AND value4>0) OR
(value1=0 AND value2=0 AND value5>0)
etc.
As you can see all bloodtest result values can be -1,0,1. And have to filter accordingly.
The first is a database of what supplement or activity has what effect on the blood. The second is an overview of the result of a blood test.
result of bloodtest:
The problem is is that it's not a simple AND filter.
Select * from table1 WHERE
(value1<0 AND value2 >0) OR
(value1=0 AND value2=0 AND value3>0) OR
(value1=0 AND value2=0 AND value4>0) OR
(value1=0 AND value2=0 AND value5>0)
etc.
As you can see all bloodtest result values can be -1,0,1. And have to filter accordingly.
h.kleinveld
Member
I resolved it with some custom PHP work below the normal result page, which creates the right MySQL query, and then I just show it in a table. That part doesn't use Fabrik.