Adding #__users to list join and publishing name field causes 500

[bascherz]

I have a list that has 3 inner joins:

1. #__comprofiler to #__users on user_id=id
2. #__comprofiler to #__zipcodes on cb_zip=zip
3. #__zipcodes to #__district_names on district=id

In all three cases above the column on the right is the primary key for that table.

I need the name and email element contents from #__users, which is the only reason that table is joined. However, if I publish either of those elements, I get this fun and familiar error:

500 Fabrik has generated an incorrect query for the list Paused Volunteers: <br /><br /><pre>Unknown column '#__users.name' in 'field list'

If I unpublish all #__users elements, the list works, but then I don't have name or email in the list or in the form, and I need them. What causes this and is there some alternative method I could use if this is a problematic pair of tables?

Oh by the way...in the complete error message (which is very long), all the other element names have the full table name, not the #__ prefix and use the Fabrik placeholder syntax (in the error message). Also, if I publish both name and email, the error is for name. Then if I unpublish name, the error message is for email. I have to unpublish them both (defeating the purpose of joining the #__users table) in order for the list to work.

 

[troester]

How/where did you join? In the list "Joins" you can't select #__users but the table with real prefix.

 

[bascherz]

Hey troester, the Joins are in the List definition under Data/Joins. There, I just select the table and elements. I don't type them in. So I don't know why this happens.

 

[troester]

As far as I can see Fabrik is storing #__users for user elements only.

Did you set name and email to be user elements?
If you have already a list join to love_users these elements are simple fields (a user element is a special databasejoin element, so it would be an additional join).

 

[lousyfool]

This may help additionally:
On a site I'm having numerous Fabrik lists with J users and CB joined. However, I'm not basing them on #__comprofiler like you do, but on #__users. So, creating a Fabrik list for the #__users table, then joining it with #__comprofiler like so:


IOW, make #__users the "mother" of your joins. No issues at all.

 

[bascherz]

Hmm. Certainly easy enough to do. I'll give it a shot. Thanks!

SURE ENOUGH! That did the trick. Had to completely redo the list, but it works now.

Thanks, lousyfool!

 

[troester]

So what did the trick:
Changing the element type?
Or @lousyfool 's rearrangement of list joins?

BTW: You can close solved threads by unchecking "open" in Thread Tools.

 

[bascherz]

It was lousyfool's recommendation to base the list primarily on #__users. I did have to start from scratch with that part of the list, but it did the job.