0

im actually a frontend developer not a backend developer. so believe me, its really hard for me to work with postgreSQL and i searched a lot for my question but i couldnt find exactly what i want. i want to create a query in supabase that returns all rows of 3 tables. my goal is to search in these rows for search results base on users entered text in my app. so i want to return all rows from 3 tables in a function and a request. then by using the methods of supabase js library i can filter the rows. you can understand my point better with this code example:

create
or replace function search()
returns /* what type of data should i return? */
language sql as $$
  /*
  * select all rows of table artists -> select all rows of table musics -> select all rows of 
  * table playlists
  */
$$;

then i can do something like this in js:

let query = supabase.rpc('search').ilike('name', search_text)

so how can i do something like this? thanks for helping.

UPDATE here are the rows with some of theie columns that are in my 3 tables: note: the rows of my 3 tables are totally different from each other.

a row of table *musics*:
id | name | singer | listenTimes | link
1|without me|eminem|10000|www.test.com 

a row of table *artists*:
id | name | monthlyListens | bio
1|eminem|123431|{bio infos}

a row of table *playlists*:
id | name | musicsId | musics | cover
1|your favorite playlist|[1,2,3]|3|www.test.com/cover.png

base on jiri baums answer, i want to create a function that has an argument. what this function does is to check the columns of rows from 3 tables to check if the argument is something like the value of a column and returns the rows that at least have a column like the value of argument. for example: the function has an argument with the value of emvnem. the rows 1 and 2 in the exampla above will be returned. because emvnem is like eminem. if its is exactly like eminem, the returned rows are row 1 and 2. so how can i implement something like this?

Saman
  • 171
  • 13
  • You should add sample data for all 3 tables, then also include the output you want here. – Tim Biegeleisen Jul 29 '22 at 08:50
  • 1
    table structure for 3 tables needed, whether the tables have same or different column names – bishwa.poudel Jul 29 '22 at 08:51
  • Imagine a billion records in these tables, unlikely that your application could handle this. When you need a powerful database like PostgreSQL, you don’t want a model like this. Otherwise SQLite or a flat file format would be good enough – Frank Heikens Jul 29 '22 at 17:15
  • ok. i updated my questions. please take a look at it. thank you. – Saman Jul 30 '22 at 09:50

1 Answers1

1

As a general rule, this would be a poor design:

  • In terms of efficiency, it's best to push filtering as close as possible to the data; PostgreSQL has ilike functionality built in, indeed the queries in supabase are based on SQL syntax. See the PostgreSQL docs for LIKE and ILIKE

    SELECT *
FROM table_name
WHERE name ILIKE 'search text'

    With any real amount of data, sending everything to the front-end for filtering will be prohibitive.

  • In terms of access to data, if you send the whole table to the front end, the user will be able to copy it, save it, do other things with it; in most cases, you probably prefer to avoid that.

If you nevertheless want to do this, the operator you're looking for is UNION; the PostgreSQL docs have an example of doing pretty much exactly this:

SELECT distributors.name
    FROM distributors
    WHERE distributors.name LIKE 'W%'
UNION
SELECT actors.name
    FROM actors
    WHERE actors.name LIKE 'W%';

Link: https://www.postgresql.org/docs/14/sql-select.html

Jiří Baum
  • 6,697
  • 2
  • 17
  • 17
  • hello. thank you for answer. i think your right. your reasons are logical. so your first example is correct. but i should create a function and i should check the rows of 3 tables. what should i do in this situation? can you please write the function for me? i really searched a lot about it but i couldnt find my answer. postgreSQL is really complicated for me. i think its because i dont know backend and working with databases. – Saman Jul 30 '22 at 08:57
  • Probably don't mix the data; your endpoint can either return an object with three lists, or you can have three endpoints (one for each table). That way, when the user selects one of the results, you'll be able to do the relevant action depending on whether it was a music, artist or playlist. – Jiří Baum Jul 31 '22 at 11:32