1. Fabrik 3.9 has been released. If you have updated Joomla to 3.9, this is a required update.
    Dismiss Notice

Problem with Databasejoin [SOLVED]

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

  1. nofc_fro

    nofc_fro 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: Community
    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 Member

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

    cheesegrits Support Gopher Staff Member

    Level: Community
    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 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: Community
    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 Member

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

    troester Well-Known Member Staff Member

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

    cheesegrits Support Gopher Staff Member

    Level: Community
    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 Member

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

Share This Page