SELECT DISTINCT ON (qid)qid, aid, rank(idxfti, to_tsquery('$searchphrase'),2) AS score FROM forumquestions WHERE idxfti @@ to_tsquery('$searchphrase') AND catid != '33' AND status = '1' UNION
SELECT DISTINCT ON (qid) qid, aid, rank(idxfti, to_tsquery('$searchphrase'),2) AS score FROM forumanswers WHERE idxfti @@ to_tsquery('$searchphrase') AND catid != '33'
ORDER BY $orderby DESC LIMIT 10 OFFSET $offset
I query code above which I use for search function - could someone point me to correct way how to count number of results?
SELECT DISTINCT ON (qid)qid, aid, rank(idxfti, to_tsquery('$searchphrase'),2) AS score FROM forumquestions WHERE idxfti @@ to_tsquery('$searchphrase') AND catid != '33' AND status = '1' UNION
SELECT DISTINCT ON (qid) qid, aid, rank(idxfti, to_tsquery('$searchphrase'),2) AS score FROM forumanswers WHERE idxfti @@ to_tsquery('$searchphrase') AND catid != '33'
ORDER BY $orderby DESC LIMIT 10 OFFSET $offset
I query code above which I use for search function - could someone point me to correct way how to count number of results?
Cheers
I don't know the correct syntax for PostgreSQL, but you should be able to simply wrap another select around the entire statement, like:
SELECT COUNT(*) FROM (
SELECT DISTINCT ON (qid)qid, aid, rank(idxfti, to_tsquery('$searchphrase'),2) AS score FROM forumquestions WHERE idxfti @@ to_tsquery('$searchphrase') AND catid != '33' AND status = '1' UNION
SELECT DISTINCT ON (qid) qid, aid, rank(idxfti, to_tsquery('$searchphrase'),2) AS score FROM forumanswers WHERE idxfti @@ to_tsquery('$searchphrase') AND catid != '33'
ORDER BY $orderby DESC LIMIT 10 OFFSET $offset
)