Page 1 of 1
count in multiple tables
Posted: Fri Sep 08, 2006 3:52 am
by tomcupr
Code: Select all
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
Posted: Fri Sep 08, 2006 4:57 am
by onion2k
This is in PostgreSQL I assume?
Posted: Fri Sep 08, 2006 9:08 am
by tomcupr
That's right
Re: count in multiple tables
Posted: Fri Sep 08, 2006 10:01 am
by GM
tomcupr wrote:Code: Select all
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:
Code: Select all
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
)
Posted: Mon Sep 11, 2006 4:38 am
by tomcupr
Thanks a lot

I'm missing simple things
Cheers