0

I have a search string searchString and the following schema (pseudocode, but I use postgresql):

cakes
----
id int    primary key
cake_type varchar
views     int

bakers
----
id        int primary key
name      varchar
views     int

I want to select cakes and bakers (together since I need pagination), based on how much their cake_type and name is similar to searchString, and order them by views descending.

For the first table, I could do something like this:

select
  id as cake_id,
  cake_type
from cakes
where cake_type ilike :searchString or cake_tyoe ilike lower(:searchStringLiteral)
order by views desc
limit 10
offset 5

Notice the limit and offset that I need for pagination.

But how do it for both the cakes and bakers tables so I can get pagination for the most viewed ones "together"?

Comment: The above schema is a simplified one I made, to make things simpler. I hope it serves the purpose.

Improve this question

1 Answer 1

Reset to default
2

Those are two separate tables (cakes and name) so I'm not sure what You would like to have in the results... You can join two sets by using UNION ALL but they need to have same number and type of columns. Try this one:

SELECT * FROM
 (
  SELECT Id, cake_type AS item, views
  FROM Cakes
  UNION ALL
  SELECT Id, name AS item, views
  FROM Baker
 )
WHERE  item ILIKE :searchString 
    OR item ILIKE lower(:searchStringLiteral)
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments