Cascading joins work fine for 3 tables, throws an error when I add a 4th

[lornevs]

I am (re)building a Collectors Show listing application using 4 tables, each in a one-to-many relationship. There's promoter_info, show_info (basically the names of shows they promote), venue_info and finally event_info has details of specific events. All the lists are connected with databasejoin elements. To show all the info together I joined them cascading backwards from event_info so the most important info (to the viewer) shows first. (I did it all in the events_info list, maybe that's what I did wrong?). I used a LEFT JOIN from events to venues, then from venues to shows (all repeatable) and it works fine but when I add a LEFT JOIN from shows to promoters and try to look at the data I get the error "Unknown column 'vib3_fabrik_show_info.promoter_id' in 'on clause'". It then goes on to list the distinct rows it did find which I won't list here as I think the issue is with the joins, (in my original post on the Community forum I stated I used an INNER JOIN, but that was only because the LEFT JOIN didn't work, both get the same result). The join part of the error shows as:
FROM `vib3_fabrik_event_info`
LEFT JOIN `vib3_fabrik_venue_info` AS `vib3_fabrik_venue_info` ON `vib3_fabrik_venue_info`.`id` = `vib3_fabrik_event_info`.`venue_id`
LEFT JOIN `vib3_fabrik_venue_info` AS `vib3_fabrik_venue_info_0` ON `vib3_fabrik_venue_info_0`.`id` = `vib3_fabrik_event_info`.`venue_id`
LEFT JOIN `vib3_fabrik_show_info` AS `vib3_fabrik_show_info_0` ON `vib3_fabrik_show_info_0`.`id` = `vib3_fabrik_venue_info`.`show_id`
LEFT JOIN `vib3_fabrik_promoter_info` AS `vib3_fabrik_promoter_info_0` ON `vib3_fabrik_promoter_info_0`.`id` = `vib3_fabrik_show_info`.`promoter_id`
LEFT JOIN `vib3_fabrik_show_info` AS `vib3_fabrik_show_info` ON `vib3_fabrik_show_info`.`id` = `vib3_fabrik_venue_info_0`.`show_id`
LEFT JOIN `vib3_fabrik_promoter_info` AS `vib3_fabrik_promoter_info` ON `vib3_fabrik_promoter_info`.`id` = `vib3_fabrik_show_info_0`.`promoter_id`
LEFT JOIN `vib3_users` AS `vib3_users` ON `vib3_users`.`id` = `vib3_fabrik_promoter_info_0`.`user`
WHERE ( vib3_fabrik_event_info.event_show_until_date >= '2014-12-31 00:00:00' )
ORDER BY `vib3_fabrik_event_info`.`event_start_date` ASC LIMIT 0, 100
This is a very small database, in the past I put the information in myself using a single table. I want the promoters to input their own show dates so I need a form that is user-friendly and does not require them to input the same info over and over again. I know using one table is bad design so I normalized the data I already have and split it into four tables, seemed like a good idea at the time.
I am using Fabrik 3.1rc2 on a J!3.2.2 site (migrated from 2.5 - don't let them tell you it's easy). The page can be seen at http://www.vibrations.ca/vibe3/record-shows/2014-shows. It's a test site, I've left the four tables joined so you can see the error. With three tables joined it works fine but you can't see the promoter contact info.
Can anybody help?

 

[rob]

hi this does look like a bug - could you supply an ftp account and if possible access to phpmyadmin in http://fabrikar.com/you/my-sites/form/14/241 so that I can debug it.

 

[lornevs]

Friendly bump. I did as you asked last Tuesday (I let you know by replying to your email - maybe you missed it) so just wondering if there's any progress. I need to get on with my project, if you can't fix it I'll figure out a work around.

 

[rob]

hi sorry I missed the email. I've taken a look, and haven't got anywhere with it just yet, I'll take another look tomorrow morning
-Rob

 

[rob]

Hi I'm rather confused about the logic of what you are trying to do, you have both a list join to the 'vib3_fabrik_show_info' list and a database join element to the same list. Do these two things represent different relationships, or are they expressing the same thing? If its the same thing then generally you have either a database join element OR a joined group.
If I unpublish the element 'vib3_fabrik_venue_info___show_id' (id 157) then things work.

Also I couldn't access phpmyadmin - so its still a bit hard to work out what the underlying table structure is as well, perhaps if you could double check that I can have another look.

 

[lornevs]

Thanks Rob - The logic is simply my lack of knowledge. I joined all the tables using a databasejoin element but I couldn't see how to make a list that included fields from all the tables, so I made the list joins, which seems to work at first. They do represent the same relationship so I'll unpublish the elements.
Sorry about the phpMyAdmin, I forgot to put the database prefix in the user name. That's been fixed now.

 

[rob]

Hi - Ok thats good news then, there are some real edge cases where you do want to do list joins and element joins and I was worried that that was one of those cases beyond even the edge cases that the code plans for. So just to be sure, unpublishing the element 'vib3_fabrik_venue_info___show_id' resolves this issue for you?

 

[lornevs]

Taking that one element out didn't fix it for me but unpublishing all the database join elements seemed to do the trick. Let's say this is now resolved. Thank you for all your help.