2. PHPRunner General questions
  3. how to simple search when joined tables and search terms are in child table
This topic is locked

how to simple search when joined tables and search terms are in child table

 		5/24/2022 9:46:30 PM
PHPRunner General questions
W
 wfcentral author

Example:
Table1 (owners) has fields id, name, email, phone, pets
Table2 (pets) has fields id, pet (values are cat, dog, bunny)

Owner can edit their record and select multiple pets. Field owners.pets would then have a value like 1,2 (for cat and dog)
Because I have the owner.pet field set as a multi select everything looks fine on the list page. You see cat, dog for this owner.

The problem is that using the simple search to put in "cat" does not get any results. Because the field pets contains 1,2 not the word cat.

I tried creating a JOIN but it is only display the first word in the set "cat" so search doesn't work if they do a simple search for any other pet.

How do you make it so simple search will work for these fields that display values based on database lookup dropdowns on their edit pages?
Sergey Kornilov admin 5/25/2022

I guess "Search Master and Details tables together" article in the manual is what you looking for.
W
 		wfcentral author 5/25/2022

I was hopeful that was it... however, the scenario in your response only applies if my data was like this...

user
id=1

pets
id=1 user.id=1 name=cat
id=2 user.id=1 name=dog

The help page you sent me to looks through the detail table (pets) to find the id of the user.

In my situation I have data like this

user
id = 1, pets = 1,2

pets
id=1, name=cat
id=2, name= dog

When I create the dropdown for user I tell it to lookup table pets to get the value for the dropdown. Because of this when you see the data on the list page it shows cat,dog instead of 1,2 even though the data in the field is "1,2"

However, when you search for "cat" it does not find it because the word "cat" does not exist in user table.

If I join user table with pets table
JOIN pets ON user.pets = pets.id
then I only see "cat" on my list page