1. The 3.5 release is now available. As usual, if your site is mission critical, you should test the J! 3.6 and Fabrik 3.5 releases on a sandbox first (clone your site using Akeeba).

Table child of a parent table in another database.

Discussion in 'Standard Support' started by Ian, Sep 25, 2009.

  1. Ian

    Ian New Member

    Level: Community
    I am getting an SQL error when I try and execute a TABLE that is a child of a table in another local database.

    In this example I am linking to Contacts from my SugarCRM database via a FABRIK connection.

    The problem is that the generated code does not qualify the foreign fields correctly, assuming there are in the local database. They aren't.

    Solution 1).. Local view to be used.
    I have a local view (TABLE linked into the Parent table), this works.
    I was expecting to be able to define my foreign key element back through my local Fabrik Table VIEW), but this table does not show during the definition of the foreign key on the child table, under the local database option. Forcing me to point it directly into the original Parent table (using the other CONNECTION).

    Under this option the view specific qualification should be used, as Fabrik and the user would consider that this is local link.

    Solution 2)... Point back to original Connected.Table
    Under the current set-up I would continue to CONNECT back to the original TABLE, but the generated SQL must better qualify the ELEMENT, using the CONNECTION.TABLE.ELEMENT structure.

    I know what everyone is thinking...

    Bad idea to connect 2 tables from 2 different databases. But the benefits of no duplicated data, is a far better solution. Especially if the data can be filtered etc. (which I believe is currently possible).

    Letting SugarCRM handle the contact functions for a Customer, and the FABRIK system handling other back-end data functions against that person.
    But the person is defined only once, 1 phone number, 1 address etc.

    It was a great feeling to get the VIEW into SugarCRM working, we are so close to getting this working, if this little bug can fixed.
  2. cheesegrits

    cheesegrits Support Gopher Staff Member

    Level: Supporter
    Yup, this is a known issue, due for tackling in 2.1.

    I'll raise this as a ticket on 2.1, as I'm not sure if we have an official ticket on it yet, it's just something Rob and I are aware of. Might as well make it official.

    -- hugh
  3. rob

    rob Administrator Staff Member

    Level: Community
    As an iterim solution, if you are comfortable with mySQL, is to replicate the table from the SugarDB to the Joomla database. Its been about 7 years since I looked at that so I can't really offer any practical help with it though :(
  4. cheesegrits

    cheesegrits Support Gopher Staff Member

    Level: Supporter
    In solution 1, I assume you mean you created a view on your main database, which consists of something like:

    create view sugar_view as select * from sugar_db.sugar_table

    ... so it basically just creates a 'mirror' of the sugar_table from sugar_db on your main database?

    -- hugh
  5. Ian

    Ian New Member

    Level: Community
    Explaination of Solution #1..

    Sorry, I was a little unclear...

    Although I did play with MySQL views to see if could get it working, what I was referring to on Solutionn #1, was a Fabrik Table, that simply pointed directly to the SugarCRM table, a Fabrik View definition. Which I did work, but I then couldn't define a working local Fabrik child table to access into the Fabrik parent view.

    If not clear let me know.
  6. cheesegrits

    cheesegrits Support Gopher Staff Member

    Level: Supporter
    Have you tried using the kind of MySQL view I described?

    This may even be one of the few instances of a MySQL view which is updateable.

    -- hugh
  7. Ian

    Ian New Member

    Level: Community
    Thanks Hugh for the tip.

    Mostly works.

    I have setup the MySQL view in the local database pointing to sugarcrm database:

    create view vsugarcrm_contacts_x2 as SELECT *,  CONCAT_WS(',',first_name,last_name) as full_name FROM sugarcrm.contacts c, sugarcrm.contacts_cstm x
    where c.id = x.id_c
    With this done, I have been able to setup the child connection aok in Fabrik, all working.

    When I setup a Fabrik table to view into the parent table, I get the following...

        getData: Unknown column 'vsugarcrm_contacts_x2.' in 'field list' SQL=SELECT DISTINCT `vsugarcrm_contacts_x2`.`id` AS `vsugarcrm_contacts_x2___id`, `vsugarcrm_contacts_x2`.`id` AS `vsugarcrm_contacts_x2___id_raw`, `vsugarcrm_contacts_x2`.`date_entered` AS `vsugarcrm_contacts_x2___date_entered`, `vsugarcrm_contacts_x2`.`date_entered` AS `vsugarcrm_contacts_x2___date_entered_raw`, `vsugarcrm_contacts_x2`.`date_modified` AS `vsugarcrm_contacts_x2___date_modified`, `vsugarcrm_contacts_x2`.`date_modified` AS `vsugarcrm_contacts_x2___date_modified_raw`, `vsugarcrm_contacts_x2`.`modified_user_id` AS `vsugarcrm_contacts_x2___modified_user_id`, `vsugarcrm_contacts_x2`.`modified_user_id` AS `vsugarcrm_contacts_x2___modified_user_id_raw`, `vsugarcrm_contacts_x2`.`created_by` AS `vsugarcrm_contacts_x2___created_by`, `vsugarcrm_contacts_x2`.`created_by` AS `vsugarcrm_contacts_x2___created_by_raw`, `vsugarcrm_contacts_x2`.`description` AS `vsugarcrm_contacts_x2___description`, `vsugarcrm_contacts_x2`.`description` AS `vsugarcrm_contacts_x2___description_raw`, `vsugarcrm_contacts_x2`.`deleted` AS `vsugarcrm_contacts_x2___deleted`, `vsugarcrm_contacts_x2`.`deleted` AS `vsugarcrm_contacts_x2___deleted_raw`, `vsugarcrm_contacts_x2`.`assigned_user_id` AS `vsugarcrm_contacts_x2___assigned_user_id`, `vsugarcrm_contacts_x2`.`assigned_user_id` AS `vsugarcrm_contacts_x2___assigned_user_id_raw`, `vsugarcrm_contacts_x2`.`salutation` AS `vsugarcrm_contacts_x2___salutation`, `vsugarcrm_contacts_x2`.`salutation` AS `vsugarcrm_contacts_x2___salutation_raw`, `vsugarcrm_contacts_x2`.`first_name` AS `vsugarcrm_contacts_x2___first_name`, `vsugarcrm_contacts_x2`.`first_name` AS `vsugarcrm_contacts_x2___first_name_raw`, `vsugarcrm_contacts_x2`.`last_name` AS `vsugarcrm_contacts_x2___last_name`, `vsugarcrm_contacts_x2`.`last_name` AS `vsugarcrm_contacts_x2___last_name_raw`, `vsugarcrm_contacts_x2`.`title` AS `vsugarcrm_contacts_x2___title`, `vsugarcrm_contacts_x2`.`title` AS `vsugarcrm_contacts_x2___title_raw`, `vsugarcrm_contacts_x2`.`department` AS `vsugarcrm_contacts_x2___department`, `vsugarcrm_contacts_x2`.`department` AS `vsugarcrm_contacts_x2___department_raw`, `vsugarcrm_contacts_x2`.`do_not_call` AS `vsugarcrm_contacts_x2___do_not_call`, `vsugarcrm_contacts_x2`.`do_not_call` AS `vsugarcrm_contacts_x2___do_not_call_raw`, `vsugarcrm_contacts_x2`.`phone_home` AS `vsugarcrm_contacts_x2___phone_home`, `vsugarcrm_contacts_x2`.`phone_home` AS `vsugarcrm_contacts_x2___phone_home_raw`, `vsugarcrm_contacts_x2`.`phone_mobile` AS `vsugarcrm_contacts_x2___phone_mobile`, `vsugarcrm_contacts_x2`.`phone_mobile` AS `vsugarcrm_contacts_x2___phone_mobile_raw`, `vsugarcrm_contacts_x2`.`phone_work` AS `vsugarcrm_contacts_x2___phone_work`, `vsugarcrm_contacts_x2`.`phone_work` AS `vsugarcrm_contacts_x2___phone_work_raw`, `vsugarcrm_contacts_x2`.`phone_other` AS `vsugarcrm_contacts_x2___phone_other`, `vsugarcrm_contacts_x2`.`phone_other` AS `vsugarcrm_contacts_x2___phone_other_raw`, `vsugarcrm_contacts_x2`.`phone_fax` AS `vsugarcrm_contacts_x2___phone_fax`, `vsugarcrm_contacts_x2`.`phone_fax` AS `vsugarcrm_contacts_x2___phone_fax_raw`, `vsugarcrm_contacts_x2`.`primary_address_street` AS `vsugarcrm_contacts_x2___primary_address_street`, `vsugarcrm_contacts_x2`.`primary_address_street` AS `vsugarcrm_contacts_x2___primary_address_street_raw`, `vsugarcrm_contacts_x2`.`primary_address_city` AS `vsugarcrm_contacts_x2___primary_address_city`, `vsugarcrm_contacts_x2`.`primary_address_city` AS `vsugarcrm_contacts_x2___primary_address_city_raw`, `vsugarcrm_contacts_x2`.`primary_address_state` AS `vsugarcrm_contacts_x2___primary_address_state`, `vsugarcrm_contacts_x2`.`primary_address_state` AS `vsugarcrm_contacts_x2___primary_address_state_raw`, `vsugarcrm_contacts_x2`.`primary_address_postalcode` AS `vsugarcrm_contacts_x2___primary_address_postalcode`, `vsugarcrm_contacts_x2`.`primary_address_postalcode` AS `vsugarcrm_contacts_x2___primary_address_postalcode_raw`, `vsugarcrm_contacts_x2`.`primary_address_country` AS `vsugarcrm_contacts_x2___primary_address_country`, `vsugarcrm_contacts_x2`.`primary_address_country` AS `vsugarcrm_contacts_x2___primary_address_country_raw`, `vsugarcrm_contacts_x2`.`alt_address_street` AS `vsugarcrm_contacts_x2___alt_address_street`, `vsugarcrm_contacts_x2`.`alt_address_street` AS `vsugarcrm_contacts_x2___alt_address_street_raw`, `vsugarcrm_contacts_x2`.`alt_address_city` AS `vsugarcrm_contacts_x2___alt_address_city`, `vsugarcrm_contacts_x2`.`alt_address_city` AS `vsugarcrm_contacts_x2___alt_address_city_raw`, `vsugarcrm_contacts_x2`.`alt_address_state` AS `vsugarcrm_contacts_x2___alt_address_state`, `vsugarcrm_contacts_x2`.`alt_address_state` AS `vsugarcrm_contacts_x2___alt_address_state_raw`, `vsugarcrm_contacts_x2`.`alt_address_postalcode` AS `vsugarcrm_contacts_x2___alt_address_postalcode`, `vsugarcrm_contacts_x2`.`alt_address_postalcode` AS `vsugarcrm_contacts_x2___alt_address_postalcode_raw`, `vsugarcrm_contacts_x2`.`alt_address_country` AS `vsugarcrm_contacts_x2___alt_address_country`, `vsugarcrm_contacts_x2`.`alt_address_country` AS `vsugarcrm_contacts_x2___alt_address_country_raw`, `vsugarcrm_contacts_x2`.`assistant` AS `vsugarcrm_contacts_x2___assistant`, `vsugarcrm_contacts_x2`.`assistant` AS `vsugarcrm_contacts_x2___assistant_raw`, `vsugarcrm_contacts_x2`.`assistant_phone` AS `vsugarcrm_contacts_x2___assistant_phone`, `vsugarcrm_contacts_x2`.`assistant_phone` AS `vsugarcrm_contacts_x2___assistant_phone_raw`, `vsugarcrm_contacts_x2`.`lead_source` AS `vsugarcrm_contacts_x2___lead_source`, `vsugarcrm_contacts_x2`.`lead_source` AS `vsugarcrm_contacts_x2___lead_source_raw`, `vsugarcrm_contacts_x2`.`reports_to_id` AS `vsugarcrm_contacts_x2___reports_to_id`, `vsugarcrm_contacts_x2`.`reports_to_id` AS `vsugarcrm_contacts_x2___reports_to_id_raw`, `vsugarcrm_contacts_x2`.`birthdate` AS `vsugarcrm_contacts_x2___birthdate`, `vsugarcrm_contacts_x2`.`birthdate` AS `vsugarcrm_contacts_x2___birthdate_raw`, `vsugarcrm_contacts_x2`.`portal_name` AS `vsugarcrm_contacts_x2___portal_name`, `vsugarcrm_contacts_x2`.`portal_name` AS `vsugarcrm_contacts_x2___portal_name_raw`, `vsugarcrm_contacts_x2`.`portal_active` AS `vsugarcrm_contacts_x2___portal_active`, `vsugarcrm_contacts_x2`.`portal_active` AS `vsugarcrm_contacts_x2___portal_active_raw`, `vsugarcrm_contacts_x2`.`portal_app` AS `vsugarcrm_contacts_x2___portal_app`, `vsugarcrm_contacts_x2`.`portal_app` AS `vsugarcrm_contacts_x2___portal_app_raw`, `vsugarcrm_contacts_x2`.`campaign_id` AS `vsugarcrm_contacts_x2___campaign_id`, `vsugarcrm_contacts_x2`.`campaign_id` AS `vsugarcrm_contacts_x2___campaign_id_raw`, `vsugarcrm_contacts_x2`.`id_c` AS `vsugarcrm_contacts_x2___id_c`, `vsugarcrm_contacts_x2`.`id_c` AS `vsugarcrm_contacts_x2___id_c_raw`, `vsugarcrm_contacts_x2`.`cto_main_role_c` AS `vsugarcrm_contacts_x2___cto_main_role_c`, `vsugarcrm_contacts_x2`.`cto_main_role_c` AS `vsugarcrm_contacts_x2___cto_main_role_c_raw`, `vsugarcrm_contacts_x2`.`full_name` AS `vsugarcrm_contacts_x2___full_name`, `vsugarcrm_contacts_x2`.`full_name` AS `vsugarcrm_contacts_x2___full_name_raw` , `vsugarcrm_contacts_x2`.`` AS `__pk_val` FROM `vsugarcrm_contacts_x2` LIMIT 0, 10                                                        
    It a little bit scary, but the problem is that last reference to ___pk_val, it does not have column name, just the quotes. :(

    Note that full_name is the last user column in the view.
  8. cheesegrits

    cheesegrits Support Gopher Staff Member

    Level: Supporter
    Did you set the 'Primary Key' on your Fabrik table settings? Even though you are sitting on top of a view, you still need to tell Fabrik what the PK is, and that it is auto-inc (in case it turns out that we can use this as an updateable view, i.e. add / edit rows).

    -- hugh
  9. Ian

    Ian New Member

    Level: Community
    Hugh, You still the man.

    Have set the "id" as the physical key on the Fabrik TABLE definition
    Note: It is a VARCHAR column, Not a standard Fabrik format. I have set the Auto Increment to NO.

    I am thinking any updates would be done in the native SugarCRM application.

    I should now be able to view, join, create children etc. ;D

    Looking good.

    Although this has had a little backward and forward, I think it would be of great interest to others also.
  10. cheesegrits

    cheesegrits Support Gopher Staff Member

    Level: Supporter
    Sounds good. FYI, I also always set 'alter existing field types' to No on tables which I build on top of MySQL Views, which should prevent Fabrik from (say) attempting to change a PK from varchar to INT.

    One other thing, I really have no clue of the performance implications of using a view as a kind of mirror for a foreign table. My experience so far is that it doesn't seem to hurt performance, even on fairly large tables (I use this trick in a project I'm working on, with a view to a vBulletin 'post' table, with a couple of million records). But I may just not have tried anything which is going to tickle potential performance issues.

    -- hugh
    1 person likes this.

Share This Page