0

I am new to postgresql and I have the follow query that produces the next result. I want to count the number of rows before this limit is applied

 FROM
  (SELECT *
   FROM places
   LIMIT 10
   OFFSET 0) places

SELECT places.place_id,
       username,
       title,
       description,
       visible,
       score,
       placelocation,
       category,
       price,
       accessibility,
       places.date,
       dangerous,
       url,
       image_id,
  (SELECT COUNT(*) AS likednumber
   FROM "favoritePlaces"
   WHERE "favoritePlaces".place_id=places.place_id), CASE
                                                         WHEN EXISTS
                                                                (SELECT *
                                                                 FROM "favoritePlaces"
                                                                 WHERE "favoritePlaces".place_id = places.place_id
                                                                   AND user_id='128'
                                                                 LIMIT 10) THEN 'true'
                                                         ELSE 'false'
                                                     END AS liked,
                                                     CASE
                                                         WHEN EXISTS
                                                                (SELECT *
                                                                 FROM "savedPlaces"
                                                                 WHERE "savedPlaces".place_id = places.place_id
                                                                   AND user_id='128'
                                                                 LIMIT 10) THEN 'true'
                                                         ELSE 'false'
                                                     END AS saved
FROM
  (SELECT *
   FROM places
   LIMIT 10
   OFFSET 0) places
LEFT JOIN images ON images.place_id = places.place_id
WHERE LOWER(description) SIMILAR TO LOWER(Concat('%', '', '%'))
  AND LOWER(placelocation) = LOWER(placelocation)
  AND category =category
  AND price =price
  AND dangerous =dangerous
  AND accessibility =accessibility
  OR LOWER(title) SIMILAR TO LOWER(Concat('%', '', '%'))
  AND visible=TRUE
  AND placelocation SIMILAR TO placelocation
  AND category =category
  AND price =price
  AND dangerous =dangerous
  AND accessibility =accessibility

table As per this answer, I want to implement join statement to count the rows: https://stackoverflow.com/a/28888696/14131447 as it seems to be the more reliable option?

How do I do that?

a_horse_with_no_name
  • 497,550
  • 91
  • 775
  • 843
AtanasB
  • 120
  • 7

0 Answers0