1. Fabrik 3.7 is now available. This version contains critical security updates, please update as soon as possible. Please remember to backup your site before updating, we also recommend testing your updates on development sites if possible.
  2. We had to restore the site around midday (CST) on Wed 7/26 from a backup approximately 8 hours old. So a few posts in the forums were lost. You may want to check to see if you need to re-post anything.
  3. The release of Fabrik for J! 3.8 is not yet ready. Do not update your sites to J! 3.8 until this notice is removed.

Databasejoin filtered with user id

Discussion in 'Standard Support' started by Teide, Sep 12, 2017.

  1. Teide

    Teide Member

    Level: Standard
    Hi there,

    Been trying to sort this out for a while with no luck...

    Will explain what I have:

    List 1 called parent with info about parent that is imported. it's has a member_id which more that one parent can have.
    List 2 called childrens in the same situation as parents list, with a member_id which more than a child can have.
    List 3 called user which is populated by parents and used juser to create a joomla user and have a member_id. once a parent registered the member_id is checked.

    List 1 and 2 has a join to list 3 using the member_id in order to get the user_id so list 1 and 2 are prefiltered with the user_id using {$my->id} so parents can only see their related info.

    Up to know everything works perfect.

    A new list 4 called activities is created also imported by csv with info related to the activities childs can do.
    Another list 5 is needed where parents can sign their childs in the activities.

    In this form 5 a database join element was created so the parent can only choose the children that member_id is the same as the logged user. This is where the problem is as all parents can choose all childrens.

    Any ideas?

    Thanks
     
  2. cheesegrits

    cheesegrits Support Gopher Staff Member

    Level: Community
    Add a "WHERE" filter to the join, that does something like ...

    WHERE {thistable}.member_id = (SELECT member_id FROM list3 WHERE userid = '{$my->id}')

    Obviously change table and field names as appropriate (but leave {thistable} as is).

    -- hugh
     
  3. Teide

    Teide Member

    Level: Standard
    Hi Hugh,

    Thanks for this and for fabrik.

    Works perfect!
     
  4. Teide

    Teide Member

    Level: Standard
    Hi,

    Another query if anybody can help.

    List 4 called activities has a checkbox element called age which states what children can participate.
    List 2 also has the age of the child as calc element as what is imported is the date of birth.

    In the form 5 after selecting the child the parent has to select an activity. What i am trying to achieve is using a database join element only show the activities that matches with the kids age. Form 5 has an element field that using the autofill copy the age of the childs from list 2.

    Is this posible or should I try other options?

    Thanks
     
  5. cheesegrits

    cheesegrits Support Gopher Staff Member

    Level: Community
    You should be able to achieve that if you use a WHERE clause with a placeholder for the age element, and enable AJAX updating. With AJAX update enabled, we then watch any elements you have used as placeholders in the WHERE clause, and do an AJAX call to rebuild the join's options whenever any of those elements are changed.

    So theoretically, when the autofill happens that changes the age element, that should trigger the join element to rebuild itself, using the new value of the age in your WHERE clause.

    -- hugh
     
  6. Teide

    Teide Member

    Level: Standard
    Hi Hugh,

    It works when the age in the activity list is set us a field with only one age but not if the element is a is checkbox as each activity is available for more than one age. I would like that depending on the child age, only activities that includes that age becomes available for him to choose in the database join field.

    For example, let's say activity football is for children ages 4-5-6-7-8-9-10. So a child ages 7 can choose to play football but a child age 3 cannot.

    Thanks
     
  7. Teide

    Teide Member

    Level: Standard
    Hi,

    Worked a way around: created a field element "from age" and another field "up to age" and the used AND and worked fine.

    Thanks
     

Share This Page