Simple Question from Newbie: Need List of Joomla IDs with related table's Field value

[kknuth]

Totally new to Fabrik and trying to make things "click" in my head.

I want to create a List that is pre-populated with the following fields:
1. Joomla User ID
2. User Name
3. JomSocial custom profile field value (select value from jml_community_fields_values where user_id = valuefrom#1 and field_id="18")

The end result would be just a report. No editing of data. Just a tabular display of the data.

I just don't know where to start. Do I need to create a View first in MySQL?

 

[cheesegrits]

Technically, you could build it in Fabrik without a MySQL view, by:

1) Creating a List on top of the main #__users (making very sure you set "Alter Existing field types" to NO).

2) Creating a NON repeated table join on that list to the JomSocial table, a LEFT join, between #__users.id and jml_community_fields_values.user_id.

3) Creating a pre-filter on the List for field_id=18.

4) Unpublish all the elements you don't need to show (except the PK elements for those two tables, and the ones used in the join).

5) Make sure you set the create, edit and delete rows ACL's in the List settings to Nobody.

But an equally valid approach would be to set up a View in MySQL, something like :

CREATE VIEW your_view AS SELECT u.id, u.name, j.value FROM xxx_users as u LEFT JOIN jml_community_fields_values as j WHERE j.field_id = '18'

... and then creating your Fabrik List on the your_view "table". Again, you'd have to make sure you set Alter Existing Field Types to no, and shut down add / edit / delete access in the List ACL's.

Which approach you take rather depends on your specific requirements, size of your #__users table, your future plans, etc. I can't really tell you which way to go. Personally, if this was really the only thing I ever thought I wanted to do with this List, as a simple, read only display, I'd probably go for the MySQL view.

-- hugh

 

[kknuth]

Thank you so much for the two options. If later, I wanted to use fabrik to edit values in the jml_community_fields_values table, then would the DB view not be a good approach? Why?

 

[rob]

Hi

If later, I wanted to use fabrik to edit values in the jml_community_fields_values table, then would the DB view not be a good approach? Why?

because mySQL views are read only. You can't save records directly to the view.