Join MySQL View and Repeat issue (bug?)

[dimdim]

New Fabrik user and I've encountered this strange issue using MySQL Views.

I'm working on a rather basic app where the data are in basically in two main tables in a many-to-many relationship, Customer and Services (a customer can purchase many services and a service can be bought from many customers). In reality it's a bit more complicated than that, but this basic model should replicate the issue.

So far the idea is to first create a customer, and then you can either create a new service and assign that customer there, or if he wants a service already created you can add him with a repeatable group.

Viewing the details of the records, there is no problem with the Services details showing all their customers. The problem is that I want to view in the details of each customer's form a list of all the services he has bought (another repeatable join).

If I leave the tables connected, when I create a customer, since the repeat group shows up in his form, if I won't fill it in with anything (as I don't know yet what service he wants), a new blank record is created in the Services table.

To avoid deleting all the empty records, I created a MySQL View of the Services table(named it View_Services) that I've joined with the Customer list, as I would join the Services table, and make the group Read Only (as I just need to view a list of the customer's services, not add services from the customer's form). All groups are set to Repeatable etc. And here is where the issue appears.

While in the list view of Customer all the data appear, when I view the details of the customer only the first record appears. Using fabrikdebug this is what I found:

form:getData returns an array of objects with all the data as they should be, while form:data only returns the 1st of the objects among all other data.

After searching the forums this bug seems pretty similar to another thread. I can't post links yet but the thread title is "Repeat group from view join shows only one item" from October 2014.

If I use the repeatable List join with the original Services list and not the View_Services I get the correct results and behaviour in the Customer form.

Is this a known bug of Fabrik? Perhaps github issue #708 is the same?

Any ideas on how to fix that, or another way to work around this issue would be greatly appreciated.

 

[troester]

I don't know the status of the "repeat group - mySQL view" issue.

But for your underlying problem:
set repeat minimum=0 the it won't create an empty record

 

[dimdim]

I found a workaround by setting on the MySQL View list (View_Services) display only priviledges (edit, delete etc are not applicable for any user). Then I added a placeholder at the bottom of the Customer form to show that list, so I have all the information that I want shown on the form. A positive side-effect of this approach is that when I edit the Customer form, the placeholder list at the bottom of the form is not visible(and it shouldn't be at least for what I want to do).

If I knew about the set repeat minimum = 0 thing earlier, I'd probably never go through all that as the empty records was the reason that I wanted that fixed. Good thing is that now it's working more like it is intended to, as in no way does it allow edit of the Services data through the Customer form.

 

[cheesegrits]

That issue with having a repeat group using a view is fixable. You just have to introduce that view to Fabrik as a List (even if you don't use it as it's own List, only using in the repeat join on another List) and designate a Primary Key in the list settings for it.

The issue is that MySQL views don't have the concept of a Primary Key. And we always need to know the PK of a table. Even if it's a view, you still have to have a field/element on it which functions as a "PK", that we treat as the "rowid" (unique numeric identifier) for it. When you set up a join on a List, the code that constructs our join metadata will query MySQL to find out the PK of that "table" you are joining to is. Which, if you are joining to a view, won't give us the answer. So if we don't get the answer, we try looking up that table name in our #__fabrik_lists metadata table, to see if we can get it that way. If you have added that view as a List, and designated some element as the PK, we have our answer. As a Hail Mary, we assume that the PK will be called 'id' and try that.

Obviously this also means you have to construct your view's SELECT to include something unique and numeric you can designate as the PK.

I think you also have to edit and save the List that uses that table as the join, after creating the other List, to force our code to re-construct the metadata, looking up the PK again.

-- hugh