[bug] fabrik expects joined column to have same name as databasejoin element SOLVED

Status
Not open for further replies.
batonac

batonac

Member
I have two tables that are joined together. One is called 'city' and the other 'standards_body'. The standards_body table has a databasejoin field called called 'city_id' that references the 'city'.'id' field through a databasejoin element. All configures properly until I go to access the list data, where I get this error:
Code: 
Unknown column 'city.city_id' in 'field list' SQL=SELECT SQL_CALC_FOUND_ROWS DISTINCT `standards_body`.`id` AS `standards_body___id`,
`standards_body`.`id` AS `standards_body___id_raw`,
`standards_body`.`name` AS `standards_body___name`,
`standards_body`.`name` AS `standards_body___name_raw`,
`standards_body`.`code` AS `standards_body___code`,
`standards_body`.`code` AS `standards_body___code_raw`,
`standards_body`.`standard_title` AS `standards_body___standard_title`,
`standards_body`.`standard_title` AS `standards_body___standard_title_raw`,
`standards_body`.`city_id` AS `standards_body___city_id_raw`,
`city`.`city_id` AS `standards_body___city_id`,
`standards_body`.`state_id` AS `standards_body___state_id_raw`,
`state`.`state_id` AS `standards_body___state_id`,
`standards_body`.`country_id` AS `standards_body___country_id_raw`,
`country`.`country_id` AS `standards_body___country_id`,
`standards_body`.`id` AS slug
, `standards_body`.`id` AS `__pk_val`
FROM `standards_body`
LEFT JOIN `city` AS `city` ON `city`.`id` = `standards_body`.`city_id`
LEFT JOIN `state` AS `state` ON `state`.`id` = `standards_body`.`state_id`
LEFT JOIN `country` AS `country` ON `country`.`id` = `standards_body`.`country_id` LIMIT 0, 10
The problem is that 'city' doesn't have a column 'city_id', just 'id'. Only the referencing column 'standards_body'.'city_id' has that name...
 
This randomly and weirdly started working?!? Now I'm really confused :confused:

I know that I did get the error, and that it wasn't the first time I've run into this. It may be tied to this log error?:
Code: 
2013/07/15 17:26:34 [error] 789#0: *4165 FastCGI sent in stderr: "PHP message: PHP Notice:  Undefined variable: othertables in /srv/www/fabrik_test/administrator/components/com_fabrik/models/element.php on line 765
PHP message: PHP Warning:  array_merge(): Argument #2 is not an array in /srv/www/fabrik_test/administrator/components/com_fabrik/models/element.php on line 765" while reading response header from upstream, client: 174.49.214.254, server: ft.3cordinc.com, request: "POST /administrator/index.php?option=com_fabrik&view=element&layout=edit&filter_groupId=3 HTTP/1.1", upstream: "fastcgi://unix:/var/run/php5-fpm.sock:", host: "ft.3cordinc.com", referrer: "http://ft.3cordinc.com/administrator/index.php?option=com_fabrik&view=element&layout=edit&filter_groupId=3"
 
Further testing reveals that this error seems to occurs every time I setup a new databasejoin field. When I return the the control panel and then to the fabrik list. The error no longer appears. Strange...
 
hi
I've fixed that error related to $othertables - could you update and see if that resolves the main issue?

Thanks
Rob
 
Not fixed for me on on latest update. FYI, I'm using the administrator 'view data' link to access the data on the tables, which is where I confront the error.
 
I'm not able to replicate. I know a couple of days ago we had some issues where saving joins would create spurious records in #__fabrik_joins. Could you check in that table via phpmyadmin and see if there are any odd looking records referencing either the list id or any element id that occurs in the list? If there are try removing them?
 
ok i can replicate it! phew! - Its lunch time now but I'll crack on with trying to figure out what is going on this afternoon
 
could you try updating from github, then trashing (and clearing from the trash) the db join element and any child elements that might have been created, and re-try adding? I definitely found something 'funky' although I'm not sure its exactly what you were experiencing.
 
Glad you were able to replicate! I'm sure you've discovered by now, but just for the record, I don't believe the #_fabrik_joins table is at fault. I purged all fabrik data and re-added just the tables in question, and the #_fabrik_joins table seems to be populated correctly:
Code: 
????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
? id ? list_id ? element_id ? join_from_table ? table_join ? table_key ? table_join_key ? join_type ? group_id ? params                                                    ?
????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
? 27 ? 0       ? 70         ?                 ? city       ? city_id   ? id             ? left      ? 21       ? {"join-label":"name","type":"element","pk":"`city`.`id`"} ?
????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
Meanwhile the bug still persists...
 
rob said:
could you try updating from github, then trashing (and clearing from the trash) the db join element and any child elements that might have been created, and re-try adding? I definitely found something 'funky' although I'm not sure its exactly what you were experiencing.
Click to expand...

Oops, not sure how I missed this post...

I've done even better: I just destroyed my Joomla install files and database, re-installed Joomla, Fabrik from Github, loaded my custom tables from MySQL workbench, etc. I again loaded just the standards_body table and linked the city_id element to the city table. The bug still persists. :(
 
I think the most straightforward way to replicate this bug is this:
  1. create two new lists with new tables: 'parent' & 'child'
  2. add new databasejoin element to 'child' called 'parent_id', linked to 'parent' table, 'id' value
  3. return to lists, select 'View data' for 'child' list
  4. Behold the following error:
Code: 
Unknown column 'parent.parent_id' in 'field list' SQL=SELECT SQL_CALC_FOUND_ROWS DISTINCT `child`.`id` AS `child___id`,
`child`.`id` AS `child___id_raw`,
`child`.`date_time` AS `child___date_time`,
`child`.`date_time` AS `child___date_time_raw`,
`child`.`parent_id` AS `child___parent_id_raw`,
`parent`.`parent_id` AS `child___parent_id`,
`child`.`id` AS slug
, `child`.`id` AS `__pk_val`
FROM `child`
LEFT JOIN `parent` AS `parent` ON `parent`.`id` = `child`.`parent_id` LIMIT 0, 10
 
I've seen a few of those recently, but if it's the same one's I'm seeing, they go away if you immediately reload the list page ... and I'm pretty sure I know why that's happening.

I'll follow your steps, and see what I see ...

-- hugh
 
Yeah it does go away on refresh which makes it a low priority bug. The other bug I have open is the real show-stopper for me. At first I thought they could be related but further digging appears they are not...
 
OK, the "first time" bug is now fixed in github.

Which is the "other bug"? I can have a swing at that as well ...

-- hugh
 
Status
Not open for further replies.

Useful links

  • Create a Fabrik Account (Downloads)
  • Create a Forum Account
  • Fabrik Wiki
  • Fabrik 4 Changelog /Install /Upgrade
    • We are in need of some funding.
    More details.

    Thank you.

    Members online

    Total: 203 (members: 1, guests: 202)
    Back
    Top