[SQL] Dynamic query across multiple tables in supabase?

I am trying to use an input box to search across multiple tables in react with supabase but not having much luck. Any ideas?

const fetchLeads = async () => {
let query = supabase
.from('lead')
.select(`
lead_id,
progress,
sales_status,
comments,
personerson_id (
first_name, last_name, email, phone
),
propertyroperty_id (
street_address, post_code, gas_safe, epc, epc_date
)
`);

// Server-side search logic across the person and property tables
if (searchTerm) {
query = query.or(
`person.first_name.ilike.%${searchTerm}%,person.last_name.ilike.%${searchTerm}%,property.street_address.ilike.%${searchTerm}%,property.post_code.ilike.%${searchTerm}%`
);
}


The logic isn't parsing with Supabase. I have tried multiple variants but no luck. The closest I got was using or like this but this only bring back data in the specific table, instead of both tables.

// Server-side search logic usingilike across the person and property tables if (searchTerm) { query = query .or(first_name.ilike.%${searchTerm}%,last_name.ilike.%${searchTerm}%, { foreignTable: 'person' }) .or(street_address.ilike.%${searchTerm}%,post_code.ilike.%${searchTerm}%, { foreignTable: 'property' }); }

Continue reading...