yesodweb / yesodweb/persistent

Flag to make `??` optional for rawSql queries

Open
#617 1 comment 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

Dominant language
Haskell
Stars
486
Forks
306
PR merge metrics
No merged PRs in 30d

Description

This sucks (make sure to scroll right for maximum fun):

  rawSql [st|
select jobs_id, jobs_title, jobs_original_html, jobs_sanitized_html, jobs_company_id, jobs_owner_id, jobs_jobtype, jobs_category, jobs_location_id, jobs_slug, jobs_slug_offset, jobs_education, jobs_languages, jobs_certifications, jobs_experience, jobs_remote, jobs_date, jobs_uuid, jobupdates_id, jobupdates_job_id, jobupdates_at, jobupdates_status, companies_id, companies_name, companies_subdomain, companies_subdomain_verified_at, companies_owner_id, companies_description, companies_uuid
FROM       (select ??, ??, ??,
                   jobs.id as jobs_id, jobs.title as jobs_title, jobs.original_html as jobs_original_html, jobs.sanitized_html as jobs_sanitized_html, jobs.company_id as jobs_company_id, jobs.owner_id as jobs_owner_id, jobs.jobtype as jobs_jobtype, jobs.category as jobs_category, jobs.location_id as jobs_location_id, jobs.slug as jobs_slug, jobs.slug_offset as jobs_slug_offset, jobs.education as jobs_education, jobs.languages as jobs_languages, jobs.certifications as jobs_certifications, jobs.experience as jobs_experience, jobs.remote as jobs_remote, jobs.date as jobs_date, jobs.uuid as jobs_uuid, jobupdates.id as jobupdates_id, jobupdates.job_id as jobupdates_job_id, jobupdates.at as jobupdates_at, jobupdates.status as jobupdates_status, companies.id as companies_id, companies.name as companies_name, companies.subdomain as companies_subdomain, companies.subdomain_verified_at as companies_subdomain_verified_at, companies.owner_id as companies_owner_id, companies.description as companies_description, companies.uuid as companies_uuid,
            ROW_NUMBER() OVER(PARTITION BY jobs.id
                              ORDER BY jobupdates.at desc) as rk
            from jobs
            JOIN jobupdates as jobupdates
            ON jobupdates.job_id = jobs.id
            JOIN companies
            ON companies.id = jobs.company_id
            where jobs.id NOT IN (SELECT jobtasks.job_id
                                  FROM jobtasks
                                  WHERE jobtasks.finished_at IS NULL)) as jobsf
where jobsf.rk = 1;
|] []

I have to drop the rk on the floor or it complains about there being too many columns. I can't reference the columns of the subselect without aliasing them, or it complains about ambiguous references.

Contributor guide

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

The issue identifies rawSql and shows an SQL query using ?? placeholders; start by locating rawSql handling in persistent and tracing how those placeholders are expanded and validated. Done means an option allows the placeholders in this query without the extra-column failure, with coverage for the shown case.

Written by the indexing model from the issue text.

Assessment

Tech stack
haskell, sql
Domain
databases
Issue type
Feature
Difficulty
3/5
Estimated time
1-2 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.