count in multiple tables

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
tomcupr
Forum Newbie
Posts: 11
Joined: Mon May 08, 2006 2:26 am
Location: Sheffield
Contact:

count in multiple tables

Post 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
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post by onion2k »

This is in PostgreSQL I assume?
tomcupr
Forum Newbie
Posts: 11
Joined: Mon May 08, 2006 2:26 am
Location: Sheffield
Contact:

Post by tomcupr »

That's right
GM
Forum Contributor
Posts: 365
Joined: Wed Apr 26, 2006 4:19 am
Location: Italy

Re: count in multiple tables

Post 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
)
tomcupr
Forum Newbie
Posts: 11
Joined: Mon May 08, 2006 2:26 am
Location: Sheffield
Contact:

Post by tomcupr »

Thanks a lot :-) I'm missing simple things :-)

Cheers
Post Reply