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