Count(*) of a query having a LIMIT

Question

I have this SQL query:

SELECT users.*, users_oauth.* FROM users LEFT JOIN users_oauth ON users.user_id = users_oauth.oauth_user_id WHERE (
(MATCH (user_email, user_firstname, user_lastname) AGAINST ('"+smith "+john"' IN BOOLEAN MODE)) ) 
ORDER BY user_date_accountcreated DESC LIMIT 0,50

Is there any way to get the COUNT(*) of this query ignoring the LIMIT 0,50 in this same query ? Or do I have to do 2 queries, one for the results, one for the COUNT(*) ?

Thanks.

Answer 1

If your MariaDB version is 10.2.0+ then window functions are supported and you can use COUNT(*) OVER ():

SELECT 
  users.*, 
  users_oauth.*,
  COUNT(*) OVER () AS countall
FROM users 
LEFT JOIN users_oauth ON users.user_id = users_oauth.oauth_user_id 
WHERE 
  MATCH (user_email, user_firstname, user_lastname) AGAINST ('"+smith "+john"' IN BOOLEAN MODE)
ORDER BY user_date_accountcreated DESC 
LIMIT 0,50

If your MariaDB version is 10.2.0- (prior to that) you could use a nested select within a column:

SELECT 
  users.*, 
  users_oauth.*,
  (SELECT count(*)
   FROM users 
   LEFT JOIN users_oauth ON users.user_id = users_oauth.oauth_user_id 
   WHERE MATCH (user_email, user_firstname, user_lastname) AGAINST ('"+smith "+john"' IN BOOLEAN MODE)
  ) AS countall
FROM users 
LEFT JOIN users_oauth ON users.user_id = users_oauth.oauth_user_id 
WHERE 
  MATCH (user_email, user_firstname, user_lastname) AGAINST ('"+smith "+john"' IN BOOLEAN MODE)
ORDER BY user_date_accountcreated DESC 
LIMIT 0,50