I ran into the need of getting the total flattened count for pagination purposes when using a group by clause in a query.
A stripped down example of the structure of this query would look like the following.
SELECT COUNT(*)
FROM table as t1
INNER JOIN table2 AS t2 ON t1.id = t2.t1_id
GROUP BY t1.id
As one would surmise, the results would be count of the records for grouping.
| count |
--------
| 1 |
| 2 |
| 20 |
| 3 |
| 17 |
The conclusion was to do another select count wrapping the first query. After some trial and error and intensive googling, searching the mysql docs, and mysql books, I was still getting no where.
I found the answer only by searching stackoverflow.com. To which should be my default geek search engine. (There really is a market for niche search engines).
The answer looks like this, the first query being wrapped with a count selecting from the query.
SELECT COUNT(*)
FROM (SELECT COUNT(*)
FROM table as t1
INNER JOIN table2 AS t2 ON t1.id = t2.t1_id
GROUP BY t1.id)
tmp
Thus for ending my torment and suffering, I bumped up Marcus Adams’s answer on stackoverflow and must give mad props in this journal entry.
Tags: Developers Journal, mysql, sql, stackoverflow.com
Geek Specs
Ignore the Trekkie reference.  I’ve started to work on the data abstraction layer for Midori, after narrowing down the supported out-of-the-box relational databases.  In no particular order, they are SqlServer, SqlServerCe, MySql, Postgress, and Sqlite. Its being written in .Net 4.0 and hopefully it should port to Mono.
The main abstraction class Midori.Data.SqlAdapter makes use of the System.Data interfaces, like System.Data.IDataReader. Obviously this class will grow as other crud operations are added.  Each RDMS will have a class that will inherit Midori.Data.SqlAdapter, like Midori.Data.Sqlite.SqliteAdapter, which will hold code specific to each database.
There is a Connection class that wraps other connection objects and supplies OnConnect and OnClose events, which should be helpful for closing the connection objects for methods that pass back datareader objects.
Interesting Notes
System.Data.Common.DbConnectionStringBuilder is a handy class for building and inspecting values of a connection string in a hash like way.  Some Ado.Net layers, like System.Data.SqlServerCe, does not implement their own connection string builder. While it is nice to have, its not a necessity, especially when the common dbconnectionstringbuilder will suffice.
Postgres or Npgsql, has some interesting behavior. The following select query has Pascal casing for the names of the columns.
SELECT 'column1' AS Column1, 10 AS Age
However when the DataReader for Postgres returns the names of the columns, they are all lowercase, all the other databases return the column aliases in the same case as specified in SQL.
So in the tests, I had to rewrite the query to use all lowercase column aliases, like so:
SELECT 'column1' AS column1, 10 AS age
Tags: .net, ce, dbconnectionstringbuilder, midori, mysql, postress, sql server, sqlite