Skip to content

Unable to generate code when using SQLite with LEFT JOIN #1994

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom
Closed
zasdaym opened this issue Dec 9, 2022 · 4 comments
Closed

Unable to generate code when using SQLite with LEFT JOIN #1994

zasdaym opened this issue Dec 9, 2022 · 4 comments
Labels
📚 sqlite bug Something isn't working 💻 darwin 🔧 golang

Comments

@zasdaym
Copy link

zasdaym commented Dec 9, 2022

Version

Other

What happened?

sqlc generate failed when using the described schema and query. The version used is v1.16.0.

Relevant log output

column reference "id" not found

Database schema

CREATE TABLE IF NOT EXISTS links (
    id TEXT PRIMARY KEY,
    destination TEXT NOT NULL,
    created_at INTEGER NOT NULL,
    updated_at INTEGER NOT NULL
);

CREATE TABLE IF NOT EXISTS visits (
    id TEXT PRIMARY KEY,
    link_id TEXT NOT NULL,
    created_at INTEGER NOT NULL,
    FOREIGN KEY (link_id) REFERENCES links(id)
);

SQL queries

-- name: GetLinks :many
SELECT
    links.id,
    links.destination,
    links.created_at,
    links.updated_at,
    COUNT(visits.id) AS visits
FROM links LEFT JOIN visits ON links.id = visits.link_id
GROUP BY links.id;

Configuration

version: 2
sql:
  - engine: sqlite
    schema: internal/sqlite/schema.sql
    queries: internal/sqlite/queries.sql
    gen:
      go:
        package: sqlite
        out: internal/sqlite

Playground URL

https://play.sqlc.dev/p/c8d3c10a1bb40e4c91484e3334f3ae33e7764a7f5e4ba6742c6842c4c13c5933

What operating system are you using?

macOS

What database engines are you using?

No response

What type of code are you generating?

Go
@zasdaym zasdaym added bug Something isn't working triage New issues that hasn't been reviewed labels Dec 9, 2022
@zakaria-chahboun
Copy link

it's worked for me
https://play.sqlc.dev/p/d9035f55cc5e09c0b18e9e798423c4b7e2969b7036ce25d680a553f7d18addf9

@zakaria-chahboun
Copy link

zakaria-chahboun commented Jan 9, 2023 •
edited
Loading

@zasdaym
Your query is working now, You just have to work with placeholders instead of table names.

SELECT
- links.id
+    l.id,
-    links.destination,
+    l.destination,
-    COUNT(visits.id) AS visit_count
+    COUNT(v.id) AS visit_count
-FROM links LEFT JOIN visits ON links.id = visits.link_id
+FROM links l LEFT JOIN visits v ON l.id = v.link_id
-GROUP BY links.id;
+GROUP BY l.id;

https://play.sqlc.dev/p/253ad0cdf535a53bfe0be3235fa7324d9b10e3b03cfd952bfa57d52404d5dd13

@zasdaym
Copy link
Author

zasdaym commented Jan 12, 2023

Thanks for the solution @zakaria-chahboun, but the same exact query is working when using the postgresql engine.

@andrewmbenton andrewmbenton added 🔧 golang 💻 darwin 📚 sqlite and removed triage New issues that hasn't been reviewed labels Jun 6, 2023
@orisano
Copy link
Contributor

orisano commented Jul 11, 2023

Duplicate of #2271, I think.

@kyleconroy kyleconroy closed this as not planned Won't fix, can't repro, duplicate, stale Sep 26, 2023
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
📚 sqlite bug Something isn't working 💻 darwin 🔧 golang
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
5 participants
@kyleconroy @andrewmbenton @orisano @zakaria-chahboun @zasdaym