Custom Advanced Lookup SQL Query - help needed & happy to pay!
Posted: 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....:
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.
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 "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.