Correct DB structure to Return results list that matched multiple tables

[mgaussie]

Hey all,

One part of our project is a dynamic directory that includes specialists, the industry, skills, location (city, state, country) and services.

I had in my mind how I’d make this work, but when I start trying to display results with queries that join multiple tables (e.g. get the specialist, plus their expertise, plus their service, industry and location) so I can display it all in one column - the specialists are just duplicating multiple times so I’m definitely doing something wrong.

This is the general setup of the page:

Query variables taken from the url on AC: service, industry, function etc. passed through to the SC.

I then have a SC with a query that looks at all join (reference tables) that have the various ids from the queries taken from the url- all have specialist ids in each table and this is what I link the tables with and use the conditions to only return results that match each url parameter. This isn’t working for me - I’m super basic with db’s.

An example of a join table (and I have one for each query type) is:

Service id - ref to a table that lists all services offered and the names etc
Specialist if - ref to the table of specialists
Id - join table row id

The specialist might list multiple services which is why I use join tables.

So I’m trying to display the list of specialists that match each url parameter query on the page via a paginated query.

The url could contain just one parameter (eg service Id which displays all specialists with an id listed in the service join table) or up to 6 (with their specialist id required to match all 6).

I’m struggling getting my head around this due to my lack of experience with databases so I’d appreciate any guidance here!

[mgaussie]

In theory from what I can gleam online it should be a simple inner join with each table, all on the ‘specialist ID’ - but it’s just not working, and I can’t tell if it’s being done wrong on my end - or if it’s a bug of some kind.

[mebeingken]

As a self proclaimed novice, you might find it easier to do more of this in Wappler and less in your sql statement.

I find that usually people are actually looking to:

Do a query to get the base results from the core table.
Perform a repeat on the results, and within the repeat perform one or more queries to get the related table data. The response ends up with a nice structure where each item in the base row has one or more arrays as children.

Usually people are worried about performance to which I say, fix that after you get it working, and IF it actually is a problem. Sometimes it is better to make progress than make perfect.

[mgaussie]

Hey Ken, thanks for the reply on this, too!

I am doing all of this in Wappler, trying to use the Database Query builder but just getting no where with it.

Even on simple queries I seem to just be getting duplication.

E.g. I have a table that has a list of ‘services’ that never changes, e.g. let’s call it simply ‘services_table’

id
servicename
servicename_slug

Then I have a table that I use to join a specialists multiple services, let’s call it ‘join_specialist_services’

id
service_id (referencing the above table)
specialist_id (referencing the specialist table)
service_slug (for using in links).

What I’m doing in the query builder:

First table is the ‘join_specialist_services’ table

Then I add the ‘services’ table with an inner join on ID field in ‘services’ table and ‘SERVICE_ID’ field.

This should work as the service_id and id will be the same and linked together in the db structure.

All it does it return duplicates instead of the single items.

I’m not sure I follow in terms of a query then perform a repeat on that. I’ll play with this to see if I understand

[mebeingken]

This is just my point – I think. You are getting dupes because you are joining a one to many table – one row for every unique combination. In other words, it is duplicating your base table in order to show you the joined results.

I’ll build a quick sample…

[mgaussie]

Would something like this work:

Query 1: services join table - only one table multi query
return all specialist id’s that exist in that table

Query 2: industry join table - only one table multi query
return all specialist id’s that exist in that table

Query 3: specialist table - only one table multi query
return all specialists and all their info that can then be displayed in a repeating element
condition: specialist ID must exist IN query 1 AND IN query 2 ?

[mgaussie]

Thank you - I really appreciate it

[mebeingken]

See if this sample helps:

qry_menu_items gets the base table

menu_items is a repeat of qry_menu_items and is the actual output

meal_types and tags are queries that use the menu_item_id (the key’d value) to lookup child data

And the results look like this:

[mgaussie]

Thank you Ken.

So in the worst case for me (most complex!) I have to define a specialist that matches all of the following:

services
industries
functions
locations (City, state, country).

The specialist can have multiple of each. So in theory there could be a specialist like this:

Specialist
Services x 4
Industries x 4
Functionx x 4
City x 4
State x 4
Country x 1

So based on your example, would the base table always be the specialist, to get the ID’s (which can be 10’s of thousands of specialists) as the initial query.
Then run multiple queries on all the joining tables such as
industry
function
service
locations

to find the matching results for each ‘specialist id’ across all the other tables in the repeat?

Would that be the way around you would do it?

Another option i’m thinking of

Could I query each table as I pass the industry/function/locations via url parameter to get all specialist ID’s
And then do a specialist table query returning all specialists that have an ID that appears in all the previous tables as a AND condition?

[mebeingken]

There’s a lot I don’t know about your data but I’ll take a stab at it:

Industries and Services hold the global industries and services

specialist_industries and specialist_services simple link specialists to their respective industries and services using id numbers only.

You use that linking table like this

And you have a condition that limits it to the specialist

[mgaussie]

Thanks Ken. This setup looks like it would return all industries that match the list of specialist ID’s returned by the initial query. Is that correct?

The linking structure you have created is pretty much exactly what I have done.

How would you go about returning a list of the specialists, that match certain industries, certain functions and certain services ?

E.g. if the url returns service ID 1 and function ID 29 and Industry ID 34

I’d like to return all specialists that match those ID’s in each join table…

[mebeingken]

It’s starting to feel like I’m building this for you.

[mgaussie]

Sorry Ken, no more questions and again, thank you so much for your help today - you’ve got me a lot further, a lot faster than I was going!

[mebeingken]

Is the bulk of that time spent processing the server action, or downloading the results? Assuming it is processing, then confirm you have indexed your tables appropriately.

[mgaussie]

Hey Ken, thank you for lending a hand again.

I’m not sure the best way to define if it’s download, or processing. Can you point me in the right direction?

Here is the screen of the waterfall in Google Dev Tools:

[mebeingken]

The green is the front end waiting for the request to finish, the blue is download time. Seems to me this is all processing time on the server.

And the way to find that is by clicking on timing:

[mgaussie]

Yep, 100% processing then - it has Waiting (TTB) as the entire green section - ranging from 8s upwards. It gets a little faster on the staging AWS server at a bit over 4 seconds, but still crazy and no doubt will get worse.

What would you recommend would be a good next step to debug this? I’ve created the structure as you recommended above

[mebeingken]

Table indexing.

At a minimum, add an index for each column that is used to link with other tables. Also, take a look at your conditions within queries and add indexes for those.

Now, what I’ve said above is a completely oversimplified look at table indexing, but there is a ton of information online on how to go about doing it properly and methodically.

[mgaussie]

Thanks Ken, much appreciated. Looking into this now.