How to use field records to filter your db_join?

  • Views Views: 1,188
  • Last updated Last updated:
  • Imagine you have a big database table about tree-species. And you want to select a tree that is used for fruit (filter1) production and that is able to grow in your climate (filter2). Filter1 can be applied by "WHERE {thistable}.mainuse = 5 (=fruit)", but how can u filter AND {thistable}.annualrainfall = .......? Because every user has a different annual rainfall. I got the following idea to select the tree that does grow in your climate and has also fruit:

    Create 2 field Elements (farm_altitude & farm_annual_rainfall) and 1 db_join element (element3). And the following "Joins WHERE statament":

    WHERE {thistable}.mainuse = 5

    AND {thistable}.altitudemin < {currenttable___farm_altitude}
    AND {thistable}.altitudemax > {currenttable___farm_altitude}
    AND {thistable}.rainmin < {currenttable___farm_annual_rainfall}
    AND {thistable}.rainmax > {currenttable___farm_annual_rainfall}

    An optional second step is to disable access in "add more" to select element3 and to create a form plugin to redirect (on Add mode) the form of the last saved record (use {rowid} in your URL). So after saving your record you will be redirected to the "Edit mode" of the record u just added. And in this "edit mode you will be able to select the tree species in element3 . Copy the same redirect URL in your form plugin on edit and change ../form/... into .../details/.... After saving your record on edit you will be directed to the detail-view of your record.