Admin Page 500 and the Count Query That Lost Its Join

I opened the review moderation page on the KotaPortal dashboard and got a white screen with an HTTP 500. The strange part: I was logged in as a superadmin. The list feature itself looked perfectly normal when tested directly against the API. No permission error, no timeout. Just one raw database message: Unknown column 'c.module' in 'where clause'.

My First Guess Was Wrong

My first instinct blamed the permission middleware. Maybe some scoped-staff logic bled into the superadmin path, or a role check failed to verify the module column. I spent ten minutes in the middleware logs making sure the role variable parsed correctly. Everything was fine. The middleware never even reached the query builder stage, because this error fired earlier in the pipeline.

Two Queries, One WHERE

After tracing the stack, I found the culprit. A paginated admin page actually runs two separate queries that share one WHERE clause. The first is a plain SELECT that fetches the current page. The second is a SELECT COUNT(*) that exists only so the system can compute the total number of pages, and per the MySQL documentation [3], COUNT(*) just counts rows.

The main SELECT joined reviews, entities, and categories. The COUNT query only joined reviews and entities. Sitting in that shared WHERE was a module-scope filter referencing the alias c.module. Since the COUNT query never joined categories (the table behind alias c), MySQL raised error 1054, ER_BAD_FIELD_ERROR, whose entry in the server error reference [2] reads exactly: unknown column.

This is where the JOIN documentation [1] clicks into place: an ON clause can refer only to its operands. Name resolution has no memory of the sibling query. Each statement must resolve every alias it references, on its own.

The Go side made the failure absolute. We execute queries through sqlx [4], whose GetContext performs a QueryRow and scans the resulting row into the destination. When the database rejects the statement, that error travels straight back to the handler. No fallback, no partial page. The whole admin screen collapsed into one 500.

Equalizing the Join Surface

The fix was one line. Both queries need an identical join surface, so I added a single LEFT JOIN to the count query:

-- Sebelum: join categories hilang, alias c nggak dikenal di WHERE
SELECT COUNT(*) FROM reviews r
INNER JOIN entities e ON e.id = r.entity_id
-- WHERE c.module = (moderasi) muncul di sini dan langsung error

-- Sesudah: satu LEFT JOIN nambahin alias yang sama dengan query list
SELECT COUNT(*) FROM reviews r
INNER JOIN entities e ON e.id = r.entity_id
LEFT JOIN categories c ON c.id = e.category_id
WHERE c.module = 'moderation'

With LEFT JOIN categories c present, the alias is legal in the WHERE clause again. The page total computes correctly, and the database stops protesting.

Then I locked the door behind me: a regression test that walks both paths, superadmin and scoped staff. The superadmin path proves the module filter applies even at full scope; the budaya-scoped path proves a staff account still gets an empty, correct result instead of a 500.

The rule I keep now: whenever a new filter lands in a shared WHERE builder, grep every query that consumes that builder. Never assume COUNT(*) inherits the join context of its sibling SELECT. In the eyes of the database they are two unrelated statements, and one forgotten alias is all it takes to take a page down.

Sources

• [1] MySQL Reference Manual: JOIN Clause
• [2] MySQL Server Error Reference: ER_BAD_FIELD_ERROR (1054)
• [3] MySQL Reference Manual: Counting Rows
• [4] sqlx package documentation