1. We suggest you do NOT update to Joomla 3.8.10 until we can resolve an issue it causes with caching in Fabrik. If you do install it, you'll need to disable Joomla's "System Cache" in the global System settings.
  2. Apologies for the recent server outage, a planned migration by our host provider to a new location turned into a bit of a nightmare.

Problem with Databasejoin [SOLVED]

Discussion in 'Community' started by nofc_fro, Jul 11, 2018.

  1. nofc_fro

    nofc_fro New Member

    Level: Professional
    Hi,
    I have this situation:
    Two tables called "aaa" and "bbb".
    The "aaa" table has two fields: id, name
    The "bbb" table has three fields: id, aaa_id, name

    The "aaa" table has this record:
    100, xxx

    The "bbb" table has these records:
    201.100, white
    202.100,sport
    203.100, tv
    204.100, ball

    If I create an Element with Plug-in "Databasejoin" I go to the tabs "Data", I select the table "aaa", Value "id" and "Concat label"> "aaa.id, '', bbb.aaa_id" I get this error "An error has occurred."

    How can I solve it?
     
  2. cheesegrits

    cheesegrits Support Gopher Staff Member

    Level: Professional
    You can't reference table bbb in the join to table aaa. It just doesn't exist in that context. Also, as per the tooltip on the CONCAT label, avoid using the actual table name (aaa), use {thistable} instead.

    You could try using a placeholder for the b value, like ...

    {thistable}.id, ' {bbb___aaa_id}'

    ... but this probably won't work as expected in all contexts.

    -- hugh
     
  3. nofc_fro

    nofc_fro New Member

    Level: Professional
    {thistable}.id, ' {bbb___aaa_id}' or {thistable}.id, ' {bbb___aaa.id}' ?
     
  4. cheesegrits

    cheesegrits Support Gopher Staff Member

    Level: Professional
    The first one.

    But I really don't think it's going to work as you'd hope.

    The label of a join gets built in two contexts:

    Form context. When building a form for display, we run a single query to get all the possible values of the join, something like "SELECT id AS value, something AS label FROM aaa" . So there is no table 'bbb' in that query, which is why you can't use bbb.aaa_id, it just doesn't exist in the query. But you might be able to use the element placeholder, {bbb___aaa_id}. We do placeholder replacement on the CONCAT string, using current values of your form's elements, so it should replace {bbb___aaa_id} with the current value for that form. But (for instance) when creating a new form (rather than editing), it won't have a value when you load it. Also, if aaa_id is selectable on the form, and you change it once the form is loaded, the join dropdown won't "see" that change.

    List context ... I won't go into detail, but I doubt the placeholder would work for that, although it might.

    -- hugh
     
  5. nofc_fro

    nofc_fro New Member

    Level: Professional
    Excuse me but maybe I have to start from the beginning:

    CASE 1: Make a concatenation between two elements of the same table.
    I have the table "aaa" with these elements: “id”, "name", "surname”.
    I want to create another element -called "full name”- that concatenates the two previous elements.
    I create this element of type “databasejoin” and in the tabs "Data" I put:

    Render as: "Drop-down"
    Connection: "Database"
    Table: "aaa"
    Value: "id";
    Label: "Please select"
    Or concat label: ?

    I try:
    aaa___name, ' ', aaa___surname
    or
    name, ' ', surname

    http://fabrikar.com/forums/index.php?wiki/database-join-element/

    But not work
     
  6. troester

    troester Well-Known Member Staff Member

    Level: Standard
    name, ' ', surname
    should do.

    If you get an error (like " ...ambiguous...") use
    {thistable}.name, ' ', {thistable}.surname

    (litterally {thistable})

    BTW: as a Pro member you should post in the Pro forum to get Pro support.
     
  7. nofc_fro

    nofc_fro New Member

    Level: Professional
    I can't -> (You have insufficient privileges to post here.)
     
  8. troester

    troester Well-Known Member Staff Member

    Level: Standard
    Did you try to log out, clear browser cache, log in again?
     
  9. cheesegrits

    cheesegrits Support Gopher Staff Member

    Level: Professional
    Try again, I kicked XenForo in the butt.

    Yes - you can concat fields form the same table ...

    {thistable}.name, ' ', {thistable}.surname

    Your original question was trying to concat fields from the joined table with fields from the form's table, which you can't do.

    -- hugh
     
  10. nofc_fro

    nofc_fro New Member

    Level: Professional
    Ok thanks, I wrote in the other forum yesterday :)
     

Share This Page