London Smith London Smith - 1 month ago 8
SQL Question

Count(*) of a query having a LIMIT

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

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