Custom Advanced Lookup SQL Query - help needed & happy to pay!

The recommended method of customizing your AppGini-generated application is through hooks. But sometimes you might need to add functionality not accessible through hooks. You can discuss this here.
Post Reply
peebee
AppGini Super Hero
AppGini Super Hero
Posts: 352
Joined: 2013-03-21 04:37

Custom Advanced Lookup SQL Query - help needed & happy to pay!

Post by peebee » 2017-08-11 01:12

I'm looking for a custom SQL query to replace an existing Advanced Lookup SQL query which is not specific enough for my purposes. So far, everything I've tried is failing? I'm happy to pay $'s for a solution.

There are 3 x tables concerned: "jobs", "reagents" and "regions" (although for this exercise, regions isn't so important).

The existing custom SQL query taken from the ajax_combo.php below is working as it is - but I want to refine the results even further. This produces a select2 dropdown in the Jobs table detail view where I can select Real Estate Agents that are active. All good so far....:

Code: Select all

'realestate1' => array(
  'parent_table' => 'reagents',
  'parent_pk_field' => 'id',
  'parent_caption' => '`reagents`.`reagent_tname`',
  'parent_from' => '`reagents` LEFT JOIN `regions` as regions1 ON `regions1`.`id`=`reagents`.`reagent_region` ',
  'filterers' => array(),
  'custom_query' => 'SELECT `reagents`.`id`, `reagents`.`reagent_tname` FROM `reagents` LEFT JOIN `regions` as regions1 ON 
  `regions1`.`id`=`reagents`.`reagent_region` WHERE `reagent_active` =1 ORDER BY 2',
  'inherit_permissions' => false,
  'list_type' => 0,
  'not_null' => false
),
In the "reagents" table, there is also a field titled "reagent_pcregions". That field stores a multi-select of 4 x digit postcodes. These postcode values are stored in the db separated by a comma and space (eg: 2345, 2346, 2347, 2348). There may be only 1 or many postcodes stored in the field reagents.reagent_pcregions

In the "jobs" table, there is a field titled "postcode". The field "jobs.postcode" stores one single 4 x digit postcode

THE QUERY I am after is this original query from the code above:

'SELECT `reagents`.`id`, `reagents`.`reagent_tname` FROM `reagents` LEFT JOIN `regions` as regions1 ON `regions1`.`id`=`reagents`.`reagent_region` WHERE `reagent_active` =1 ORDER BY 2'

PLUS in the WHERE clause: I want to show in the jobs table lookup dropdown ONLY `reagents`.`reagent_tname` WHERE the first 3 digits of the `jobs`.`postcode` of the selected job is contained within the multiselect `reagents`.`reagent_pcregions` field string - also only in the first 3 digits.

Simplified: the lookup dropdown in the Jobs table to only show Real Estate Agents who service the postcode of the selected Job - based on the first 3 digits of the 4 digit postcode. Real Estate Agents have the postcodes they service stored in the multiselect reagents.reagent_pcregions field.

Make sense?

I have tried adding the "FIND_IN_SET(left()" function to the WHERE clause as detailed here: http://www.w3resource.com/mysql/string- ... nction.php but I just can't get it to work?

I've spent enough time/effort to finally acknowledge I'm out of my depth. I'm more than happy to pay a reasonable cost for somebody's expertise if they are interested in helping. Thanks.

Post Reply