Beginner question: Autocomplete with multiple tables?

Wappler General How To
#1

Hello,
I’m completely new to wappler and I have a difficulty combining different fields in an autocomplete text input.

So, for example I have 2 tables of ‘cars’ and ‘bikes’ (both have fields of ‘id’ and ‘brand’). How do I have both in the autocomplete so the user can get suggestions for cars and bikes by typing the brand?

#2

Not an expert here but I’d create a VIEW on your SQL tables and use that VIEW as the source of your autocomplete.

#3

In the optiontext part, you can write something like:
optiontext="id+' '+brand"

1 Like
#4

If what you mean is the views folder in the database manager, right-clicking on it does not give me an option to create any. maybe because I’m using docker and node.js?

#5

Not familiar with Docker and Node sorry, I use PHP and a MySQL database. I create my views in my DB.
@sid has a good solution.

#6

thanks for the suggestion! However I haven’t managed to get it to work because I actually have different field names and I’m not familiar with too many syntax yet.
let’s say instead of ‘brand’, I have ‘car_brand’ and ‘bike_brand’, how do I write it then?

#7

You can’t use autocomplete with 2 different database tables. You need to join them in the query builder OR create a view in your database and use it in the query builder.

1 Like
#8

Teodor’s right. I missed the part about the two fields being in separate tables.
Once you have a query that returns both fields, then my syntax would work.

#9

To explain it clearer, I want to replicate doctolib.de where the input box does not only return the autocomplete for health problems but also the doctors’ specialty and their names.

For the first one, I saw your guide on joining:

But I think it combines 2 entries into 1, instead of appending the list (hopefully I’m mistaken).

And for the second method, If what you mean is the views folder in database manager, I don’t see any option to create a ‘view’. just to collapse and expand all. maybe due to docker + node.js setup? I don’t really know

#10

You can’t create views in the database manager. You need to create them on database level. Or use joins, so join your two tables in the query manager and use the syntax Sid already posted.

#11

sorry I have been trying to no avail.

so after joining both tables, I now have fields of ‘id’, ‘car_brand’ and ‘bike_brand’.
On the autocomplete input, I set the data source to the join value but now I’m stuck on the text field and value field

#12

If you joined your two tables in the query builder and the result returns these columns then as per sid’s suggestion just add:

optiontext="car_brand + ' ' + bike_brand"
#13

But that resulted in both brands as a single option.
for example :
table cars:
id = [1,2,3]
car_brands = [‘BMW’,‘Ford’,‘Audi’]

table bikes:
id = [1,2,3]
bike_brands = [‘Honda’,‘Yamaha’,‘Kawasaki’]

with the method above, I got 3 options of:
BMW Honda
Ford Yamaha
Audi Kawasaki

My goal is:
BMW
Ford
Audi
Honda
Yamaha
Kawasaki

simply just combine both brands into 1 array. I hope my explanation is not too confusing

#14

That is not as simple as it might sound to you.
You will need a custom query here for what you need to achieve. For example using UNION will do what you need:

SELECT car_brands FROM table_cars
UNION ALL
SELECT bike_brands FROM table_bikes

make sure to replace the table columns and table names as they are in your database.

This will join the results like
Screenshot 2021-07-14 at 15.53.56

1 Like
#15

Thanks Teodor! exactly what I wanted.

well I guess there is a lot to learn since I just came from bubble.

1 Like